Get the column of a user selected cell using vba excel

Question

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!

use If rng.Column = 7 Then where 7 corresponds to G. Btw, don't forget to add error hadler for case when user press CANCEL and nothing selected — Dmitry Pavliv
That's just rng.column. (Column G = 7.) Is that your question? Oh, simoco. :) — tbur
It worked great. I have the error handler already but thanks for the tip. I used If rng Is Nothing Then Exit Sub. Put it as the answer and I will accept it. — Tim

Dmitry Pavliv Dmitry Pavliv · Accepted Answer · 2014-03-07T21:09:51

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

Get the column of a user selected cell using vba excel

1 Answers