Return contents of a cell in VBA

[pklocke]

Hey...noob here..hope this is pretty easy...think so (just not an excel/vba expert).

I have a table of rows in an excel file. I am attempting to create a function such that, if you click on any row in the table (it is a VERY wide table), that it will return the value (contents) of the first cell in the row.

Example:

A B C
001 Frank Smith
002 Mary Jones
003 John Do
etc...

Now, were I to click on the name Mary (cell B2), I would like the function to return the value 002 from A2 (I will then use that in a vlookup that will present just a subset of the wide row in a specific location in the table.

What I have so far:

in VBA:

Function GetCursor() As String
'--------------------------------------------------------
' Get's current Cursor Location and returns to excel
'--------------------------------------------------------
GetCursor = ActiveCell.Address
End Function

---------------------------------macro subroutine ----------------
Sub Update_Click()
Range("H3").Value = GetCursor
End Sub

The above does indeed return the "cell reference" (ie $B$2) to cell H3, were I to click on Mary's name (I have a button on the excel page that executes Update_Click)

So I just need to change the $2 to $1, I think. I believe I can figure that out. But the part that's stumping me is to then use the $B$1 in a vlookup.

Here is an image to give you and idea what I am attempting to do:

Thx for any guidance (and I hope it makes some sense)

P

 

[Saagar]

I goofed up. I will get back to you later in the evening.

 

[Saagar]

Here is the answer:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Rows.Count = 1 And Target.Columns.Count = 1 Then
If Not (Intersect(Target.Cells, ActiveSheet.[c18:c217]) Is Nothing) Then
Range("b2").Value = GetCursor
End If
End If

End Sub

 

[pklocke]

That seems to have done it. You guys rock!

So, what is difference between SelectedCell and Target.Cells (or .Rows)?

Maybe that's more rhetorical...as it works...but trying to learn...

thx, again!

P

 

[Saagar]

Since you are interested in learning, here is what I know.

You will notice "Target" in the opening block:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target is an in-built excel object. The reference to the range you select on the worksheet is stored in Target for use in the macro.

Target.Rows.Count is the count of number of rows in the selected range
Target.Columns.Count is the count of number of columns in the selected range

Target.Cells refers to all the cells in the range you selected.

In the previous code, SelectedCell was a user defined variable for a range object. That is what "Dim SelectedCell as Range" means.

"For Each SelectedCell in Target.Cells" means for every cell in the range you selected, execute the code between the "For... and Next" block.

Hope this helps.

 

[pklocke]

Thank you VERY much for taking the time to explain. THou doth rocketh!