Word Hacks [Electronic resources]

Hack 57 Get Simple User Input for a Macro

Macros that interact with the user can be very useful. This hack shows you how to get feedback from a user without the overhead or complexity of a VBA UserForm.

When you get input from a user (often yourself), you may want to present the person with several choices. You can use a UserForm with radio buttons or checkboxes, but it might add unnecessary overhead and complexity. Instead, use the dialogs already built into VBA, such as the MsgBox and InputBox.

For example, if you just need the user to select between two choicessay, "Red" or "Blue"you could display a standard MsgBox with its included "Yes" and "No" buttons. The following macro displays the dialog shown in Figure 7-1:

Sub PickRedOrBluePlease( )
Dim lResponse As Long
lResponse = MsgBox(Prompt:="Press Yes for Red, Press No for Blue", _
Buttons:=vbYesNo, _
Title:="Pick a color")
If lResponse = vbYes Then
MsgBox "You picked Red"
Else
MsgBox "You picked Blue"
End If
End Sub

Note the disabled Close button on the dialog's titlebar. To offer the choice of canceling the dialog, use the vbYesNoCancel value for the Buttons parameter.

But what if you need more than two or three choices? How about choosing a number between 1 and 7? Try an InputBox and validate the response:

Sub ChooseAmongSeven( )
Dim sResponse As String
sResponse = InputBox(prompt:="Please choose a number from 1 to 7", _
Title:="Pick a Number", _
Default:=CStr(5))
' Dialog was canceled
If Len(sResponse) = 0 Then Exit Sub
If Not sResponse Like "[1-7]" Then
MsgBox Chr(34) & sResponse & Chr(34) & _
" is not a number between 1 and 7", _
vbInformation
Else
MsgBox "You picked: " & sResponse
End If
End Sub

If the user fails to enter a whole number between 1 and 7, the macro displays the dialog in Figure 7-2.

7.2.1 Hacking the Hack

If the user does not enter a valid choice, you can continue to offer the dialog instead of exiting the macro. The following code uses a Do loop to repeatedly prompt the user for a valid choice:

Sub PromptUntilValid( )
Dim sResponse As String
Do
sResponse = InputBox(prompt:="Please choose a number from 1 to 7", _
Title:="Pick a Number", _
Default:=CStr(5))
If Len(sResponse) = 0 Then Exit Sub
If Not sResponse Like "[1-7]" Then
MsgBox Chr(34) & sResponse & Chr(34) & _
" is not a number between 1 and 7", _
vbInformation
Else
MsgBox "You picked: " & sResponse
End If
Loop Until sResponse Like "[1-7]"
End Sub