Get information from user with Application.Inputbox

If you want information from a user you can use for example a UserForm, but in Mac Excel you are not able to add a UserForm to your workbook in the VBE UserInterface. You must develop in Excel for Windows or maybe Application.Inputbox is an option to use. Be aware that there is also a VBA.Inputbox but this have no option to set the datatype and not have an option to see if the Cancel button is pressed.

Below you find 3 macro examples to get a range, number and text with Application.Inputbox.

Note: if you sum the Type numbers you can specify more than one datatype, if you use 3 you can enter text or numbers for example.


Sub RangeExample()
Dim myRange As Range

On Error Resume Next
Set myRange = Application.InputBox(prompt:="Select a Range", Type:=8)
On Error GoTo 0

If myRange Is Nothing Then
MsgBox "You press Cancel"
Else
myRange.Select
End If

End Sub


Sub NumberExample()
Dim MyNumber As Variant

MyNumber = Application.InputBox(prompt:="Enter a number", Title:="This is the title", Default:="", Type:=1)

If MyNumber = False Then
MsgBox "You press Cancel"
Else
MsgBox MyNumber
End If

End Sub

Sub TextExample()
Dim MyText As Variant

MyText = Application.InputBox(prompt:="Enter a text", Title:="This is the title", Default:="", Type:=2)

If MyText = False Then
MsgBox "You press Cancel"
Else
MsgBox MyText
End If

End Sub
31-July-2021
Web design by Will Woodgate