CODEWITHSUNDEEP
vbaexcel
Josh Baker
Josh Baker

Reputation: 3

InputBox Range Selection from Userform

I am trying to use an inputbox to select a range in a worksheet. In theory it is simple. The user selects a commandbutton on the worksheet which opens a userform. The userform allows for the selection of one of several optionbuttons. Once the form is submitted I want the user to be able to select a range from the active sheet.

After the userform submission, the range inputbox appears, but does not allow for selection on the active sheet.

If I call the inputbox directly from the sheet (do not run through the userform) the input box works properly. However, in this application I need the userform to allow for a selection.

I have tried re-activating the sheet, programmatically selecting a default range, hiding and unloading the userform.

Fundamentally this is all I am doing:

Public Function functionName()

    Dim rng As Range

    Set rng = Application.InputBox("select range", Type:=8)

End Function

It just does not seem to work when it is called from a module by a userform.

Upvotes: 0

Views: 1901

Answers (2)

SierraOscar
SierraOscar

Reputation: 17637

It sounds like your user form is being displayed as Modal.

By default, Userforms are displayed as modal which means that code execution will halt until the user interacts with the form in some way. This also prevents the user from doing things like selecting ranges or changing sheets etc.

You can show a form as non-modal either by changing the "Modal" property in the VBE design window - or supplying the argument when you call the form:

MyUserForm.Show           '// Uses default modal property of True*
MyUserForm.Show True      '// Implicitly state that the form should be modal.
MyUserForm.Show False     '// Implicitly state that the form should NOT be modal.

*Assuming the ShowModal property has been left as the default 'True' at design time.

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

Assuming your function to have the user get a range looks like this (note that I put this in a standard module, not in the userform code):

Public Function GetRange() As Range

    Dim rInput As Range

    On Error Resume Next
    Set rInput = Application.InputBox("select range", Type:=8)
    On Error GoTo 0

    If rInput Is Nothing Then
        Set GetRange = Nothing
    Else
        Set GetRange = rInput
    End If

End Function

Then your code upon closing the userform and having the user select a range should look like this (note that I put this in a commandbutton which closes the userform first and then asks the user to select the range. Put this wherever your Unload Me is located):

Private Sub CommandButton1_Click()

    Unload Me

    Dim r As Range

    'Get the range from the input box and verify user did not press cancel
    On Error Resume Next
    Set r = GetRange
    On Error GoTo 0
    If r Is Nothing Then Exit Sub   'Pressed cancel so no range is returned, cannot proceed

    'Do what you want with the selected range here
    MsgBox "'" & r.Parent.Name & "'!" & r.Address

End Sub

Upvotes: 1

Related Questions