Home
Excel VBA Examples
How To Do 
Software Create Step by Step Excel Userform
With a userform, we can create a user-friendly interface for our workbook or document .Thus, data entry to the worksheet becomes more controllable and easier.
Now let’s see how to create a simple userform to enter personal spending data.
► Let’s start Excel ,select a worksheet and open the Visual Basic Editor (to open from keyboard shortcut: press Alt + F11 keys). We will need to use both the Project Explorer and the Properties Window , if they are not visible let’s open them from the View menu.
When building the userform try to avoid double-clicking on anything(item of userform) ,because this opens the form’s code window. If you accidentally open the code window , close the code window by clicking its Close button ,or you can view the UserForm design window pressing the keyboard shortcut Ctrl + Tab.
► Let’s select the current workbook in the Project Explorer and let’s open the Insert Menu and choose UserForm.The blank UserForm appears in the code window of the Visual Basic Editor and a corresponding entry appears in the Project Explorer . The Project Explorer shows a new folder named Forms ,also Forms folder contains the new UserForm which has been given the name UserForm1 :
We should also see the Toolbox window to add new items onto userform.If it is not visible click anywhere on the new UserForm (the Visual Basic Editor hides the toolbox when it thinks you are working else where) and if it still does not appear open it from the View menu.
Toolbox is a floating window that allows we to add controls to the userform. It is not possible to add controls using Visual Basic Editor menu commands.
To add a control to userform, we click on the corresponding icon on the Toolbox and drag it onto the userform :
Toolbox is a floating window that allows we to add controls to the userform. It is not possible to add controls using Visual Basic Editor menu commands.
To add a control to userform, we click on the corresponding icon on the Toolbox and drag it onto the userform :
The UserForm has a dotted border around it. On the border, in the lower-right corner and halfway along the bottom and right sides of the form, are small white squares. These are used to resize the UserForm. We can use the mouse to drag these handles to make the UserForm the required size.
The grid of dots on the UserForm is for to help that we easily align items placed there :
The grid of dots on the UserForm is for to help that we easily align items placed there :
► Let’s Rename the UserForm and Add a Caption
A single project can include many UserForms so it is a good idea to give each UserForm a meaningful name . With the UserForm selected ,let’s find the Name property in the Properties Window (it is normally the first item in the list) and let’s change it frmExpenses. Then let’s change the Caption property to Personnel Expenses. The Project Explorer now displays the UserForm’s new name and the Title Bar of the form changes to show the new caption :
A single project can include many UserForms so it is a good idea to give each UserForm a meaningful name . With the UserForm selected ,let’s find the Name property in the Properties Window (it is normally the first item in the list) and let’s change it frmExpenses. Then let’s change the Caption property to Personnel Expenses. The Project Explorer now displays the UserForm’s new name and the Title Bar of the form changes to show the new caption :
When naming UserForm and their controls ,we must not include spaces in the name or use any of the VBA “reserved words” (i.e. those keywords that are part of the VBA language such as “Date”)
► Let’s Add a TextBox Controls and a Label Controls From VBA Control Collection
The Excel VBA Controls are the items, such as text boxes, combo boxes,labels and command buttons, that will be placed on the form. The standard selection of controls is represented by buttons on the Toolbox. Let’s point at a Toolbox button to see a tooltip showing the name of the control.
Let’s add a TextBox control to the form by clicking on the TextBox button in the Toolbox then clicking somewhere near the centre of the form. As with the UserForm itself any control that we place on the form shows a dotted border and resizing handles when the item is selected (click on any itemto select it).
We can change the size and shape of a control either by dragging the resizing handles (the mouse pointer will change to a double-headed arrow or by changing the values of its Height and Width properties in the Properties Window. To move a control drag the dotted border at a point between resizing handles (the mouse pointer will show a four-headed arrow or change the values of its Top and Left properties).
This way, we can drag and drop the textbox to any point we want on the form :
The Excel VBA Controls are the items, such as text boxes, combo boxes,labels and command buttons, that will be placed on the form. The standard selection of controls is represented by buttons on the Toolbox. Let’s point at a Toolbox button to see a tooltip showing the name of the control.
Let’s add a TextBox control to the form by clicking on the TextBox button in the Toolbox then clicking somewhere near the centre of the form. As with the UserForm itself any control that we place on the form shows a dotted border and resizing handles when the item is selected (click on any itemto select it).
We can change the size and shape of a control either by dragging the resizing handles (the mouse pointer will change to a double-headed arrow or by changing the values of its Height and Width properties in the Properties Window. To move a control drag the dotted border at a point between resizing handles (the mouse pointer will show a four-headed arrow or change the values of its Top and Left properties).
This way, we can drag and drop the textbox to any point we want on the form :
Each VBA control should have a meaningful name so that when we write the code we can easily identify control. The control that we added currently has the name TextBox1. Let’s use the Properties Window to change its name to txtFirstName.
It is helpful when naming controls to add a prefix describing the type of control (“txt” for textbox, “cbo” for combobox etc.). This reminds you what type of control it is when we are working in the code. It forces the names to appear together when displayed in a list. It also let’s we use words that are otherwise reserved (e.g. txtDate instead of Date).
Now, let’s use the toolbox to place a Label control on the form. To change the caption of the label we can either type directly on to the label or we can change its Caption property in the Properties Window. Let’s change the label’s caption to First Name: .
And let’s change the TextAlign property of the label to 3-fmTextAlignRight then double-click the lower-right corner resizing handle to snap the label to fit the text . Let’s drag the label to a position just to the left of the FirstName textbox.
When we move controls around by dragging them they snap to points on the grid. Whilst this is a useful feature, sometimes we need to position objects more accurately. We will notice that VBA Editor can’t place the label exactly level with the centre of the textbox. The grid forces it to be too high or too low. We can use the Properties Window to subtract (or add as necessary) 3 units from the Top property of the label , so that label is correctly positioned in relation to the textbox.
It isn’t necessary to give the label control a different name, because in this project we will no referring to it in the VBA codes although in other circumstances we may need to do this.
► Let’s Add Other VBA Controls To Userform
Let’s use the same techniques to add the remaining controls to the form. We need to add four textboxes, a combobox (a textbox with a drop-down list), a checkbox and three command buttons. Here is a list of the remaining controls we need to add and their properties:
Let’s use the same techniques to add the remaining controls to the form. We need to add four textboxes, a combobox (a textbox with a drop-down list), a checkbox and three command buttons. Here is a list of the remaining controls we need to add and their properties:
At any time we can check out exactly how the UserForm will look in use by pressing F5 key on our keyboard or clicking the Run button on the Visual Basic Editor toolbar. When we done this ,the UserForm will be opened in Excel. To return to the Visual Basic Editor, the UserForm is closed by clicking the close button [x] in its upper-right corner.
The UserForm that it’s controls are placed should look something like this :
► Let’s Create The ComboBox List
The Department combobox needs to be told where to get the information to build its list. There are two ways to do this. It can be done with code or it can refer to a named range of cells in the workbook. The latter method is often preferred because you can easily edit the list without having to rewrite any code.
Let’s switch to Excel and open a worksheet in the same workbook. Let’s type a column of items representing the entries we want to appear in the combobox list. We need put one item in each cell. To appear the items in alphabetical order, we can sort the list in the worksheet.
Now, let’s select the cells containing the list items and name the range of cells. For convenience, ago let’s select Data2 sheet and enter items that will form the Departments name starting from cell A1 on the Data2 sheet. Then, let’s click in the Name box (the box just above and to the left of cell A1), let’s type the name Departments then let’s press Enter key. Let’s click somewhere else on the worksheet then check that we have correctly named the range by clicking the down-facing arrow to the right of the Name box. We should see your range name in the list. Let’s choose it and check that Excel selects the cells that contain your list :
The Department combobox needs to be told where to get the information to build its list. There are two ways to do this. It can be done with code or it can refer to a named range of cells in the workbook. The latter method is often preferred because you can easily edit the list without having to rewrite any code.
Let’s switch to Excel and open a worksheet in the same workbook. Let’s type a column of items representing the entries we want to appear in the combobox list. We need put one item in each cell. To appear the items in alphabetical order, we can sort the list in the worksheet.
Now, let’s select the cells containing the list items and name the range of cells. For convenience, ago let’s select Data2 sheet and enter items that will form the Departments name starting from cell A1 on the Data2 sheet. Then, let’s click in the Name box (the box just above and to the left of cell A1), let’s type the name Departments then let’s press Enter key. Let’s click somewhere else on the worksheet then check that we have correctly named the range by clicking the down-facing arrow to the right of the Name box. We should see your range name in the list. Let’s choose it and check that Excel selects the cells that contain your list :
We can use this new name that we created in the whole workbook.
If we add items to the list at a later date we may need to redefine the list. We can do this by opening Excel’s Insert menu and choosing Name then Define.
Let’s return to the Visual Basic Editor and click on the Department combobox to select it then go to the Properties Window and find the RowSource property. Let’s enter Departments name as we used for the range containing our list .
Let’s test the form by pressing the F5 key and see that the combobox now how displays our list :
► Let’s Check The Tab Order
Many people when working in a form like to move around from control to control by clicking the Tab key on their keyboard. The order in which the tab key moves we around a form is initially defined by the order in which we placed the controls on the form.
Let’s run the userform (open it in Excel) and, starting from the FirstName textbox, let’s press the Tab key repeatedly and check that it takes we through the form in a logical order.
If we want to change the order ,let’s close the form and in the Visual Basic Editor open the View menu and choose Tab Order. Here we can move items up and down the list to control the behaviour of the Tab key in the form :
Many people when working in a form like to move around from control to control by clicking the Tab key on their keyboard. The order in which the tab key moves we around a form is initially defined by the order in which we placed the controls on the form.
Let’s run the userform (open it in Excel) and, starting from the FirstName textbox, let’s press the Tab key repeatedly and check that it takes we through the form in a logical order.
If we want to change the order ,let’s close the form and in the Visual Basic Editor open the View menu and choose Tab Order. Here we can move items up and down the list to control the behaviour of the Tab key in the form :
► Let’s Check The Tab Order
The design of the form is finished. The next job is to write the VBA codes to power it. VBA codes is needed in this template to run as stable the three command buttons and especially for entering data on the worksheet .
The design of the form is finished. The next job is to write the VBA codes to power it. VBA codes is needed in this template to run as stable the three command buttons and especially for entering data on the worksheet .
◆ Coding the Cancel Button
The Cancel button is the simplest one to code. It needs to do the same job as the built-in close button ([x]) in the upper right corner of the form.
Let’s double-click the cmdClose command button (or right-click it and choose View Code) to open the UserForm’s code module. The Visual Basic Editor will have written the Sub and End Sub lines of the button’s Click event for we. Let’s place our cursor in the empty line between these lines, let’s press Tab key then enter the line: Unload Me
The Cancel button is the simplest one to code. It needs to do the same job as the built-in close button ([x]) in the upper right corner of the form.
Let’s double-click the cmdClose command button (or right-click it and choose View Code) to open the UserForm’s code module. The Visual Basic Editor will have written the Sub and End Sub lines of the button’s Click event for we. Let’s place our cursor in the empty line between these lines, let’s press Tab key then enter the line: Unload Me
Our code should look like this :
Let’s test the code. Let’s open the Debug menu and choose Compile VBAProject. If we get an error message,we need check our codes and compile again. Then let’s switch to the form design window (by pressing Ctrl + Tab or double click the form’s name in the Project Explorer).
Let’s press the F5 key to run the form in Excel. If we clicked the form’s Cancel button ,the userform should close and it should return we to the Visual Basic Editor :
Private Sub cmdCancel_Click()
Unload Me
End Sub
Let’s test the code. Let’s open the Debug menu and choose Compile VBAProject. If we get an error message,we need check our codes and compile again. Then let’s switch to the form design window (by pressing Ctrl + Tab or double click the form’s name in the Project Explorer).
Let’s press the F5 key to run the form in Excel. If we clicked the form’s Cancel button ,the userform should close and it should return we to the Visual Basic Editor :
◆ Coding the OK Button
Userform OK button has three jobs to do. It has to:
1. Let’s check the user’s input so that all the required information has been supplied (this is called “validation”).
2. Let’s write the data on to the worksheet in Excel.
3. Let’s clear the form ready for the next entry.
In the design view of the form let’s double-click the cmdOK button (or right-click it and choose View Code) and let’s enter the following lines into the cmdOK_Click event procedure :
Userform OK button has three jobs to do. It has to:
1. Let’s check the user’s input so that all the required information has been supplied (this is called “validation”).
2. Let’s write the data on to the worksheet in Excel.
3. Let’s clear the form ready for the next entry.
In the design view of the form let’s double-click the cmdOK button (or right-click it and choose View Code) and let’s enter the following lines into the cmdOK_Click event procedure :
Private Sub cmdOK_Click()
If Me.txtFirstName.Value = “” Then
MsgBox “Please enter a First Name.”, vbExclamation, “Staff Expenses”
Me.txtFirstName.SetFocus
Exit Sub
End If
End SubThis procedure uses an If Statement to check the contents of the txtFirstName textbox. If the textbox is empty (i.e. its contents are “” – the two quote marks with nothing between them represents “nothing”) a message is displayed, the focus is set to that textbox (the user’s cursor is taken there), and the procedure is cancelled. As before, let’s compile and test the code. Let’s open the form and, without entering anything in the First Name textbox, let’s click the OK button. We should see the error message . Let’s dismiss the message box then let’s type an entry in the First Name textbox and try again. No message box should be displayed when we click the OK button :
Let’s make a similar entry for each textbox and for the combobox. We can view the complete code listing at the end of this document .
In addition to checking that an entry is present, it is also sometimes necessary to check that the entry is correct. Let’s enter the following statements . They use the IsNumeric() function to check that the value in the txtAmount textbox is a number (and not something else such as text) and the IsDate() function to check that the value in the txtDate textbox is a date.
In addition to checking that an entry is present, it is also sometimes necessary to check that the entry is correct. Let’s enter the following statements . They use the IsNumeric() function to check that the value in the txtAmount textbox is a number (and not something else such as text) and the IsDate() function to check that the value in the txtDate textbox is a date.
If Not IsNumeric(Me.txtAmount.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Personnel Expenses”
Me.txtAmount.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDate.Value) Then
MsgBox “The Date box must contain a date.”, vbExclamation, “Personnel Expenses”
Me.txtDate.SetFocus
Exit Sub
End IfNow, having reached a point where all the required entries are present and correct, it’s time to write the entries that entered to userform on to the worksheet. This code involves using a variable to hold the number of rows of data on the worksheet.
Let’s add an empty line at the top of the current procedure, immediately after the statement Private Sub cmdOK_Click() and enter the line for variable :
Dim RowCount As Long
We have assumed that the entries are going to be made on Sheet1(we named it “Data1”) of the current workbook, starting in cell A1. We might like to prepare the worksheet by typing a row of headings in the top row. The code will work the same way if there are headings or not.
Let’s return to the end of our code and enter the new line: RowCount = Worksheets(“Sheet1”).Range(“A1”).CurrentRegion.Rows.Count
This statement counts how many rows of data are included in the region that includes cell A1 and stores that number in the RowCount variable. Now let’s enter the lines that write the date on to the worksheet :
With Worksheets(“Data1”).Range(“A1”)
.Offset(RowCount, 0).Value = Me.txtFirstName.Value
.Offset(RowCount, 1).Value = Me.txtLastName.Value
.Offset(RowCount, 2).Value = Me.cboDepartment.Value
.Offset(RowCount, 3).Value = DateValue(Me.txtDate.Value)
.Offset(RowCount, 4).Value = Me.txtAmount.Value
.Offset(RowCount, 5).Value = Me.txtDescription.Value
If Me.chkReceipt.Value = True Then
.Offset(RowCount, 6).Value = “Yes”
Else
.Offset(RowCount, 6).Value = “No”
End If
.Offset(RowCount, 7).Value = Format(Now, “dd/mm/yyyy hh:nn:ss”)
End With The code uses a number of similar statements to write the value of each control into a cell. Each cell is identified by its position relative to cell A1 by using the VBA Offset property.
This requires two numbers, the first representing the number of rows away from cell A1 (which is held in the RowCount variable), the second representing the number of columns away from cell A1 (which is written into the code as a number).
Let’s note that the DateValue() function is used to change the date entry into a real date (rather than a date represented as text) before passing it to Excel.
The value of a checkbox is expressed as “TRUE” or “FALSE” so, because we wanted to see “Yes” or “No” on the worksheet, the code uses an If Statement to make the required entry.
Finally a timestamp is written into the last column using the Format() function to specify how the exact time, as supplied by the Now() function, is displayed. Now is a good time to compile and test the code again.
To complete the procedure, after the data has been written to the worksheet, the form needs to be emptied. This requires another variable to be entered at the top of the procedure:
Dim ctl As Control
This variable represents the controls on the worksheet and will be used in the following loop which visits each control, checks to see if it is a textbox or a combobox, and if it is it sets the control’s value to an empty string ("").
If the control is a checkbox it sets its value to False ,for this let’s enter the following VBA code lines:
This requires two numbers, the first representing the number of rows away from cell A1 (which is held in the RowCount variable), the second representing the number of columns away from cell A1 (which is written into the code as a number).
Let’s note that the DateValue() function is used to change the date entry into a real date (rather than a date represented as text) before passing it to Excel.
The value of a checkbox is expressed as “TRUE” or “FALSE” so, because we wanted to see “Yes” or “No” on the worksheet, the code uses an If Statement to make the required entry.
Finally a timestamp is written into the last column using the Format() function to specify how the exact time, as supplied by the Now() function, is displayed. Now is a good time to compile and test the code again.
To complete the procedure, after the data has been written to the worksheet, the form needs to be emptied. This requires another variable to be entered at the top of the procedure:
Dim ctl As Control
This variable represents the controls on the worksheet and will be used in the following loop which visits each control, checks to see if it is a textbox or a combobox, and if it is it sets the control’s value to an empty string ("").
If the control is a checkbox it sets its value to False ,for this let’s enter the following VBA code lines:
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Then
ctl.Value = ""
ElseIf TypeName(ctl) = “CheckBox” Then
ctl.Value = False
End If
Next ctl
Let’s compile and test the code again. If any errors occur then let’s check that our typing is exactly as shown here.
◆ Coding the Clear Button
The function of this button is to clear the form manually if the user wishes to do so. It uses exactly the same procedure as the last part of the OK button’s procedure. Let’s double-click the cmdClear button to create its click event procedure and enter the following code or we can copy the code from the OK button’s procedure to save time:
The function of this button is to clear the form manually if the user wishes to do so. It uses exactly the same procedure as the last part of the OK button’s procedure. Let’s double-click the cmdClear button to create its click event procedure and enter the following code or we can copy the code from the OK button’s procedure to save time:
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Then
ctl.Value = ""
ElseIf TypeName(ctl) = “CheckBox” Then
ctl.Value = False
End If
Next ctl
► Let’s Compile, Test & Save The Finished UserForm
Let’s compile and test the code. If we are satisfied that the form is working correctly let’s save our file. The job is almost finished, all that remains is to create a macro to open the form.
Let’s compile and test the code. If we are satisfied that the form is working correctly let’s save our file. The job is almost finished, all that remains is to create a macro to open the form.
◆ Macro to Open the UserForm
As it have seen, it is easy to open the UserForm from the Visual Basic Editor, but the user who is going to use this tool needs an more easy way to open the UserForm from Excel. There are several ways to do this , let’s make using a macro containing the simple statement:
frmExpenses.Show
As it have seen, it is easy to open the UserForm from the Visual Basic Editor, but the user who is going to use this tool needs an more easy way to open the UserForm from Excel. There are several ways to do this , let’s make using a macro containing the simple statement:
frmExpenses.Show
◆ Manually Opening the UserForm
This statement can be included in a macro that the user can call from the usual menu. To create this macro first let’s go to the Visual Basic Editor’s Insert menu and choose Module to add a standard module to the workbook containing the UserForm. Let’s enter the following code into the new module’s code window :
The first line is optional. It tells Excel to switch to Data1 sheet –Worksheets(“Data1”).Activate-. But, since the code that writes the data on to the worksheet specifies the worksheet by name, it could be omitted and the data would still be written in the correct place.
The user can run this macro from the menu in the usual way (Tools > Macro > Macros) or you could assign it to a custom menu item, toolbar button, a button on the worksheet or a drawing object.
In this our template ,we drew a shape on the worksheet to open the userform and assigned to this shape the macro that it contained the above codes to open the userform :
This statement can be included in a macro that the user can call from the usual menu. To create this macro first let’s go to the Visual Basic Editor’s Insert menu and choose Module to add a standard module to the workbook containing the UserForm. Let’s enter the following code into the new module’s code window :
Sub OpenExpensesForm()
Worksheets(“Data1”).Activate
frmExpenses.Show
End Sub The first line is optional. It tells Excel to switch to Data1 sheet –Worksheets(“Data1”).Activate-. But, since the code that writes the data on to the worksheet specifies the worksheet by name, it could be omitted and the data would still be written in the correct place.
The user can run this macro from the menu in the usual way (Tools > Macro > Macros) or you could assign it to a custom menu item, toolbar button, a button on the worksheet or a drawing object.
In this our template ,we drew a shape on the worksheet to open the userform and assigned to this shape the macro that it contained the above codes to open the userform :
◆ Opening the UserForm Automatically
We can make use of one of Excel’s built-in event procedures to open the UserForm automatically when the workbook is opened. In the Visual Basic Editor locate and let’s double-click the ThisWorkbook module in the Project Explorer. This module exists to hold macros specific to the workbook itself.
At the top of the code window there are two drop-down lists. The left-hand one will currently read General. Let’s open the list and choose Workbook. The Visual Basic Editor automatically creates the Workbook_Open macro for user. Any code we place in this macro will be executed automatically when the workbook opens. (If we want to see what else we can do here , let’s take a look at the other items on the right-hand list.)
Let’s complete the macro code as follows :
Private Sub Workbook_Open()
Worksheets(“Sheet1”).Activate
frmExpenses.Show
End Sub
We can make use of one of Excel’s built-in event procedures to open the UserForm automatically when the workbook is opened. In the Visual Basic Editor locate and let’s double-click the ThisWorkbook module in the Project Explorer. This module exists to hold macros specific to the workbook itself.
At the top of the code window there are two drop-down lists. The left-hand one will currently read General. Let’s open the list and choose Workbook. The Visual Basic Editor automatically creates the Workbook_Open macro for user. Any code we place in this macro will be executed automatically when the workbook opens. (If we want to see what else we can do here , let’s take a look at the other items on the right-hand list.)
Let’s complete the macro code as follows :
Private Sub Workbook_Open()
Worksheets(“Sheet1”).Activate
frmExpenses.Show
End Sub
► Complete Code Of the UserForm
Here is a complete listing of the code in the UserForm’s code module :
Here is a complete listing of the code in the UserForm’s code module :
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdClear_Click()
‘ Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Then
ctl.Value = “”
ElseIf TypeName(ctl) = “CheckBox” Then
ctl.Value = False
End If
Next ctl
End Sub
Private Sub cmdOK_Click()
Dim RowCount As Long
Dim ctl As Control
‘ Check user input
If Me.txtFirstName.Value = “” Then
MsgBox “Please enter a First Name.”, vbExclamation, “Personnel Expenses”
Me.txtFirstName.SetFocus
Exit Sub
End If
If Me.txtLastName.Value = “” Then
MsgBox “Please enter a Last Name.”, vbExclamation, “Personnel Expenses”
Me.txtFirstName.SetFocus
Exit Sub
End If
If Me.cboDepartment.Value = “” Then
MsgBox “Please choose a Department.”, vbExclamation, “Personnel Expenses”
Me.txtFirstName.SetFocus
Exit Sub
End If
If Me.txtDate.Value = “” Then
MsgBox “Please enter a Date.”, vbExclamation, “Personnel Expenses”
Me.txtFirstName.SetFocus
Exit Sub
End If
If Me.txtAmount.Value = “” Then
MsgBox “Please enter an Amount.”, vbExclamation, “Personnel Expenses”
Me.txtFirstName.SetFocus
Exit Sub
End If
If Me.txtDescription.Value = “” Then
MsgBox “Please enter a Description.”, vbExclamation, “Personnel Expenses”
Me.txtFirstName.SetFocus
Exit Sub
End If
If Not IsNumeric(Me.txtAmount.Value) Then
MsgBox “The Amount box must contain a number.”, vbExclamation, “Personnel Expenses”
Me.txtAmount.SetFocus
Exit Sub
End If
If Not IsDate(Me.txtDate.Value) Then
MsgBox “The Date box must contain a date.”, vbExclamation, “Personnel Expenses”
Me.txtDate.SetFocus
Exit Sub
End If
‘ Write data to worksheet
RowCount = Worksheets(“Data1”).Range(“A1”).CurrentRegion.Rows.Count
With Worksheets(“Data1”).Range(“A1”)
.Offset(RowCount, 0).Value = Me.txtFirstName.Value
.Offset(RowCount, 1).Value = Me.txtLastName.Value
.Offset(RowCount, 2).Value = Me.cboDepartment.Value
.Offset(RowCount, 3).Value = DateValue(Me.txtDate.Value)
.Offset(RowCount, 4).Value = Me.txtAmount.Value
.Offset(RowCount, 5).Value = Me.txtDescription.Value
.Offset(RowCount, 6).Value = Format(Now, “dd/mm/yyyy hh:nn:ss”)
If Me.chkReceipt.Value = True Then
.Offset(RowCount, 7).Value = “Yes”
Else
.Offset(RowCount, 7).Value = “No”
End If
End With
‘ Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Then
ctl.Value = “”
ElseIf TypeName(ctl) = “CheckBox” Then
ctl.Value = False
End If
Next ctl
End Sub
I couldn't download it, can you send it to my e-mail address?
ReplyDeleteWhat is your e-mail ?
Delete