Excel Form Control Button Quirks

If you’re building an Excel workbook for other people to use, you can add form control buttons, so it’s easy for them to run macros. One of my sample files has Navigation Buttons on each sheet, so you can quickly go to the previous or next sheet. There are a few form control button quirks though – maybe you’ve run into some of them. Today, we’ll take a look at one of the button name quirks – a hidden name that Excel fiercely protects.

Form Control Button Default Name

When you add a form control button to a worksheet, it gets a default numbered name, such as “Button 1”. That name is also shown as its caption.

When the button is selected, you can see its name in the Name Box, at the left of the Formula Bar.

formcontrolsbuttons03

Rename a Form Control Button

You can easily change the name of a button, by typing a different name in the name box, and then pressing the Enter key. For example, change Button 1 to “NewName”.

No problem!

formcontrolsbuttons04

Restore the Default Name

However, it’s not always so easy. Thanks to Dave Unger, who alerted me to a quirk with form button names. He described the problem on the StackOverflow forum.

The problem occurs if you decide to change that button back to its original name, “Button 1”.

  • Type “Button 1” in the Name Box, and press Enter.
  • Nothing happens.

The button still shows “NewName” as its name. In the screen shot below,

  • Top half shows the original name that I typed, when trying to restore that name.
  • Bottom half shows that the name wasn’t accepted, and stayed as “NewName”

formcontrolsbuttons05

Restore Default Name With a Macro

If you know the original name of a button, you can use a macro to restore that name. The following macro restores the original name to “Button 1”, which was previously renamed as “NewName”.

Sub RenameButton02()
ActiveSheet.Buttons("NewName").Name = "Button 1"
End Sub

Duplicate Button Names

In another quirk (or maybe it’s a feature), you can give multiple buttons the same name. I used a macro to name all three of these buttons as “Button 10”

formcontrolsbuttons08

But, when I ran a macro to list each button, with its name, top left cell address and caption, it gave the details for the first button, 3 times.

That could cause problems if you’re referring to buttons by name, in any of your macros.

formcontrolsbuttons09

Internal Button Names

But what if you don’t remember the original name for a button? And how do you know which default “internal” names are currently reserved by Excel?

If you use a macro to list all the buttons, and their names, you’ll only see the current, visible names. There’s no property that reveals the internal name.

The only way that I’ve found to get those names is to loop through a list of numbers, and see which buttons have the numbered names that match. You can get that macro on the Form Control Buttons page of my Contextures website.

Duplicate Name Confusion

The List All Buttons macro works pretty well, if none of the buttons were given names that conflict with another button’s default name.

In that situation, the list might show incorrect results for some buttons. In the list below, the code repeated the information for Button 1 (display name Button 3), instead of showing the correct details for default Button 3.

formcontrolsbuttons09

To help with that situation, you can turn on the optional line that renames each button to its numbered name. That might not work 100% of the time, but it could help!

Here’s the same list, with the optional renaming line turned on.

formcontrolsbuttons10

Get the Form Control Button Quirks Sample File

To experiment with the form buttons and their naming quirks, go to the Form Control Buttons page of my Contextures website, and download the sample file.

And please let me know if you discover any other Form Control Button quirks!

____________________

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.