Use these instructions and sample macros to work with the form control buttons on a worksheet.
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.
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:
NOTE: Excel remembers this default name (or "internal name"), until the button is deleted.
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:
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.
If a form control button is added to a worksheet, you can rename the new button, either manually or with a macro.
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
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.
In the screen shot below, Button 1 was previously renamed as "NewName".
To attempt a manual change back to the original name:
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
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.
However, if you do know the button's original "internal" name, you can either either name to refer to a button in a macro:
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.
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.
The macro creates a new worksheet, with a list of all the buttons on the active worksheet.
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
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.
Last updated: October 28, 2018 3:59 PM
Contextures RSS Feed