-1

I've just started a new contract and am working with Office VBA again after about 20 years. There is a spreadsheet I'm working on where command buttons disappear after the document saves. I've been reading other threads that suggest as a fix setting the size of the button after saving. However, I can't figure out how to identify that button in code.

When I click "Format", there is no place where the name of the object appears. There's a macro attached to it, but I see no way to identify the "sender" when the macro fires.

The macro pops up a form, and after the user enters some information and submits, it saves the sheet, and the button goes "poof".

Is there any way to get the name of the button in the macro so I can resize it?

Thanks!

Brian Link
  • 11
  • 1

1 Answers1

0

If you right-click the button, its name appears in the upper left hand corner of the spreadsheet in the Developer ribbon, right below the "Visual Basic" and "Macros" menu items. In my case, it was "Button 1".

Accessing it was non-intuitive for a guy used to working with full-blown .NET apps, but the button is a "Shape". So, addressing the button is accomplished with the identifier ActiveSheet.Shapes("Button 1"). With this object, you can access its properties and methods.

Brian Link
  • 11
  • 1