CODEWITHSUNDEEP
vbaexcel
Tim
Tim

Reputation: 111

Get the column of a user selected cell using vba excel

Refer to this question: Let the user click on the cells as their input for an Excel InputBox using VBA. I use this line of code Set rng = Application.InputBox(Prompt:="Select the cell you want to edit.", Title:="CELL TO EDIT", Type:=8) to ask the user to select a cell. If the cell that is selected is in column G then I will need to call a subroutine. So, I need to figure out what column the selected cell is in.

Thanks for the help!

Upvotes: 1

Views: 7451

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

Use rng.Column property to return column number:

Sub test()
    Dim rng As Range
    On Error Resume Next
    Set rng = Application.InputBox(Prompt:="Select the cell you want to edit.", title:="CELL TO EDIT", Type:=8)
    On Error GoTo 0

    If rng Is Nothing Then Exit Sub

    'column G=7
    If rng.Column = 7 Then
       'do something
    End If
End Sub

If user can select more than one cell, change

If rng.Column = 7 Then

to

If Not Application.Intersect(rng, Sheets("Sheet1").Range("G:G")) Is Nothing Then

Upvotes: 1

Related Questions