Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Mass Naming of Controls

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Looks like I'll have to resort to the brute force method...

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Greenwood, SC
    Posts
    677
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •