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

 

[Scott Huish]

If you want to do it upon clicking on a cell then you want to use the Worksheet SelectionChange event.

You can then return all values you want from this event code.

Where do you want it to put the data?

 

[pklocke]

Hi HotPepper:

That makes sense. One caveat tho is that if I click on a cell that is not in the "table range" then I don't want anything to happen. For instance if I click on one of my cells in upper left, that hold static data, no action should occur. I only want the action to occur if I click on the one of the table cells (next to the gantt chart).

Now, once I do that, then I would be placing the returned data in the cells next to the headers near my arrowed comments in the upper part of the sheet (like H3, H4, H5, etc, etc)

Make sense?

Thx for your reply!

pat

 

[T_Holliday]

You use the Intersect method to see if the changed cell is within the range of what you want triggering this macro. Use the Intersect method with the Target range variable passed from the SelectionChange event...

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
    If Not (Intersect(Target, Activesheet.[B:B]) Is Nothing) Then
        'Execute code
    End If
End Sub

You may need to edit that to suit your purpose, but this is set up so that every time you click on a cell, it checks to see if the cell you selected is on column "B" before executing the code. It could generate an error, though, if a user selects more than once cell at a time, in which case you'll have to take another step:

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
Dim SelectedCell as Range
    For Each SelectedCell in Target.Cells
        If Not (Intersect(SelectedCell, Activesheet.[B:B]) Is Nothing) Then
            'Execute code
        End If
    Next
End Sub

So it will execute the macro once for each cell in the multi-cell selection.

 

[pklocke]

Thanks A Bunch!...I shall give 'er a go and see how it works, and let you know...hope to spend some time on it this p.m.!

P

 

[pklocke]

Ok...I think I have got it working from the standpoint of installed, executing, and in my case, returning the selected current cell location to a specific cell on the spreadsheet (ie, returns $C$20 to cell G2).

But, what I "REALLY" want to do with this now is to take the contents of the cell referenced by G2, namely '$C$20', and do a vlookup, returning some value (in my original post, image capture, you can see the blue "box" with headings. I want to populate the content in the adjacent rows via a vlookup).

Can I use either that subroutine, or the cell referenced content in cell G2 (ie, $C$2), in a normal vlookup on the spreadsheet somehow? Somehow I have to do an indirect referernce back to the contents, ie:

=vlookup(contents of cell pointed to by G2<CONTENTS G2 cell by to pointed>,some range <SOME range>,some offset <SOME offset>, false)

is that do-able?
thx (again!)

 

[pklocke]

Nevermind! I stumbled upon the Indirect function! WOOHOO.

Thanks for getting me going the right direction!!!

Pklocke

 

[pklocke]

One other oddity, maybe you have suggestion.

As I have been working with the spreadsheet, (aside from the coding we did for above, like formatting, adding columns etc), if I happen to select either the entire column that we are focusing on, above, odd things happened, and I assume it is because the event is triggered, but for more than just a single cell, so the thing goes a bit nuts. Is it possible to count the number of cells just selected, within the event code, and if more than 1, exit?
Here is the code as is today, and that set of cells (c18:C217) are those that I want to trigger additional action (GetCursor)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SelectedCell As Range
Debug.Print "Made it herex"
For Each SelectedCell In Target.Cells
If Not (Intersect(SelectedCell, ActiveSheet.[c18:c217]) Is Nothing) Then
Range("b2").Value = GetCursor
End If
Next
End Sub

so, thinking another IF statement after then then that says something like "If range selected count=1, then do the line for getcursor, else, do nothing."

 

[Saagar]

These statements return the size of the range.
SelectedCells.Rows.Count
SelectedCells.Columns.Count

----
your code may be:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SelectedCell As Range
Debug.Print "Made it herex"

if SelectedCell.rows.count=1 and SelectedCell.columns.count=1 then

If Not (Intersect(SelectedCell, ActiveSheet.[c18:c217]) Is Nothing) Then
Range("b2").Value = GetCursor
End If

endif

End Sub

 

[pklocke]

That make sense. I made the changes, and my subroutine looks like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim SelectedCell As Range
Debug.Print "Made it herex"
If SelectedCell.Rows.Count = 1 And SelectedCell.Columns.Count = 1 Then '<--This statement is generating error >
If Not (Intersect(SelectedCell, ActiveSheet.[c18:c217]) Is Nothing) Then
Debug.Print "Worksheet_SelectionChange: " & GetCursor
Range("b2").Value = GetCursor
End If
End If
End Sub


However, I get an error:
"Run-time error '91':
Object Variable or With block variable not set"

on statement:
If SelectedCell.Rows.Count = 1 And SelectedCell.Columns.Count = 1 Then


I know this is going to be one of those really obvious things (like setting an assignment or something), but I cannot see it. Idea (and thanks....it is logical what you are suggesting).

Pat