For an input box, I'd like "Cancel" to leave the existing value of something,
and "OK" with no input would replace the existing value with a zero-length
string, "" . On the help page, it appears that Cancel won't work the way I
need. Is there any way to do this?
Yes, but not with an InputBox, which makes no distinction between a Cancel and
an empty input.
You'll need a UserForm
(
http://www.word.mvps.org/FAQs/Userforms/CreateAUserForm.htm). In the Properties
pane, set the form's name to frmInput. Put the following controls on the form:
- a text box that you name txtInput
- a command button that you name btnOK, with the caption OK and with the Default
property set to True
- a command button that you name btnCancel, with the caption Cancel and with the
Cancel property set to True
In the userform's code page, paste this code:
Public Canceled As Boolean
Private Sub btnCancel_Click()
Canceled = True
Me.Hide
End Sub
Private Sub btnOK_Click()
Canceled = False
Me.Hide
End Sub
In a regular module, insert this code as a demonstration of how to use the
UserForm:
Sub demo()
Dim myInput As frmInput
Dim rslt As String
rslt = "Default text"
Set myInput = New frmInput
With myInput
.txtInput = rslt
.Show
If Not .Canceled Then
rslt = .txtInput
End If
End With
Set myInput = Nothing
MsgBox rslt ' for debugging
End Sub
The important bit here is the public variable Canceled, which is set to True if
the user clicks the Cancel button (or presses the Esc key or clicks the red X)
and is False otherwise. In the calling macro, the variable rslt keeps its
original value if Canceled is True. When Canceled is False, then rslt is
replaced by whatever is in the userform's text box -- and that may be an empty
string.
