## Question:

Tag: vba,excel-vba

Having a little trouble with my userform, its not unloading once i hit the command button the data is inputted to the sheet but the userform is not refreshed and the data stays in the text boxes. It was working fine until i put the data validation in, but i cant remove the data validation because it is key to have it in there, any suggestions on what i need to be looking for?

Private Sub CommandButton1_Click()
Dim emptyRow As Long

If Not IsNumeric(TextBox1.Value) Then
MsgBox ("Sorry, you need to provide a valid order number")
TextBox1.SetFocus
Exit Sub
End If

If TextBox3.Value = "" Then
MsgBox ("Sorry, you need to provide a weight")
TextBox3.SetFocus
Exit Sub
End If

If TextBox4.Value = "" Then
MsgBox ("Sorry, you need to provide a country code")
TextBox4.SetFocus
Exit Sub
End If

If TextBox2.Value = "" Then
MsgBox ("Sorry, you need to provide a country")
TextBox2.SetFocus
Exit Sub
End If

If ComboBox1.Value = "" Then
MsgBox ("Sorry, you need to provide a service")
ComboBox1.SetFocus
Exit Sub
End If

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
If ComboBox1 = "EU" Then
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 4).Value = UCase(TextBox4.Value)
Cells(emptyRow, 5).Value = UCase(TextBox2.Value)
Cells(emptyRow, 2).Value = TextBox3.Value
Cells(emptyRow, 6).Value = ComboBox1.Value
Else
If ComboBox1 = "ROW" Then
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 4).Value = UCase(TextBox4.Value)
Cells(emptyRow, 5).Value = UCase(TextBox2.Value)
Cells(emptyRow, 3).Value = TextBox3.Value
Cells(emptyRow, 6).Value = ComboBox1.Value
Else

End Sub

Cells(emptyRow, 7).Value = Date

Application.ActiveWorkbook.Save
ParcelDataEntry.Show
End If
End Sub


There are a couple problems with the code you posted.

After the If ComboBox1 = "ROW" Then ... Else block of code you've got an End Sub but no End If. You definitely need to add the End If and I suspect you should remove the End Sub.

You've got three lines of code inside the last If ... End If block which I suspect you want to run whenever the user has properly completed the controls on the form. These should be moved outside of the If ... End If blocks.

You are repeating chunks of code which test if the user has entered a value into a control. These should be extracted into a function. You are also repeating lines of code that write to the worksheet. These can be moved outside of the If ... End If block but leaving the different lines inside.

I think this is how you want your code to look:

Private Sub CommandButton1_Click()
Dim emptyRow As Long

If Not IsNumeric(TextBox1.Value) Then
MsgBox ("Sorry, you need to provide a valid order number")
TextBox1.SetFocus
Exit Sub
End If

If Not UserEnteredAValue(TextBox3, "weight") Then
Exit Sub
End If

If Not UserEnteredAValue(TextBox4, "country code") Then
Exit Sub
End If

If Not UserEnteredAValue(TextBox2, "country") Then
Exit Sub
End If

If Not UserEnteredAValue(ComboBox1, "service") Then
Exit Sub
End If

'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Transfer information
' These cells always get the same value
Cells(emptyRow, 1).Value = TextBox1.Value
Cells(emptyRow, 4).Value = UCase(TextBox4.Value)
Cells(emptyRow, 5).Value = UCase(TextBox2.Value)
Cells(emptyRow, 6).Value = ComboBox1.Value
Cells(emptyRow, 7).Value = Date

If ComboBox1 = "EU" Then
Cells(emptyRow, 2).Value = TextBox3.Value
Else
If ComboBox1 = "ROW" Then
Cells(emptyRow, 3).Value = TextBox3.Value
Else

End If

End If

Application.ActiveWorkbook.Save
ParcelDataEntry.Show

End Sub

Private Function UserEnteredAValue(ByRef theControl As Control, ByRef theDescription As String) As Boolean

Dim result As Boolean

If theControl.Value <> "" Then
result = True
Else
MsgBox ("Sorry, you need to provide a " & theDescription)
theControl.SetFocus
result = False
End If

UserEnteredAValue = result

End Function


PS when you use the Cellsor the Range method you should always qualify it with the workbook and worksheet reference, e.g. Workbooks("book_name.xlsm").Worksheets("sheet_name").Cells(emptyRow, 1) so that you know exactly workbook & worksheet you are using.

# Related:

## Using date in CreateQueryDef

vba,date,ms-access
I have a table on which I run a query that I export to Excel. Here is an example: Table: Food Item | Price | Limit_Date | ------------------------------- Carrot | 0.80 | 08/07/2015 | Salmon | 4.30 | 01/07/2015 | Biscuits | 2.40 | 15/12/2015 | Milk | 1.00 |...

## I need help setting the RecordSource of a Report within a VBA Function

vba,ms-access,access-vba
Ok, so I've been looking around here for a few days (and a few other sites) and while I have modified my code a good bit to find a solution, it still doesn't work. I have four source queries, and I'd like to set the record source of my report...

## Vba changing directory to save

excel-vba
I'd like to save a file in a directory whose name changes according to the name of the Previous Month. Here is the code: name_month = MonthName(Month(Date) - 1) Set Newbook = Workbooks.Add With Newbook .Title = "TESO1" .SaveAs Filename:="C:\Users\ee31264\Desktop\Mensile Automat\name_month \send\TESO1.xlsx" End With Newbook.Close unfortunately the name_month i wrote...

## VBA - Unable to pass value from Private to Public Sub

excel,vba,excel-vba
I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

## Using VLOOKUP formula or other function to compare two columns

mysql,excel,vba,date
I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

## Excel VBA Program Code by Using Randomize Timer

vba
What is wrong in below program code, why not returning correct result. Whenever i put any number in excel cell (1,1) and run the program, Numbers changed automatically, also not throwing a correct result in cell (1,2). Please Help as i learning VBA. Private Sub Sheet2_1() Dim num1 As Integer...

## Using a cell's number to insert that many rows (with that row's data)

excel,excel-vba
I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

## VBA data type to store Range().Characters()

vba
I can't find a data type to store Characters from a Range. I already tried the following code, but nothing happened. I think the problem is because of wrong data type. Dim chars As Characters chars = Range("A2").Characters(0, 4) MsgBox chars.Text ...

## Excel VBA 2013 Print Image

image,excel-vba,printing
I would like to print an image that shows in a frame on a userform. frame1.Picture = LoadPicture(spath & xPicture & ".jpg") spath is the full path to where the picture is stored. This shows the picture just fine. Now I would like to be able to print it. Is...

## Interface Controls for DoEvent in Excel

excel,vba,excel-vba,loops,doevents
I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

## VBA for duplicate rows

excel-vba,duplicates
I have a sheet of columns. I want to compare data in multiple columns, and return a flag in another column to indicate rows that are duplicates. I found a little code online which was meant for checking one column of data, and have so far been unsuccessful in being...

## VBA “Compile Error: Statement invalid outside Type Block”

excel,vba,excel-vba,excel-2010
I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

## Copying sheet to last row of a sheet from another workbook

excel,vba,excel-vba
I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

## Extract All Named Ranges Into A Class

vba,excel-vba
I have a workbook with a very large amount of named ranges (well over 200). I really need a way to work quickly and easily with all of the named ranges so I can then work with / populate them using VBA. My solution up until now has been to...

## Copying a Range from Excel and Pasting it into Powerpoint NOT as a metafile but as a table which you can edit in PPP

vba,excel-vba,powerpoint-vba
All I want to do is copy a range from Excel and then paste this range into PowerPoint. When my range is manually copied from Excel to the clipboard... If I right click on a blank slide when pasting into PowerPoint, it gives me the option to paste "using destination...

## adding variables into another variable vba

excel,vba,excel-vba
Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

## Using a stored integer as a cell reference

excel,excel-vba,reference
Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

## Range.offset propety

excel-vba
I'm trying to understand a code on VBA, to debug the VBA program of someone. So I want to know that is the property and the meaning of Range (Value).offset(x;y). Here is my code: `Sub initialise() Dim i As Double, j As Double For i = 0 To maxnodes -...

## If cell value starts with a specific set of numbers, replace data

excel,vba,excel-vba
My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

## How do you delete favorite folders in outlook using VBA

vba,outlook-2007
I wish to delete all the folders from the outlook favorites then subsequently replace them, but the delete doesn't seem to work. What's wrong with my code. Setup Objects works fine ' Get the "Favorite Folders" navigation group Set favGroup = Application.ActiveExplorer.NavigationPane.Modules.GetNavigationModule(olModuleMail).NavigationGroups.GetDefaultNavigationGroup(olFavoriteFoldersGroup) This works Set inboxFldr = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox) favGroup.NavigationFolders.Add (inboxFldr)...

## Removing Alert When Using DeleteFile API

vb.net,vba,api,delete
I'm writing a VBA application which involves looping a large number of directories recursively. I am using the FindFirstFile API to to achieve this, as it offers a substantial performance boost over the FileSystemObject. In order to remove the FSO from my code entirely, I need a routine to delete...

## Excel-VBA: create named Range in row until end of cell content

vba,excel-vba,range
Imagine an Excel sheet with some rows and some content in each row (i.e. different column-length for each row). In Excel-VBA: How can I create a range-X within column-X that goes from row-cell-2 to the end of the content of this X-column ?? i.e. I would like to create a...

excel,excel-vba
I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

## EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

excel,vba,excel-vba
I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

## ReplaceLine method in VBE only replacing part of line

excel-vba,vbe
In response to this question I thought it would be fun to write a VBE macro that would automatically replace lines which look like DimAll a, b, c, d As Integer by Dim a As Integer, b As Integer, c As Integer, d As Integer In my first draft I...

vba,excel-vba
Having a little trouble with my userform, its not unloading once i hit the command button the data is inputted to the sheet but the userform is not refreshed and the data stays in the text boxes. It was working fine until i put the data validation in, but i...

## How do I do to count rows in a sheets with filters? With a suppress lines

excel,vba,filter
I have a sheet with lots of columns, but when I filter and use count = Application.WorksheetFunction.CountA(Range("A:A")) It returns all the rows non Empty. Not only the rows I filtered....

## Activecell not in Array

vba
Having difficulty comparing my activecell against a predefined array. After importing data from another source I want to trim out the columns I don't need I've trued the array as both undefined, string and variant And tried activecell / activecell.value <> NeededColumns No joy :( Sub Trim() Dim NeededColumns As...

## Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

excel,vba,excel-vba,user-defined-functions
I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

## Exit Sub And Call another Sub

vba,excel-vba,call
I have two subs, sub1 and sub2. I want to exit sub1 completely and start sub2 if a condition is satisfied. My attempt is (running from : sub1 . . If x=y Then Exit Sub And Call sub2 End If . . End Sub ...

## Replace reference with its value in Excel VBA workbook

vba,excel-vba
I have an Excel workbook to manage my Delivery Notes, which creates another workbook with actual delivery note and stores it in the folder for me, then places new record in my main workbook so that the Delivery Number for the next delivery note can be increased by 1. My...

## excel search engine using vba and filters?

excel,vba
I am using the following vba code to filter my rows in excel based on the value in my cell C5 Sub DateFilter() 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 ActiveSheet.Range("C10:AS30").AutoFilter Field:=1, Criteria1:="*" & ActiveSheet.Range("C5").Value & "*" Application.ScreenUpdating = True End...

## Creating a Range in VBA

vba,range
I'm working on my first VBA function. The goal is to have a function that accepts two integers as inputs, and outputs an array containing all the integers in between the two inputs (end-points included). Example: If I input 5 and 9, the output should be an array of 5,...