top of page

Macros with VBE

  • There are several ways to run a macro in Microsoft Excel.

  • A macro is an action or a set of actions that you can use to automate tasks.

WHAT IS VBE?

  • On the Developer tab, click Visual Basic to launch the Visual Basic Editor.

  • Browse the Porject Explorer to the module that contains the macro you want to run, and open it.

  • Select the macro you want to run, by placing your cursor anywhere within the macro, and press F5, .

  • Or the menu, go to Run > Run Macro.

VBA (Visual Basic for Application):

  • VBA (Visual Basic for Applications) is the programming language of Excel and other Office programs.

USER FORM WITH VBA

  • Here we will create an Excel VBA Userform with following steps.

  • Open Visual Basic Editor on Excel sheet to make a form and write the script for the form.

Alt + F11

  • Add Controls:

  • Open the Visual Basic Editor.

  • If the Project Explorer is not visible, click View > Project Explorer.

  • Click Insert > Userform. If the toolbox does not apper automatically, click View > Toolbox.

  • You screen should be set up as below.

  • Add controls listed in the table below.

  • Drag the tools from toolbox and add to the User Form.

  • Change the names and captions of the controls accroding to the table below.

  • Names are used in teh Excel VBA code.

Drag tools form here.

Set tools here.

Make the form as shown in left.

Change the name & caption of tools.

  • Put the code into Form buttons:

  • Open the Visual Basic Editor.

  • In the Project explorer, right click on DinnerPlannerUserForm and click View Code.

  • Choose UserForm from the left drop-down list > choose Initialize from the right drop-down list.

  • Add the the following code lines.

Right Click > View Code

Private Sub 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"
End With

'Empty DinnerComboBox
DinnerComboBox.Clear

'Fill DinnerComboBox
With DinnerComboBox
    .AddItem "Italian"
    .AddItem "Chinese"
    .AddItem "Frites and Meat"
End With

'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

End Sub

  • Assign Macros to OK, Clear, Cancel and MonyeSpinButton the below code.

1
2
4
3

Double click on buttons above to assign codes.

1

Assign code to OK button.

Private Sub OKButton_Click()

Dim emptyRow As Long

'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 = True Then Cells(emptyRow, 5).Value = DateCheckBox1.Caption

If DateCheckBox2.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox2.Caption

If DateCheckBox3.Value = True Then Cells(emptyRow, 5).Value = Cells(emptyRow, 5).Value & " " & DateCheckBox3.Caption

If CarOptionButton1.Value = True Then
    Cells(emptyRow, 6).Value = "Yes"
Else
    Cells(emptyRow, 6).Value = "No"
End If

Cells(emptyRow, 7).Value = MoneyTextBox.Value

End Sub

2

Assign code to Clear button.

Private Sub ClearButton_Click()

       Call UserForm_Initialize

End Sub

3

Assign code to Cancel button.

Private Sub CancelButton_Click()

         Unload Me

End Sub

4

Assign code to MoneySpinButton.

Private Sub MoneySpinButton_Change()

         MoneyTextBox.Text = MoneySpinButton.Value

End Sub

  • Test the USERFORM:

  • Right click on commandbutton1 and write the code above.

  • Save the VBA macros in workbook and give some name.

  • Save as Excel-Enabled Workbook format type, not in normal Excel file type.

Private Sub CommandButton1_Click()
    DinnerPlannerUserForm.Show
End Sub

  • Exit the Visual Basic Editor.

  • Click on CommantButton1.

  • That will call a funtion UserForm.

  • Fill the form, and click OK, Clear or Cancel.

  • If you click on OK your detail will fill onto rows as follow.

After click OK, the form data will fill in excel.

1
2
3

Short Key for run the progrom in Visual Basic Editor.

For run the program:

Ctrl

F5

+

Step into code line by line. 

Ctrl

+

F8

bottom of page