Rename Excel Check Boxes VB code Please!!! 2

[mbernosky]

I have a workbook with over 3,000 check boxes. The are named Check Box 3 - Check Box 3000. I would like to change the name ie. Check Box 3 to chk3_EPO_C14 is there a way to do with in a macro.
Michael

 

[xlbo]

what's the naming convention ???
If there's no routine to follow it's gonna be pretty damn hard to do

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[SkipVought]

Hi,

Something like this ought to work...

Sub ChangeCkBxNames()
    On Error Resume Next
    For Each ws In Worksheets
        For Each sp In ws.Shapes
            If sp.FormControlType = xlCheckBox Then
                If Err.Number = 0 Then
                    nbr = Right(sp.Name, Len(sp.Name) - 10)
                    sp.Name = "chk" & nbr & "_EPO_C"
                Else
                    Err.Clear
                End If
            End If
        Next
    Next
End Sub

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mbernosky]

Thanks SkipVought
I really needed that

 

[SkipVought]

Geoff,

Guess you got that one by osmosis

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

guess I did - have one back ;-)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244