VBA Get Row and Column Index of Cell Clicked to Open UserBox in Excel 2016

Question

I have defined a UserBox which opens on the double click of cells within a specified range. I do this with simple intersection checking in Worksheet_BeforeDoubleClick(). I have a row of header tags in my sheet and I want a label within the opened UserBox to display "Opened with X" where X is the header of the column the cell is in. For example, if cell C9 is double clicked, I want to to display the text of the header located in cell C2. I know how to apply the contents of an arbitrary cell to a label within the UserBox, but I'm not sure how to extract the row and column of the cell that was clicked.

score 2 · Accepted Answer · answered Jun 05 '19 at 15:58

2

Try:

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim Column As Long, Row As Long

    Column = Target.Column
    Row = Target.Row

End Sub

answered Jun 05 '19 at 15:58

Error 1004

7,877
3
23
46

score 0 · Answer 2 · answered Jun 05 '19 at 15:36

0

I figured this out. I can simply call ActiveCell.Row and ActiveCell.Column.

answered Jun 05 '19 at 15:36

taurus

470
7
22

try to avoid `ActiveCell`, `Select` etc because they cause confusion. – Error 1004 Jun 05 '19 at 15:59

VBA Get Row and Column Index of Cell Clicked to Open UserBox in Excel 2016

2 Answers2

Linked