Excel vba Renaming checkboxes

问题:

I have a worksheet that contains many checkboxes. Excel names these automatically as CheckBox1, CheckBox2, etc... However, for my document, I need to rename them all as Rij11_1, Rij11_2, etc (Rij11 being row 11, _1 being the first checkbox in that row, and so on). It is important that the renaming starts on _1 for each row.

Stackoverflow members Osknows and Dave DuPlantis helped me already on this one (THANKS A LOT FOR ALL YOUR HELP SO FAR), with this code:

Sub test()  
    Dim obj As OLEObject, ChkBoxRow as long  
    ChkBoxRow = 11   
    With Worksteets("Storia") 
    For Each obj In .OLEObjects     
    If TypeName(obj.Object) = "CheckBox" Then          
        if obj.TopLeftCell.Row = ChkBoxRow then            
            obj.Name = "Rij11_" & Right(obj.Name, 1)         
        end if      
    End If 
    Next obj
    End With
End Sub 

However, the first renamed checkbox on each row does not start on 1 (usually on 7, for some reason which escapes me), and if there are more than 10 checkboxes in a row, the numbering doesn't go beyond 10. After _9, I get _0 instead of _10 and then, after _0, it continues with _1 again, resulting in name duplicates in the row.

Is there anyone who can help me out with this renumbering issue?

Thanks a lot!

Kind regards, Marc

回答1:

Here's an easy way to get rid of these. Highlight the column or cells in which you want to remove the checkboxes and associated text. Then cut them using CTRL+C, after that move into a different sheet, paste them. (This method is if say you want to remove one column of checkboxes but keep another, or delete random checkboxes). Now hit F5, then Special, then Objects (on the sheet where you pasted the ones for deletion) they will all be selected, now just cut them, DONE! :)

回答2:

The issue is with the Right(obj.Name, 1) in the inner If statement. The renaming only sticks the rightmost character of the old object name onto the end of the new name. Try replacing it with the following:

Right(obj.Name, Len(obj.Name) - 8)

Where 8 is the length of "CheckBox".