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

 

[mikerickson]

If they are Forms check boxes (not ActiveX)
Don't run it twice, lest you get boxes labeled "Clean - Reuse - Reuse"

Sub test()
    Dim oneShape As Shape

    For Each oneShape In ActiveSheet.Shapes
        With oneShape
            If .Type = msoFormControl Then
                If .FormControlType = xlCheckBox Then
                    With .TextFrame.Characters
                        .Text = Replace(.Text, "Clean", "Clean - Reuse")
                    End With
                End If
            End If
        End With
    Next oneShape
End Sub

 

[klarowe]

that is beautiful... thank you soooo much. I think you just saved me hours of work.

However, there are a couple things I need to add.
1) can you adjust that so that it will delete the box that says "Use As Is"
2) I need the newly named checkbox to increase in length so that all the text can appear. If you can get me started, I can play around with the length til it works

If it helps at all, there are no other checkboxes to worry about on the page (ie ActiveX)... if that will help get rid of some of the If statements and make anything easier....
Thanks again!

 

[wigi]

To delete shapes, use:

oneShape.Delete

with a suitable test on the name of the shape: oneShape.Name

Look at this page: http://www.rondebruin.nl/controlsobjectsworksheet.htm
("Delete only Forms controls")

Consider also the Width property.

 

[klarowe]

I figured that much but I'm having trouble filtering it out so to speak. I tried this but its not working:

If oneShape.TextFrame.Text = "Use As-Is" Then oneShape.Delete

 

[wigi]

If oneShape.TextFrame.Characters.Text = "Use As-Is" Then oneShape.Delete

 

[Rick Rothstein]

The macro seems to do what you asked for...

Sub DoThingsToCheckBoxes()
  Dim SH As Shape
  For Each SH In ActiveSheet.Shapes
      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
  Next
End Sub

 

[klarowe]

I changed it up a bit to loop through all sheets,

Sub checkboxchange()
    Dim SH As Shape
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        For Each SH In ws.Shapes
            If SH.TextFrame.Characters.Text = "Use As-Is" Then
                SH.Delete
            ElseIf SH.TextFrame.Characters.Text = "Clean" Then
                H.TextFrame.Characters.Text = "Clean - Reuse"
                SH.Width = 2 * SH.Width
            End If
        Next SH
    Next ws
End Sub

but either way I get an error (object doesn't support this property or method) on line

 If SH.TextFrame.Characters.Text = "Use As-Is" Then

I'm running excel 03 if that matters

 

[Gary McMaster]

Some shapes don't have a textframe. Lines for example.

I think you need to check and see if the shape is a control as in Mike's earlier code sample.

Gary

 

[klarowe]

Some shapes don't have a textframe. Lines for example.

I think you need to check and see if the shape is a control as in Mike's earlier code sample.

Gary

That was the trick...

Sub test()
    Dim sh As Shape
    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
End Sub

Don't think I have seen a code with so many if statements within an if statement, but it did the trick perfectly!!

Thank you all for your help... you can't imagine how much time you just saved me!!!!