5

I have an Excel document that I need to modify. This thing was created by a "guru" and has been locked down from almost any form of modification.

I have the master-password to access the VBA modules and, I assume, that might be all that I need to make any changes to this document worksheets.

The first worksheet of this Excel file is a switch board. It has a series of buttons that launch various VBA forms that are used for data input. I need to modify the properties associated with one of these buttons, but I can't figure out how to view the Properties dialog. How can I display it?

FYI, I have attempted to right-clicked this button many times, but I can't get a context menu to show up. What can I do to modify this button?

12
  • what type of button is it? A form button or an ActiveX? Have you got your Developer Tab opened? – user222864 Aug 28 '13 at 9:06
  • @mehow: I'm not sure and no, I don't have a developer tab open. All I can say is that these buttons are on my first worksheet, they are not on a VBA form, and they located within cells but appear to "float" over worksheet. How do I open a developer tab/toolbar? – RLH Aug 28 '13 at 10:22
  • 1
    Aha! I see now. Thank you. Feel free to post an answer. After cutting on the Developer tab, I see that all I have to do is put the Worksheet in "Design Mode", much like Access and queries. – RLH Aug 28 '13 at 12:03
  • 2
    It doesn't work for me :-(. I have inserted a button control, enabled the Developer tab, turned on Design mode, and when I right click the button nothing different happens - the same options are shown as with Design mode off: Cut/Copy/Edit Text/Grouping/Order/Assign Macro/Format Control. When I click on "Properties" next to Design mode, it shows the Worksheet properties, not Button properties, even though the button is selected with resize handles visible. Aargh! – Dave Dec 28 '15 at 17:06
  • 1
    @Dave, see this ozgrid.com/forum/showthread.php?t=21848. There are two types of buttons, and the one with properties is the ActiveX control button, not the toolbox one. – Carlos A. Ibarra Mar 18 '17 at 14:35
5

Turn on the Developer Tab in Excel

Go into Design Mode

enter image description here

Right-click any control and View Properties

2
  • 2
    It does not work in Office 2013: no "properties" menu item in popup menu, and "properties" button in the bar only shows sheet properties. – jumpjack Feb 2 '17 at 17:22
  • 1
    See this: ozgrid.com/forum/showthread.php?t=21848. There are two types of buttons, and the one with properties is the ActiveX control button, not the toolbox one. – Carlos A. Ibarra Mar 18 '17 at 14:36
3

Then you have the joker who didn't use controls, but used Textboxes instead. And not just Textboxes , but grouped Textboxes . In this case, you can get into Design Mode as explained above and this will give the option Assign Macro when you right-click the Textbox. Choosing that opens a dialog which allows you to make a New macro or Record it. Of course, you will wonder how it is working if there is no macro already assigned to that Shape. Yes, Shape not Textbox, because Excel doesn't like to make the distinction too clear. Then you will mess around for awhile and try to recreate the original object without the macro defined so it will quit running the macro every time you try to click the nested Textbox. You will need to click around on the nested Textboxes until you get the one you're after to actually show as highlighted (Hint: Try right-clicking the text itself). You may even search around and learn how to ungroup the Textboxes (highlight the Textbox, look at the ribbon where it says Drawing Tools, then click the Format tab there). Once the correct object is highlighted, the context menu should show a Macro already assigned to it and offer an Edit option. Now you will be able to begin to debug or modify the spaghetti code VBA doubtless left behind by the 'guru'. You may even search the definition of 'guru': a spiritual teacher, especially one who imparts initiation. Consider yourself 'initiated'.

3
  • wow. Seems like you worked with that person too. :) – mcw Oct 1 '15 at 15:04
  • @mcs0933 - I must have been in a foul mood. – B H Oct 13 '15 at 3:05
  • This seems and off topic side rant and not an attempt to solve the original question which has been answered by user222864. This may should have been posted as a comment. – bvaughn Dec 7 '16 at 18:15
0

I found the same problem while writing my own macro. I had to replace the "button" with an Active-X "command button" (same position but on the Active-X array of controls) in order to be able to access a properties dialog for the button.

1
  • Just so you know, if you use ActiveX controls, you risk compatibility with Mac hardware, since their support of it is limited. Also, the RowSource property in comboBoxes is not supported in Mac's as well (workaround: use .addItem() instead). – Bill L. Oct 4 '17 at 23:15
0

It's a bit of an old post, but I'm sure more people will have the same question.

  1. Open the Developer tab in the ribbon.

  2. Press Design Mode.

  3. Right-click your button near the edge (otherwise it opens with Move here, Copy here, Cancel..).

  4. Select Format Control....

  5. A dialog opens with 7 tabs that allows you to edit various properties.

1
  • This is essentially the accepted answer. – fixer1234 Jul 6 '18 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.