2

I maintain a large spreadsheet and there are ActiveX command buttons throughout the code. One sheet has almost 100 of these. I made sure the button is visible as some of the buttons are hidden/unhidden depending on the flow of the code.

Is there a way I can find where on the sheet the button is located that the VBA code is pointing to? Here is a snippet:

Private Sub CommandButton4_Click()
    Application.Goto Range("Add_Trainees"), True
    CommandButton3.Visible = True ' unhides menu button
    CommandButton81.Visible = True ' hides SC1
    CommandButton97.Visible = True ' hides SC2
End Sub

I am trying to find where on the sheet command buttons 3, 81 and 97 are located.

Community
  • 1
  • 1
busdriver12
  • 31
  • 1
  • 2
  • 5
  • Does this work for Buttons too? [Hidden and Invisible Objects](http://blogs.office.com/2009/01/30/hidden-and-invisible-objects/). – mins Apr 07 '15 at 05:00
  • Have a look at this http://stackoverflow.com/questions/6242605/excel-vba-getting-row-of-clicked-button – Kannan Suresh Apr 07 '15 at 05:14
  • 1
    Over 100 buttons? Why? I'd suggest to re-think your UI design... – Maciej Los Apr 07 '15 at 06:14
  • Thanks for the replies folks. I am having a few issues trying to access all the answers I've received so far apart from Ray Chen's. Thanks pnuts, that's showing me what I was looking for. Also, thanks to mins for the MS blog link - that confirmed pnuts' info. Also Maciej Los I am cleaning up VBA written by a former employee of my company and have spent a lot of time cleaning up a lot of excessive and redundant code. Once the basic functionality issues have been sorted I intend to cull a lot of the excessive use of buttons. I am in the process of tracing the flow of the code. Thanks all – busdriver12 Apr 07 '15 at 11:05

1 Answers1

0

I think pnuts provides the correct way to find out where the buttons are.

I would also suggest to open the panel from "Developer" -> "Properties". In that panel, you can check out all the ActiveX Controls, including the command buttons, in the list.

Ray Chen
  • 99
  • 6
  • Thanks @Ray Chen. I am new to this site and still getting the hang of how it works. Developer | Properties did not reveal the buttons but pnuts suggestion did work. Thanks for your help – busdriver12 Apr 07 '15 at 11:13
  • What about an old "forms" button (note, on a worksheet, not a form. Created by View/Toolbars/Forms and dragging). They are not on F4 properties in VBA window. From what I can tell, you just have to visually locate the button on the sheet, and a couple of meager settings are available from right click. And I have found nowhere that the button name is shown. Help! – MicrosoftShouldBeKickedInNuts Jan 14 '18 at 22:43
  • Related I don't see any way to go directly to the non ActiveX "form button" code - you can't double click on the button in design mode the way you do with an ActiveX button. The only way I've found is to visually search VBA Project Explorer for the code module which is identified from right clicking the button. – MicrosoftShouldBeKickedInNuts Jan 14 '18 at 22:44
  • (I do realize that I could navigate activesheet.buttons and activesheets.shapes items and the forms buttons would appear as FormControlType xlButtonControl and significantly Type msoFormControl (as opposed to msoOLEControlObject). I'm looking for a more direct interface to forms buttons than this tedious approach.) – MicrosoftShouldBeKickedInNuts Jan 21 '18 at 04:12