Need to find and rename checkboxes by VBA

[klarowe]

I have around 24 reports that I made up and each one has on average around a thousand checkboxes for dispositions (see picture). My boss today decided to ask me to delete one of the checkboxes for each part disposition line (use as is) and change another to read "Clean - Reuse". If I were to go through and change each and every one manually it will take me forever.

Please tell me there is a way to run a code in each report that will find all of the checkboxes with text "Use as is" and delete them; then find all of the checkboxes with text "Clean" and change them to "Clean - Reuse". All checkboxes are form control (not activeX).
Thanks in advance!!!

Example of a sheet (anywhere from 20-50 sheets per report):

 

[klarowe]

took it a step further and added a loop to go through all open workbooks. That way i can just run : Application.Run ("'Test CDR.xls'!changecheckboxes") and it will go through any workbooks I have open so I can do multiples at once.
For future use, here is the new code:

Sub changecheckboxes()
    Dim sh As Shape
    Dim wkb As Workbook
    Dim ws As Worksheet
    For Each wkb In Workbooks
        For Each ws In ActiveWorkbook.Sheets
            For Each sh In ws.Shapes
                With sh
                    If .Type = msoFormControl Then
                        If .FormControlType = xlCheckBox Then
                            If sh.TextFrame.Characters.Text = "Use As-Is" Then
                                sh.Delete
                            ElseIf sh.TextFrame.Characters.Text = "Clean" Then
                                sh.TextFrame.Characters.Text = "Clean - Reuse"
                                sh.Width = 2 * sh.Width
                            End If
                        End If
                    End If
                End With
            Next sh
        Next ws
    Next wkb
End Sub

One thing I noticed is that in the original code I didn't have "Dim ws as worksheet" and it still worked. Is that just a VBA given that doesn't have to be written?

 

[Gary McMaster]

Don't think I have seen a code with so many if statements within an if statement

You could also visit just the check boxes instead of every shape on the sheet(s). It would be faster especially if there were a lot of other shapes that were not check boxes. There is obviously something else, that's what caused your textframe error. You could also get rid of the test you just added to see if it's a control since it is predefined as a collection of nothing else.

Dim oCB As CheckBox

For Each oCB In ActiveSheet.CheckBoxes
Debug.Print oCB.Name
'Do whatever to each check box
Next oCB

Gary

 

[klarowe]

I'll have to give that a shot. I did try running multiple reports at one time (about 7) and it was really odd. it went very quick, but 3/4 of them didn't have the checkboxes changed. And then some of them had "False" entered in on cells that normally are filled in by an inputbox depending on the status of a toggle button on the hope page... which was turned off (eliminated inputboxes from popping up on sheet activation)

One other thing... somewhat related. I also have a bunch of buttons for navigating through the sheets (previous, index, next). Can the working code be altered for buttons. I would like to resize all the navigation buttons so that they are equal. Even better I'd like to link them to the cell size they are in if thats possible (all columns on all sheets are the same width, but rows in which the buttons are different with each sheet)

 

[Gary McMaster]

I'll have to give that a shot

That was just an FYI for future use. If it ain't broke don't fix it.

One thing I noticed is that in the original code I didn't have "Dim ws as worksheet" and it still worked. Is that just a VBA given that doesn't have to be written?

No. It is because you did not declare "Option Explicit" in the declarations section of your module which forces you to declare variables before you use them. It can make debugging easier. If you mis-type a variable name, for example, it will be flagged as undeclared rather than just being used (as your ws variable was) and allowing you to believe you are working with one variable when it is actually 2 different variables because of a typo.

There is a switch in the VBA IDE "Options", "Require Variable Declaration", ticking that switch causes "Option Explicit" to be automatically added when a module is inserted.

Gary

 

[klarowe]

good to know. I'll keep that in mind for future.

Now how about the button? I tried changing "type = xlcheckbox" to "type = xlbutton" and I didn't get an error, but my quick text change test didn't change anythign either... lol

 

[Gary McMaster]

I would like to resize all the navigation buttons so that they are equal. Even better I'd like to link them to the cell size they are in

You can try the sample below in a brand new workbook (so you don't mess up your project). Sprinkle a few form buttons around the new sheet and see if this will help.

Gary

Public Sub Test()

Dim oButton As Button 'Form buttons

'Or use shapes, visit all shapes and make sure shape type is correct
'similar to previous check boxes code
For Each oButton In ActiveSheet.Buttons

    Debug.Print oButton.Name
    Debug.Print oButton.Caption
    
    'Make buttons same size as cell containing button top left corner
    oButton.Left = oButton.TopLeftCell.Left
    oButton.Top = oButton.TopLeftCell.Top
    oButton.Width = oButton.TopLeftCell.Width
    oButton.Height = oButton.TopLeftCell.Height
    'Do whatever with each button
    
Next oButton


End Sub

 

[klarowe]

I'll give that a shot and report back. I have a test workbook of the reports that i have been using for testing and debugging a code before applying it to any of the real reports. It has saved me multiple times... lol

Edit: one quick question, where would I add in to filter out and apply the changes to only the buttons that have the text "previous", "index", and "next". I'm not sure it if is where you have the debug.print obutton.name / caption... i'm not real familiar with that.

 

[klarowe]

Nvmd on the edit, I figured it out. But what is the debug.print used for? When I ran the macro from the immediate screen, it gave me a bunch of button names and values (true / false). And the macro worked perfectly with both those lines commented out...

 

[Gary McMaster]

The debug.print statements are for exactly that ... debugging and are not needed.

"Select Case" might be useful for the filtering:

Public Sub Test()

Dim oButton As Button 'Form buttons

'Or use shapes, visit all shapes and make sure shape type is correct
'similar to previous check boxes code
For Each oButton In ActiveSheet.Buttons

    'Debug.Print oButton.Name
    'Debug.Print oButton.Caption
    
    Select Case UCase(oButton.Caption)
    
    Case "PREVIOUS", "INDEX", "NEXT"
    
        'Make buttons same size as cell containing button top left corner
        oButton.Left = oButton.TopLeftCell.Left
        oButton.Top = oButton.TopLeftCell.Top
        oButton.Width = oButton.TopLeftCell.Width
        oButton.Height = oButton.TopLeftCell.Height
        'Do whatever with each button
    Case Else
        'Do nothing
    End Select
    
Next oButton


End Sub

 

[klarowe]

I kinda figured that, but I wanted to be sure. I've got it pretty much working perfectly... I'm messing around a bit with the different checkbox code you had (easier to interpret for future). I just need to figure out how to set the checkbox size properties. I need to set it to a number rather than just 2 * cb.width

I actually ended up using an If with or statement for the filters

Sub ResizeButtons()
    Dim oButton As Button
    Dim ws As Worksheet
    Dim wkb As Workbook
    For Each wkb In Workbooks
        For Each ws In wkb.Worksheets
            For Each oButton In ws.Buttons
                If oButton.Caption = "Previous" Or oButton.Caption = "Index" Or oButton.Caption = "Next" Then
                    oButton.Left = oButton.TopLeftCell.Left
                    oButton.Top = oButton.TopLeftCell.Top
                    oButton.Width = oButton.TopLeftCell.Width
                    oButton.Height = oButton.TopLeftCell.Height
                End If
            Next oButton
        Next ws
    Next wkb
End Sub

Now just out of curiousity, is there a way to set it to resize to the cell that the majority of the button is on? Because the buttons were all manually resized, some of the left corners are in a cell I don't want them resized to but rather be resized to the cell that the majority of the button is covering. If not, its not a big deal b/c I can just go through and move all the buttons so that the top left is in the correct cell... but i figured I'd ask since you guys seem to know how to do absolutely everything by code... lol