CODEWITHSUNDEEP
excelvba
ChrisB
ChrisB

Reputation: 3205

Input Box to Select a Range or Enter Text

I have an Excel VBA script that prompts a user for a range of cells containing keywords to be used later:

Dim keywordRange As Range
Set keywordRange = Application.InputBox("Select a range with your keywords.", "KEYWORDS", Type:=8)

This code works fine. A pop-up box appears with a blank text field. Click on a cell and the text field is filled with a reference to that range, for example, "$A$1".

I want the option to type text into that blank text field instead of selecting a range. For example, the input box would return a range of "$A$1" if the cell was clicked or a value of "My Key Word" if the user typed it.

Currently, entering text other than a valid range reference generates this error:

The reference you typed is not valid, or you did not provide a reference where one was required. To enter a reference using the mouse, click in the edit box, and then click the cell in the sheet you want to refer to, or drag through the range you want to select.

I tried changing the the 'keywordRange' variable type to String. That generated a compile error.

Any ideas where to start? Thanks!

Upvotes: 1

Views: 4227

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149297

What I would recommend is create a Userform and place a RefEdit control as shown in the image below

enter image description here

And then use something like this

Private Sub CommandButton1_Click()
    Dim rng As Range

    On Error Resume Next
    Set rng = Range(RefEdit1.Value)
    On Error GoTo 0

    If Not rng Is Nothing Then
        MsgBox "You selected the range " & RefEdit1.Value
    Else
        MsgBox "You typed " & RefEdit1.Value
    End If
End Sub

Now you can type text or select a range as shown below.

enter image description here

Upvotes: 2

Michael Russo
Michael Russo

Reputation: 442

Change the type from 8 to 10

From Microsoft Docs:

The following table lists the values that can be passed in the Type argument. Can be one or a sum of the values. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

Upvotes: 1

Related Questions