davesexcel.com

Excel information

Visual on creating a UserForm

Excel 2003 is used for this example and will work in other versions of Excel as well

 

Open the Visual Basics Toolbar

 

 Click the Visual Basics Icon to go to the VBA Editor and insert a UserForm


Draw the TextBox

Draw the Command Button


 

Change the UserForm Caption

 

 

Change the Command Button Caption

Prepare to code the Command Button

 Copy this code from the website

 Paste it in the Command Button

 

Edit the Code

 The code now looks like this

Private Sub CommandButton1_Click()

    Dim Rws As Long, Rng As Range


    Rws = Cells(Rows.Count, "A").End(xlUp).Row + 1    'Finds First Empty Row after last entry

    Set Rng = Cells(Rws, 1)

    Rng = TextBox1    'Places whatever is in textbox1 into the first empty cell after the last cell

    Unload Me    'closes the Userform

End Sub

 

 

 

Close the VBA Editor, or Press Alt & F11

 

Create a Button On the Spreadsheet

 

 

 

or

 

 

 

Type in code and exit from VBA Editor

 

 

 

 Create the list

 Click the Button to Show the Userform

 

 

 

 

 

 

 Download the example

CreateUserform.xls

CreateUserformXl2007.xlsm