Userforms and inputboxes in Excel VBA

When you work with VBA macros in Excel, your program may need input from the user. If it is just a number, a text or a range of cells, you can use Excel's built-in Inputbox, but if it is more complex stuff, you need to design your own userform.

Sub AskForANumber()
Dim vInput
Dim bNumber As Boolean

On Error GoTo ErrorHandle:

'This stubborn Inputbox pops up again and
'again, until the user puts in a number.
Do Until bNumber = True
   vInput = InputBox("Write a number:")
   If IsNumeric(vInput) Then bNumber = True
Loop

'Multiplies the number by 2 and writes the result in cell A1
Range("A1").Value = vInput * 2

Exit Sub
ErrorHandle:
MsgBox Err.Description & " Procedure AskForANumber"
End Sub

Sub SelectRange()
Dim rCell As Range

On Error Resume Next

Worksheets(1).Activate

'Asks the user to select a cell or a range of cells.
'Type 8 tells the Inputbox that we are dealing with a
'range objekt.
Set rCell = Application.InputBox(prompt:="Select a cell or a range",Type:=8)

MsgBox "You selected " & rCell.Address

Set rCell = Nothing
End Sub

Userforms

For more complex tasks you must design your own userforms. In the VBA editor you choose "Insert" and "Userform". Then you get an empty form, where you can add controls like textboxes, checkboxes, lists, comboboxes, command buttons and much more.

MyForm.Show

That is if you have named the userform "MyForm". Usually the form is closed, when the user clicks a certain command button (e.g. "OK"), and this happens, if you add code to the button's click-procedure. You just write: "Unload Me". It looks like this:

Private Sub CommandButton1_Click()
Unload Me
End Sub

However before the command "Unload Me," you would usually write code that does something with the user input.

If you want something to happen, when the userform opens, you write it in the form's Initialize procedure:

Private Sub UserForm_Initialize()
'Action - what to do when the form opens.
'For instance this could be reading values from cells into textboxes.

End Sub

The userform and all the controls you put into it all have a series of standard events, that you can put code into. That could be what to do, when the user clicks on the control, presses a key and much more.

It can be tedious work to make userforms, especially if the spreadsheet will be used by somebody else. Users are notoriously unpredictable, and one must take care that whatever they do, you don't get an error.

Users will happily enter numbers when asked for a text (or vice versa), or they make a space before typing. If an error is possible, the user will trigger it by doing the unexpected.