Mass Naming of Controls

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
I have two worksheets with 50 buttons and 50 checkboxes each and would like to name them en mass. I've tried looping through the Controls and CommandButtons collections, but cannot get it to work. I seem to remember being able to do this in VB but having problems in VBA.

Any suggestions?
This message was edited by kkknie on 2002-05-02 10:25
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi kkknie,

Here is a macro that sets the names of all the button and textbox ActiveX controls on the active worksheet.

Sub NameControls()
Dim Ctl As OLEObject
Dim NoCB As Integer
Dim NoTB As Integer
NoCB = 0: NoTB = 0
For Each Ctl In ActiveSheet.OLEObjects
Select Case Ctl.ProgId
Case "Forms.CommandButton.1"
NoCB = NoCB + 1
Ctl.Name = "MyButton" & NoCB
Case "Forms.TextBox.1"
NoTB = NoTB + 1
Ctl.Name = "MyTextBox" & NoTB
End Select
Next Ctl
End Sub

Enjoy.
 
Upvote 0
Thanks for the help. I tried the code you posted, but it didn't work. After recording the adding of a checkbox, I found that the shapes collection is what I needed.

ActiveSheet.Shapes("Check Box 1").Name = "NewName1"

Looking though the help files suggests that your method should also work. Sometimes Microsoft hacks me off...
 
Upvote 0
Hi kknie,

The problem is because you are apparently using Forms controls (built-in to Excel and available from the Forms toolbar) rather than ActiveX controls (available to all MS Office applications and available from the Controls toolbar). These are two totally different types of controls. The Forms controls are in the Shapes object collection as you mentioned, while the ActiveX controls are in the OLEObjects collection. The reason I though you were using ActiveX controls is that you mentioned a CommandButton. The Forms controls set does not include a "CommandButton", but only a "Button". It is quite easy to write code to name all the Forms controls, just as my code named all the ActiveX controls. Here is an example similar to the one I provided for ActiveX controls. This names all the buttons and checkboxes on the active worksheet.

Sub NameFormcontrols()
Dim Ctl As Shape
Dim NoBtn As Integer
Dim NoCbx As Integer
NoCbx = 0: NoBtn = 0
For Each Ctl In ActiveSheet.Shapes
If Ctl.Type = msoFormControl Then
Select Case Ctl.FormControlType
Case xlCheckBox
NoCbx = NoCbx + 1
Ctl.Name = "MyCheckBox" & NoCbx
Case xlButtonControl
NoBtn = NoBtn + 1
Ctl.Name = "MyBtn" & NoBtn
End Select
End If
Next Ctl
End Sub
 
Upvote 0
Aha!

This explains quite a bit. I'm used to writing applications in VB6.0 and on Forms, so I naturally assumed that a checkbox was a checkbox. Life should now become a bit simpler...

Much thanks!

K
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top