Home
Data Entry Wizard by Userform in Excel VBA Easily

Data Entry Wizard by Userform in Excel VBA Easily

Excel VBA Macros April 1, 2023

Welcome to Excel Avon

Data entry wizard in UserForm

DOWNLOAD USED EXCEL FILE FROM HERE

In today’s post, we will explain that how to create a Data entry wizard box in Excel VBA, as well as use the options of UserForm, which you have already read, in the previous post, we gave you the introduction of Multipages by UserForm in Excel VBA. Here we are going to use textbox, label, command button, option button, multipage and frame in creating Data entry wizard and as we know. Data entry wizards make complex tasks easier and more accurate.

A data entry wizard helps you divide a long form into a series of screens, where users can enter data on each screen as they proceed to the next step until completion. A stepwise approach is less overwhelming and reduces chances of erroneous data entry, as the partially completed form can be saved as draft and submitted later. Let’s go to VBE for data entry wizard.

Create Data Entry Wizard in Userform

Well, you must know how to insert UserForm, as we have learned in many posts till now, you can insert userform. Let us teach you how we include the UserForm. Let’s understand, so we have to go like last time, first go to the Developer tab, then click on the option for Visual Basic as shown in the image below. 

sort-data-on-excel-using-VBA

On opening in VBE, you have to go to Insert and then UserForm has to be inserted, as can be seen in the image.

introduction-of-userform

When we click on the User form, a separate wizard box will be created in a window VBE, in which you can see UserForm1 as the default name. You can increase or decrease the size of the UserForm with the mouse pointer.

Data-entry-with-wizardbox

First of all, drag a multipage from the userform’s toolbox.

data entry wizard

In Multipage1 we will insert the frame. The caption of the frame will be changed by going to the frame properties, here we are creating a wizard for personal details.

data entry wizard in userform

We’ll select the Userform Toolbox label and then drag it onto the Userform in the Multipage Wizard. By going to the properties of the label, there will be a label caption. Change the title of the label.

data entry wizard in userform1

To create a wizard box, put a textbox on the frame. We will use this textbox to fill in the first name. Rename this textbox as well (txtName1).

data entry wizard in userform2

Copy the label and text box as a group and then paste. Change the caption of the pasted label (last name). Change the name of the textbox along with the caption of the label(txtName2).

data entry wizard in userform3

Add the frame to Gender in the wizard box, and then change the Caption to Gender.

data entry wizard in userform4

Now add option buttons in wizard box for Male, Female and Transgender in the Gender frame and give the caption of all option buttons as (Male, Female and Transgender). After changing the caption, we will change the name of the option button (optMale,optFemale and optTrans).

data entry wizard in userform5

We will insert the label here, along with the label, we will also insert a textbox which will be for the date of birth, then change the caption of the label. Change the name of DOB textBox(txtDOB).

data entry wizard in userform6

It is also necessary to have the name of mother and father in the form of a student, so we will insert two labels and two test boxes and then change the caption of the label. Change the name of father’s and mother’s textbox name (txtfather & txtmother).

data entry wizard in userform7

Now we will design the second page in which we will give frame for the address as well as fill the contact details and change the caption of the frame by going to the property of the frame.

data entry wizard in userform8

Now in this address frame we will insert some labels whose caption will be for street address, city, state and zipcode respectively. 

data entry wizard in userform9.png

We will insert another frame next to the address frame whose caption we will change by going to the properties of the frame (Contact).

data entry wizard in userform10

And then insert labels into this frame (contact) and some testboxes with labels, and change the captions of all these labels and testboxes by going to properties.

data entry wizard in userform11

We will change the caption of all these labels and the name of the textbox from the properties.

data entry wizard in userform12

Now we will insert the command button in the userform, change the caption of the command button to submit. Here we have to add 3 command buttons, the first one will be to go to the next page, the second one will be for close and the third button will be inserted for previous, we will change the caption of all these buttons.

data entry wizard in userform15

After changing the caption of all these buttons, we will also change the names of the buttons like btnpre, btnnext and btnclose.

data entry wizard in userform16

Now we will write code for close command.

Private Sub btnClose_Click()
Unload Me
End Sub

Here we will right click on the userform and then click on View Code.

data entry wizard in userform13

Now we will run the initialize event here.

data entry wizard in userform14

Here we are writing the code for the first page, first of all we will disable the previous code for the first page here.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

And now we will run the code.

data entry wizard in userform17

After running the code, we can see that the previous page is disabled in the first page.

data entry wizard in userform18

After closing the data form, we will come back to VBE and then here we will create the code for pay next button. Then make condition here the value of page1 is equal to one less than the value of page.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
End If
End Sub

Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Create a condition if the value of multipage1 in the userform is equal to last page  value of multipage1 if the caption of the ‘next’ button in the userform is not ‘submit’. which will work in the created wizard box.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
End Sub
End If
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

This is different condition Else the caption of the next button will remain next.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Now we will create condition for page 2 if we are in page 2 then previous button will be enabled in page2.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Let us run the userform one more time and see if the command button we have inserted is working. See, when we are in the first page, the previous page will not be active, but as soon as we go to page 2, the button will be activated.

data entry wizard in userform18

By closing the wizard box, we will write the script for the previous button.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Create condition if we are in page 2 then the value of multipage1 will be equal to one less than the value of multipage1.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

The caption of the next button in the userform will be Next.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Here we will define some variables like long and for worksheet.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

After defining the variable, we will set the worksheet as active sheet.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
Set WS = ActiveSheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Here we will create one more condition which will be in the subroutine of next button, if the caption of the next button in the UserForm is submit, then declare the last row.

Private Sub btnClose_Click()
Unload Me
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
Set WS = ActiveSheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.btnNext.Caption = "Submit" Then
   lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

After the detail is filled in the user form, that detail will be stored in all the cells, for this we will declare the worksheet cell range like separate range for first, separate cell for last name, then for date of birth gender.

Private Sub btnClose_Click() 
Unload Me 
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
Set WS = ActiveSheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.btnNext.Caption = "Submit" Then
   lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
   WS.Range("A" & lr).Value = Me.txtName1.Value
   WS.Range("B" & lr).Value = Me.txtName2.Value
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Let’s continue the same condition, now for gender, if gender frame optmale button is selected, then cell c will be filled with mail. Similarly now for female, select the gender frame optFemale button then cell c will be filled with female And the same will happen for Transgender, if gender frame selects the opttrans button, then Transgender will be filled in cell c.

Private Sub btnClose_Click() 
Unload Me 
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
Set WS = ActiveSheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.btnNext.Caption = "Submit" Then
   lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
   WS.Range("A" & lr).Value = Me.txtName1.Value
   WS.Range("B" & lr).Value = Me.txtName2.Value
   If Me.optMale = True Then
      WS.Range("C" & lr).Value = "Male"
   ElseIf Me.optFemale = True Then
      WS.Range("C" & lr).Value = "Female"
   ElseIf Me.optTrans = True Then
      WS.Range("C" & lr).Value = "Transgender"
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Similarly declare the range of cells for all the details.

Private Sub btnClose_Click() 
Unload Me 
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
Set WS = ActiveSheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.btnNext.Caption = "Submit" Then
   lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
   WS.Range("A" & lr).Value = Me.txtName1.Value
   WS.Range("B" & lr).Value = Me.txtName2.Value
   If Me.optMale = True Then
      WS.Range("C" & lr).Value = "Male"
   ElseIf Me.optFemale = True Then
      WS.Range("C" & lr).Value = "Female"
   ElseIf Me.optTrans = True Then
      WS.Range("C" & lr).Value = "Transgender"
    WS.Range("D" & lr).Value = Me.txtDOB.Value
    WS.Range("E" & lr).Value = Me.txtFather.Value
    WS.Range("F" & lr).Value = Me.txtMother.Value

    WS.Range("G" & lr).Value = Me.txtStreet.Value
    WS.Range("H" & lr).Value = Me.txtCity.Value
    WS.Range("I" & lr).Value = Me.txtState.Value
    WS.Range("J" & lr).Value = Me.txtZIP.Value

    WS.Range("K" & lr).Value = Me.txtPhone.Value
    WS.Range("L" & lr).Value = Me.txtPhone2.Value
    WS.Range("M" & lr).Value = Me.txtEmail.Value 
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Define another variable Ctrl as Control and we create Loop with using Each statement.

Private Sub btnClose_Click() 
Unload Me 
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
Dim Ctrl As Control
Set WS = ActiveSheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.btnNext.Caption = "Submit" Then
   lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
   WS.Range("A" & lr).Value = Me.txtName1.Value
   WS.Range("B" & lr).Value = Me.txtName2.Value
   If Me.optMale = True Then
      WS.Range("C" & lr).Value = "Male"
   ElseIf Me.optFemale = True Then
      WS.Range("C" & lr).Value = "Female"
   ElseIf Me.optTrans = True Then
      WS.Range("C" & lr).Value = "Transgender"
    WS.Range("D" & lr).Value = Me.txtDOB.Value
    WS.Range("E" & lr).Value = Me.txtFather.Value
    WS.Range("F" & lr).Value = Me.txtMother.Value

    WS.Range("G" & lr).Value = Me.txtStreet.Value
    WS.Range("H" & lr).Value = Me.txtCity.Value
    WS.Range("I" & lr).Value = Me.txtState.Value
    WS.Range("J" & lr).Value = Me.txtZIP.Value

    WS.Range("K" & lr).Value = Me.txtPhone.Value
    WS.Range("L" & lr).Value = Me.txtPhone2.Value
    WS.Range("M" & lr).Value = Me.txtEmail.Value
    For Each Ctrl In Me.Controls
    Next Ctrl
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

Here we’ll create a condition for control, If the control is of text type, then the value of control will be nothing. else if the control is of command button type the control will be disabled. 

Private Sub btnClose_Click() 
Unload Me 
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
Dim Ctrl As Control
Set WS = ActiveSheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.btnNext.Caption = "Submit" Then
   lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
   WS.Range("A" & lr).Value = Me.txtName1.Value
   WS.Range("B" & lr).Value = Me.txtName2.Value
   If Me.optMale = True Then
      WS.Range("C" & lr).Value = "Male"
   ElseIf Me.optFemale = True Then
      WS.Range("C" & lr).Value = "Female"
   ElseIf Me.optTrans = True Then
      WS.Range("C" & lr).Value = "Transgender"
    WS.Range("D" & lr).Value = Me.txtDOB.Value
    WS.Range("E" & lr).Value = Me.txtFather.Value
    WS.Range("F" & lr).Value = Me.txtMother.Value

    WS.Range("G" & lr).Value = Me.txtStreet.Value
    WS.Range("H" & lr).Value = Me.txtCity.Value
    WS.Range("I" & lr).Value = Me.txtState.Value
    WS.Range("J" & lr).Value = Me.txtZIP.Value

    WS.Range("K" & lr).Value = Me.txtPhone.Value
    WS.Range("L" & lr).Value = Me.txtPhone2.Value
    WS.Range("M" & lr).Value = Me.txtEmail.Value
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Ctrl.Value = ""
        ElseIf TypeName(Ctrl) = "OptionButton" Then
            Ctrl.Value = False
        End If
    Next Ctrl
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

When the details are submitted, it will come back to the page first, so here after the details are submitted, we will get a message ‘Data submitted successfully’ and also disable the previous button.

Private Sub btnClose_Click() 
Unload Me 
End Sub
Private Sub btnNext_Click()
Dim lr As Long
Dim WS As Worksheet
Dim Ctrl As Control
Set WS = ActiveSheet
If Me.btnNext.Caption <> "Submit" Then
    Me.MultiPage1.Value = Me.MultiPage1.Value + 1
End If
If Me.btnNext.Caption = "Submit" Then
   lr = WS.Range("A" & Rows.Count).End(xlUp).Row + 1
   WS.Range("A" & lr).Value = Me.txtName1.Value
   WS.Range("B" & lr).Value = Me.txtName2.Value
   If Me.optMale = True Then
      WS.Range("C" & lr).Value = "Male"
   ElseIf Me.optFemale = True Then
      WS.Range("C" & lr).Value = "Female"
   ElseIf Me.optTrans = True Then
      WS.Range("C" & lr).Value = "Transgender"
    WS.Range("D" & lr).Value = Me.txtDOB.Value
    WS.Range("E" & lr).Value = Me.txtFather.Value
    WS.Range("F" & lr).Value = Me.txtMother.Value

    WS.Range("G" & lr).Value = Me.txtStreet.Value
    WS.Range("H" & lr).Value = Me.txtCity.Value
    WS.Range("I" & lr).Value = Me.txtState.Value
    WS.Range("J" & lr).Value = Me.txtZIP.Value

    WS.Range("K" & lr).Value = Me.txtPhone.Value
    WS.Range("L" & lr).Value = Me.txtPhone2.Value
    WS.Range("M" & lr).Value = Me.txtEmail.Value
    For Each Ctrl In Me.Controls
        If TypeName(Ctrl) = "TextBox" Then
            Ctrl.Value = ""
        ElseIf TypeName(Ctrl) = "OptionButton" Then
            Ctrl.Value = False
        End If
    Next Ctrl
    Me.MultiPage1.Value = 0
    Me.btnPre.Enabled = False
    MsgBox "Data submitted successfully!", vbInformation
End If
If Me.MultiPage1.Value = Me.MultiPage1.Pages.Count - 1 Then
    Me.btnNext.Caption = "Submit"
Else
    Me.btnNext.Caption = "Next"
End If
If Me.MultiPage1.Value > 0 Then
    Me.btnPre.Enabled = True
End If
End Sub

Private Sub btnPre_Click()
If Me.MultiPage1.Value > 0 Then
Me.MultiPage1.Value = Me.MultiPage1.Value - 1
End If
Me.btnNext.Caption = "Next"
End Sub
Private Sub UserForm_Initialize()
Me.MultiPage1.Value = 0
Me.btnPre.Enabled = False
End Sub

After doing this, you have to go to Insert tab and then Module has to be inserted, as can be seen in the image.

Sort-data-on-excel-using-vba

Once the module is inserted, and we will write a subroutine for the sub OpenForm. Created a macro that will open only the wizard form in the sheet.

Sub OpenForm()
UserForm1.Show
End Sub

Open the worksheet and go to insert tab drag a button for assign macro.

DATA ENTRY BY USERFORM IN EXCEL VBA5

First, name the button and then, we’ll assign Macro. Select macro and click Ok.   

data entry wizard in userform18.png

Here we have named the button as open form because this button will open the form. click the button.

data entry wizard in userform20

Now we can see the pay wizard form here and we have also filled personal details in it, now we will use the ‘Next’ button.

data entry wizard in userform25

When we will click on the Next button here another page will appear where we will have to fill the address and contact details. Now here after filling all the details in the wizard we will click on the submit button and if we want to go to the previous page (personal page) we can access the previous page.

data entry wizard in userform22

After clicking the submit button we will get the message here.

data entry wizard in userform23

Now you can see in the worksheet that the data has been stored here.

data entry wizard in userform24

Now we will make the style of wizard box non and from which the tab style will be removed then we will see the wizards without tabs.

data entry wizard in userform21

Therefore, I hope that you have understood Data Entry Wizard by Userform in Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.

LEARN MORE TOPIC IN VBA HERE

DOWNLOAD USED EXCEL FILE FROM HERE

You can also see well-explained video here about Data Entry Wizard by Userform in Excel VBA

Related Posts

Leave a Reply