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):

 

[Gary McMaster]

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.

You could use this to find out if the button is completely inside a single cell:

      If oButton.TopLeftCell.Address = oButton.BottomRightCell.Address Then
            'Button is completly inside a single cell, take whatever action
        Else
            'Button spans more than one cell
        End If

You would have to do the math for the "Else".

In samples already given you have seen how to find the left, top, width and height of the cell that contains the button as well as the same properties for the control (button) itself. Using simple addition & subtraction you should be able to calculate if most of the button is inside the top left or bottom right cells (assuming it doesn't span a whole block of cells). If needed you could adjust the top & left of the button so that its top left corner moves and causes it to fall into a different cell. Although it's simple math, it could easily turn into a project and be difficult to follow if you come back to it in the future.

Gary

 

[Gary McMaster]

I don't see any buttons on the screen shot in your original post but if the layout is as consistent as your checkboxes appear to be maybe you could force your buttons into specific rows & columns based on their caption:

Public Sub Test()

Dim oButton As Button

For Each oButton In ActiveSheet.Buttons

    If oButton.Caption = "Previous" Then
        oButton.Left = ActiveSheet.Columns(8).Left
        oButton.Top = ActiveSheet.Rows(22).Top
    End If
    
    If oButton.Caption = "Index" Then
        oButton.Left = ActiveSheet.Columns(10).Left
        oButton.Top = ActiveSheet.Rows(22).Top
    End If

    If oButton.Caption = "Next" Then
        oButton.Left = ActiveSheet.Columns(12).Left
        oButton.Top = ActiveSheet.Rows(22).Top
    End If

Next oButton

End Sub

 

[klarowe]

I don't see any buttons on the screen shot in your original post but if the layout is as consistent as your checkboxes appear to be maybe you could force your buttons into specific rows & columns based on their caption:

The are in consistent columns, but not rows. I'm going to play around with that a bit and see if I can get it to work. But i think it will...

Edit: I was able to get them all to work properly in the test as far as lining up with the correct column before resizing, but some do extend above the actual row I want them to be resized to. Is there a way to just make a slight shift on all of them downward (what would equal about 3 arrow clicks while the buttons were highlighted)??? If so then I can run that to move them down into the correct row, then run the column line up, then finally the resize and everything will work perfectly.
Thanks again for all the help.

Time for some lunch and then I'll be back at it some more

 

[klarowe]

Got it all worked out. So far its working perfectly (only tried on my test report for now):

Sub ResizeButtons()
    Application.EnableEvents = False
    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" Then obutton.Left = ws.Columns("B").Left
                If obutton.Caption = "Index" Then obutton.Left = ws.Columns("E").Left
                If obutton.Caption = "Next" Then obutton.Left = ws.Columns("H").Left
 
                If obutton.Caption = "Previous" Or obutton.Caption = "Index" Or obutton.Caption = "Next" Then
                    obutton.ShapeRange.IncrementTop 5#
                    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
    Application.EnableEvents = True
End Sub

Thanks again to all for your help. Once again you guys went above and beyond to help me on these **** reports... lol

Edit: Ok, I lied, I need just one more thing.. lol. Is there a way to call out the row where the buttons are located? I'd like to set the row height to make sure the buttons aren't being cut off.

 

[klarowe]

Ok, nvmd. I figured it out.

Just added

obutton.TopLeftCell.RowHeight = 15#

after the increment.

So everything really does work perfect now.
Thanks again!!!