Home > Macros > Basics > Form Controls Excel Form Control Buttons Use these instructions and sample macros to work with the form control buttons on a worksheet.

Introduction

You can add form control buttons on a worksheet, so it is easy for people to run macros. For example, add Navigation Buttons on a Worksheet, to make it easy to move through a workbook.

The macro examples on this page are for worksheet administrators. These macros will help you manage the form control buttons, behind the scenes. For example, the change a button's name, or create a list of all the buttons on the active sheet.

Button Default Name

When a form button is added to an Excel worksheet, it is assigned a default numbered name, such as "Button 1". That name is also used as the new button's caption.

To see the default name:

• Click on the new button to select it (or Ctrl+Click, if a macro has been assigned to the button).
• Then, look in the Name Box, at the left of the Formula Bar.

NOTE: Excel remembers this default name (or "internal name"), until the button is deleted.

Copy and Paste Buttons

When a form button is copied and pasted on an Excel worksheet, the copy has the same caption as the original button. It also gets a hidden "internal" name, and a visible name.

The visible name depends on whether or not original button has ever been renamed -- even if its original name has been restored:

• If the original button HAS NOT been renamed at any point, the copy gets a visible name that matches its hidden "internal" name. In the screen shot below, the copy is named Button 6.



• If the original button HAS been renamed at any point, the copy temporarily shows a visible name that is the same as the original button's visible name. In the screen shot below, the copy is shows its name as Button 1.

Visible Name Correction

Eventually, the visible name for the copied button is corrected, and shows its default numbered name. You can see the correction in the animated screen shot below.

When I first pasted the button, it showed the name of Button 2. I ran the List All Buttons macro (below), and it only listed two buttons, even though the sheet had 3 buttons, because two of the buttons had the same name.

I can't determine a pattern for when this automatic name correction occurs. Sometimes it is almost instant, and other times it takes a minute or two, as it when I recorded this animated screen shot.

Be careful though, if you are copying and pasting form buttons with a macro, and immediately referring to the buttons by name.

Rename a New Button

If a form control button is added to a worksheet, you can rename the new button, either manually or with a macro.

To manually rename a new button:

• Click on the new button to select it (or Ctrl+Click, if a macro has been assigned to the button).
• Click in the Name Box, at the left of the Formula Bar.
• Type a new name, to replace the existing butto name
• Press Enter, to complete the name change

Use a Macro to Change a Button Name

To change a button name with a macro, use one of the following examples.

The first macro below identifies a button by its index number, and renames it.

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

The next macro identifies a button by its name, and renames it.

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

Restore Original Button Name

After a form button has been renamed, you cannot simply change back to its original name, by typing the original name in the Name Box. Thanks to Dave Unger, who alerted me to this quirk in naming form buttons. He described the problem in this question on the StackOverflow forum.

Try to Manually Restore the Default Button Name

In the screen shot below, Button 1 was previously renamed as "NewName".

To attempt a manual change back to the original name:

• Select the button, and type "Button 1" in the Name Box
• Press Enter, to complete the name change
• The name is not accepted, and the button keeps its revised name of "NewName"

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 has been renamed as "NewName".

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

Internal Button Name

Even though Excel remembers the original "internal" names for all the form buttons that are on a worksheet, there is no easy way to determine what those internal button names are.

• Manually, you can see the current name for any button, in the Name Box.
• If you use a macro to show a button's name, it only shows the current, visible name. There is no property that reveals the hidden default name.

However, if you do know the button's original "internal" name, you can either either name to refer to a button in a macro:

• use the button's internal "hidden" name or
• use the button's current visible name.

NOTE: You cannot change the hidden internal name for a form button -- there is no property that allows you to view or edit that name.

List All Buttons

One way to get a list of buttons, with their hidden "interal" names, and other details, is to loop through a set of numbers. The macro below loops from 1 to 100000, and looks for buttons with a name that ends with the current number.

• Once a button has been identified in the loop, such as Button 1, that name is used to get the remaining details for the button, such as its ID number and location.
• The macro exits the loop when all the buttons have been listed.

The macro creates a new worksheet, with a list of all the buttons on the active worksheet.

How to Use This Macro

Copy this code, and add it to a regular module in your workbook. Save your file as Macro-Enabled (*.xlsm) or Binary (*.xlsb). Then, activate a worksheet that has form control buttons, and run the macro.

Sub ListAllButtons()
Dim wsList As Worksheet
Dim wsA As Worksheet
Dim btn As Button
Dim sh As Shape
Dim BtnList As ListObject
Dim lRow As Long
Dim lBtns As Long
Dim lNum As Long
Dim lMax As Long
Dim lCount As Long
Dim LastCol As Long
Dim strBtn As String
Set wsA = ActiveSheet
Set wsList = Sheets.Add
lRow = 1
LastCol = 7
lMax = 100000
lBtns = wsA.Buttons.Count
On Error Resume Next

With wsList
  .Range(.Cells(lRow, 1), _
      .Cells(lRow, LastCol)).Value _
    = Array("Internal Name", "Display Name", _
        "Index", "ID", "Row", "Col", "Caption")
  lRow = lRow + 1

  For lNum = 1 To lMax
    Set btn = Nothing
    lCount = lCount + 1
    If lCount > lBtns Then Exit For
    
    strBtn = "Button " & lNum
    Set btn = wsA.Buttons(strBtn)
    
  'OPTIONAL - make display name
    '  same as numbered default name
    '  this might help if multiple buttons have
    '  the same display names
    'btn.Name = strBtn 
    
    If Not btn Is Nothing Then
      Set sh = wsA.Shapes(btn.Name)
      .Range(.Cells(lRow, 1), _
        .Cells(lRow, LastCol)).Value _
        = Array(strBtn, sh.Name, btn.Index, _
            sh.ID, sh.TopLeftCell.Row, _
            sh.TopLeftCell.Column, btn.Caption)
      lRow = lRow + 1
    End If
  Next lNum

  Set BtnList = .ListObjects.Add(xlSrcRange, .Range("A1").CurrentRegion, , xlYes)
  BtnList.TableStyle = "TableStyleLight8"
  BtnList.HeaderRowRange.Columns.AutoFit
  BtnList.DataBodyRange.Columns(1).AutoFit

End With

End Sub

Download the Sample File

You can download a copy of the Excel Form Control Button Macros file to see the worksheet buttons and the code for the macros. The file is in xlsm format, and is zipped. After you unzip the file and open it, enable macros, so you can use the navigation macros.

More Tutorials

Run Macros with Worksheet Buttons

Excel Worksheet Buttons Cause Problem

Get Started with Excel Macros

Text Box Macros

FAQs, Excel VBA, Excel Macros  

Add Code to Excel Workbook