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

Figure 7-1. Hack VBA's built-in dialogs to get simple user input

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.

Figure 7-2. Using VBA's built-in dialogs to provide interaction from a macro

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