Excel Vba Userform Templates Downloads

Add the Controls | Show the Userform | Assign the Macros | Test the Userform

Excel Vba Userform Templates Downloads Download
Excel Vba Userform Templates Downloads Pdf
Excel Vba Userform Templates Downloads Excel

Download Excel Vba Userform Examples Download Pin by Kadr Leyn on Excel Tutorials and Excel Advanced 9 Excel Vba Templates ExcelTemplates ExcelTemplates Attendance Sheet Printable Excel Template in Userform. Userform image in excel vba explained with examples image is one of the userform control you can select and drag image on the userform.
Excel Vba Userform Examples Free Download. Saved by Alinezhad. Vba Excel Microsoft Office Project Management Bar Chart Templates Gypsum Free Programming Ms.
ListBox in Excel VBA: A Sample Supermarket Application to show examples on adding new items, clearing, selecting multiple items and changing various options of ListBox. ANALYSISTABS-ListBox: Collections in Excel VBA: Download the files and see the examples one Collections.

Awesome free excel userform templates. Over 200 excel templates. Any free availble templates would be very appreciative. Open the control toolbox and select a combobox and add it to the userform. Download our free example macro files tools dashboards templates for practicing excel vba and developing new tools for automating data analysis tasks. In the Excel worksheet, go to the developer’s tab and click on the visual basic editor. Now in the insert tab, click on userform to open a userform window. We need to add buttons to this userform in order to get input from the user. We can see that a toolbox is also opened beside our userform, which has the control buttons.

This chapter teaches you how to create an Excel VBA Userform. The Userform we are going to create looks as follows:

Add the Controls

To add the controls to the Userform, execute the following steps.

1. Open the Visual Basic Editor. If the Project Explorer is not visible, click View, Project Explorer.

2. Click Insert, Userform. If the Toolbox does not appear automatically, click View, Toolbox. Your screen should be set up as below.

3. Add the controls listed in the table below. Once this has been completed, the result should be consistent with the picture of the Userform shown earlier. For example, create a text box control by clicking on TextBox from the Toolbox. Next, you can drag a text box on the Userform. When you arrive at the Car frame, remember to draw this frame first before you place the two option buttons in it.

4. Change the names and captions of the controls according to the table below. Names are used in the Excel VBA code. Captions are those that appear on your screen. It is good practice to change the names of controls. This will make your code easier to read. To change the names and captions of the controls, click View, Properties Window and click on each control.

Control	Name	Caption
Userform	DinnerPlannerUserForm	Dinner Planner
Text Box	NameTextBox
Text Box	PhoneTextBox
List Box	CityListBox
Combo Box	DinnerComboBox
Check Box	DateCheckBox1	June 13th
Check Box	DateCheckBox2	June 20th
Check Box	DateCheckBox3	June 27th
Frame	CarFrame	Car
Option Button	CarOptionButton1	Yes
Option Button	CarOptionButton2	No
Text Box	MoneyTextBox
Spin Button	MoneySpinButton
Command Button	OKButton	OK
Command Button	ClearButton	Clear
Command Button	CancelButton	Cancel
7 Labels	No need to change	Name:, Phone Number:, etc.

Note: a combo box is a drop-down list from where a user can select an item or fill in his/her own choice. Only one of the option buttons can be selected.

Show the Userform

To show the Userform, place a command button on your worksheet and add the following code line:

PrivateSub CommandButton1_Click()
DinnerPlannerUserForm.Show
EndSub

We are now going to create the Sub UserForm_Initialize. When you use the Show method for the Userform, this sub will automatically be executed.

1. Open the Visual Basic Editor.

2. In the Project Explorer, right click on DinnerPlannerUserForm and then click View Code.

3. Choose Userform from the left drop-down list. Choose Initialize from the right drop-down list.

4. Add the following code lines:

PrivateSub UserForm_Initialize()
'Empty NameTextBox
NameTextBox.Value = '
'Empty PhoneTextBox
PhoneTextBox.Value = '
'Empty CityListBox
CityListBox.Clear
'Fill CityListBox
With CityListBox
.AddItem 'San Francisco'
.AddItem 'Oakland'
.AddItem 'Richmond'
EndWith
'Empty DinnerComboBox
DinnerComboBox.Clear
'Fill DinnerComboBox
With DinnerComboBox
.AddItem 'Italian'
.AddItem 'Chinese'
.AddItem 'Frites and Meat'
EndWith

'Uncheck DataCheckBoxes
DateCheckBox1.Value = False
DateCheckBox2.Value = False
DateCheckBox3.Value = False
'Set no car as default
CarOptionButton2.Value = True
'Empty MoneyTextBox
MoneyTextBox.Value = '
'Set Focus on NameTextBox
NameTextBox.SetFocus
EndSub

Explanation: text boxes are emptied, list boxes and combo boxes are filled, check boxes are unchecked, etc.

Assign the Macros

We have now created the first part of the Userform. Although it looks neat already, nothing will happen yet when we click the command buttons on the Userform.

1. Open the Visual Basic Editor.

2. In the Project Explorer, double click on DinnerPlannerUserForm.

3. Double click on the Money spin button.

4. Add the following code line:

PrivateSub MoneySpinButton_Change()
MoneyTextBox.Text = MoneySpinButton.Value
EndSub

Explanation: this code line updates the text box when you use the spin button.

5. Double click on the OK button.

6. Add the following code lines:

PrivateSub OKButton_Click()
Dim emptyRow AsLong
'Make Sheet1 active
Sheet1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range('A:A')) + 1
'Transfer information
Cells(emptyRow, 1).Value = NameTextBox.Value
Cells(emptyRow, 2).Value = PhoneTextBox.Value
Cells(emptyRow, 3).Value = CityListBox.Value
Cells(emptyRow, 4).Value = DinnerComboBox.Value
If DateCheckBox1.Value = TrueThen Cells(emptyRow, 5).Value = DateCheckBox1.Caption
If DateCheckBox2.Value = TrueThen Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & ' ' & DateCheckBox2.Caption
If DateCheckBox3.Value = TrueThen Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & ' ' & DateCheckBox3.Caption
If CarOptionButton1.Value = TrueThen
Cells(emptyRow, 6).Value = 'Yes'
Else
Cells(emptyRow, 6).Value = 'No'
EndIf
Cells(emptyRow, 7).Value = MoneyTextBox.Value
EndSub

Explanation: first, we activate Sheet1. Next, we determine emptyRow. The variable emptyRow is the first empty row and increases every time a record is added. Finally, we transfer the information from the Userform to the specific columns of emptyRow.

7. Double click on the Clear button.

8. Add the following code line:

PrivateSub ClearButton_Click()
Call UserForm_Initialize
EndSub

Explanation: this code line calls the Sub UserForm_Initialize when you click on the Clear button.

9. Double click on the Cancel Button.

Excel Vba Userform Templates Downloads Download

10. Add the following code line:

Explanation: this code line closes the Userform when you click on the Cancel button.

Test the Userform

Excel Vba Userform Templates Downloads Pdf

Exit the Visual Basic Editor, enter the labels shown below into row 1 and test the Userform.

Excel Vba Userform Templates Downloads Excel

Result: