Determine which form control button has been pressed by its Text RRS feed

  • Question

  • I am trying to identify which form control button has been pressed.  There are up to 400 buttons on the sheet each used for Navigation around the sheet.  I used the Form control button because I did want 400 "xyz_Click() " procedures.  Since many of the buttons are repetitive ie they Select the same cell when pressed I named sets of the nav buttons the same;there are up to 10 rows of buttons "TOP" "PR1" "PR2" "PR3"....up to..."PR10" for the Perth results.  If I press PR3 I want to Select the first cell the Results subsection 7 for Perth.  The other main sections are M Melb S SYdney B Brisbane and A for Adelaide.   so BR7 should take me to the start of the data for Results subsection 7 for Brisbane. The other Cities are M Melbourne, S SYdney and A Adelaide.  

    I named the Buttons "PR1" etc thinking if I can identify the button name I can easily to goto the relevant cell.  However the only relevant properties I can return (using Application.Caller) when A button is pressed are: the Row, Column, TopLeftCell, BottomRightCell, Index, ID and Name.  Index tells me the button number form 1 to 400.  Name and ID return Long Integers which vary.  Unfortunately there is no property which returns the string name on the button I had planned to use. 

    Does anybody know how the get the NAME I placed on each button using Characters.Text?

    If not the Index property is my best bet if I can work an algorithm to identify each button from its Index (however, my
    maths is not good). Also,  the number of subsections vary from day to day (up to 10 but usually 8) and the number of
    data items in each set of results vary from day to day (up to 20 varying from 5 to 20).

    Another strategy I can use is to place the Nav buttons within a cell in a specific column.  if all the BR1 to BR 10 buttons are placed in the last 10 columns ie., Columns T to AC then it will be relatively easy to identify which button by the column and row returned. However there is no way I can accurately place a button (via VBA) so it is placed and stays within a cell  I use the Buttons.Add (Top, Left, Width, Height) to add the buttons.   There are some inherent issues with this in that those columns may not be visble on the screen since they are the last 10 columns and the user may as well just scroll up and down rather than resize and/or move the screen to view these buttons.  The default Object Positioning Property is "Move and Size with cells" which could overcome some of the issues.

    Another strategy might be to use hperlinks but that is not something I'm familiar with or understand but willing to learn.

    If only I could identify the text on each button I placed using Characters.Text my problems is solved.



    Saturday, March 24, 2012 2:52 AM

All replies

  • In the Event procedure you can use following

    Sub TestMacro()

    MsgBox ActiveSheet.Shapes(Application.Caller) _

    End Sub

    I have assigned the macro to Form Button Control and when I click the button, it will return the caption.

    The Application.Caller returns the name of the Control on sheet. And the name you given to controls are actually accessible from Shape object if the control is embedded in sheet.

    Saturday, March 24, 2012 8:55 AM