CODEWITHSUNDEEP
excelvba
LeeM
LeeM

Reputation: 31

How to reference a cell using an input box?

I have a message box to insert a user-selected cell range. I want that cell to be the new active cell. What is the VBA code to do that?

This is the VBA I have, but it doesn't change the active cell.

Sub AddClient()

    'Message Box
    Dim userResponce As Range
    On Error Resume Next

    Set userResponce = Application.InputBox("Place curser on Client Name that you want to insert new one above")
    On Error GoTo 0

    If userResponce Is Nothing Then
        MsgBox "Cancel clicked"
    Else
        MsgBox "You selected " & userResponce.Address
    End If

End Sub

Upvotes: 3

Views: 2572

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

If you want to make the cell active then you can use Application.Goto. And if you just want to insert the row then you do not need to make the cell active as @BigBen mentioned. You can directly insert the row without activating the cell or the row.

For example

userResponce.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Also use Application.InputBox with Type:=8 as shown in Application.InputBox method

If you want to activate the cell then use the below.

Sub AddClient()
    Dim userResponce As Range
    Dim prmt As String

    prmt = "Place cursor on Client Name that you want to insert new one above"

    On Error Resume Next
    Set userResponce = Application.InputBox(prompt:=prmt, Type:=8)
    On Error GoTo 0

    If userResponce Is Nothing Then
        MsgBox "Cancel clicked"
    Else
        Application.Goto Reference:=userResponce
    End If
End Sub

Upvotes: 4

Related Questions