Problem with focusing on Application.Inputbox type:= 8
My environment is
Excel 2000 SR-1
Win XP
How would you get a focus on Application.InputBox type:=8 in another
worksheet if the function is called from a command button in a
worksheet? Because of the focusing problem when a user clicks on the
other sheet, [Enter] does not work, so the user has to click on OK or
Cancel button to finish. Here is a code I have:
There is a command button cmdB in a worksheet sh1.
In the sh1 worksheet module,
Private Sub cmdB_Click()
Dim data As Range
Dim CancelClick As Boolean
Dim Prompt As String
Dim Title As String
Prompt = "foo"
Title = "foo"
'Select a data range
Call DataSelect(data, Prompt, Title, CancelClick)
If CancelClick Then Exit Sub
'some code here
end sub
In a standard module,
Sub DataSelect(DataRange As Range, Prompt As String, Title As String,
CancelClick As Boolean)
Dim DAddress As String
On Error GoTo Canceled
DAddress = Selection.Address
Set DataRange = Application.InputBox(Prompt:=Prompt, Title:=Title,
Default:=DAddress, Type:=8)
CancelClick = False
Exit Sub
Canceled:
CancelClick = True
End Sub
|