form button on spreadsheet : identifying which row the button is on

[Andyatwork]

Hello hivemind,

This is a hypothetical problem. I am imagining a spreadsheet with the equivalent of an info button in each cell of column A. The idea is the user clicks the button to open a User Form containing additional info about the entitity represented by values on that row.
e.g., Row 1 is labels, row 2 is all about Bob, 3 is about Jane, 4 is about Freddy. If the user clicks the info button on row 3 then a user form opens up and displays additonal info about Jane.

I am wondering if there is a way for VB to identify which row the clicked button was on without having to write individual code for each button telling VB which row the button was on.

I am hoping there is some form button property I can reference or interrogate which will give me a cell address; a button.position property or similar. This will obviously save me a lot of coding if VB can dynamically determine which row is being interrogated as there will be a couple of hundred of these to start with, potentially thousands. (I realise Access is a better solution for database interrogation, but I have to work with excel at present).

I've tried google and drawn a blank.

Ultimately, the user form that the button calls up will have a list box on it and I want the list box to default to the entity on that row, and allow the user to browse the other entities if they want to.

If there isn't a solution for this I will just have a single info button for the users to click that opens the user form and then they can select the entity from a list box. But I would like to offer the "intelligent display" option but don't relish coding the equivalent of several hundred "You clicked button four hundred and seventy six, default to info card for bert".

Many thanks

 

[Andrew Poulsom]

Does this work for you (when assigned to a CommandButton from Form Controls)?

Sub Test()
    MsgBox ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row
End Sub

 

[Norie]

You could use the TopLeftCell property of the button.

Mind you are you sure having hundreds of buttons is a good idea?

How about an alternative like the worksheet's BeforeDoubleClick event?

 

[ozbod]

Andy,

I am not sure specifically what you are getting the Button to do, but you could achieve something similar with a double click on a Row in column A

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

    If Target.Column = 1 Then ' a row in Column A double clicked
        
        sPerson = Cells(Target.Row, 3)
        
        'Then call or update the userform and set the listbox to = "Jane" or whatever else you would get the button to do
        
    End If


        
 End Sub

 

[Andyatwork]

Thank you for the speedy responses everyone, I think I will try the double click solution as that will give me a cleaner looking spreadsheet, Norie is right, having several hundred tiny little buttons was a massive eyesore. Although i'm sure that topleftcell propery will come in useful somewhere.

Thanks again all.