Excel vba重命名复选框 [英] Excel vba Renaming checkboxes

问题描述

我有一个包含许多复选框的工作表。 Excel将这些自动命名为CheckBox1，CheckBox2等。但是，对于我的文档，我需要将它们全部重命名为Rij11_1，Rij11_2等（Rij11是第11行，_1是该行中的第一个复选框，依此类推） 。
对于每一行，重命名从_1开始是很重要的。

Stackoverflow的成员Osknows和Dave DuPlantis帮助我已经在这一个（谢谢你所有的帮助这么远），用这个代码：

Sub test（）
Dim obj As OLEObject，ChkBoxRow as long
ChkBoxRow = 11
使用Worksteets（Storia ）
对于每个obj在.OLEObjects
如果TypeName（obj.Object）=CheckBox然后
如果obj.TopLeftCell.Row = ChkBoxRow然后
obj.Name =Rij11_ & Right（obj.Name，1）
end if
End If
Next obj
End With
End Sub


然而，每一行上的第一个重命名的复选框不会从1开始（通常是7，由于某种原因转义了我），如果有超过10个复选框在一行中，编号不超过10.在_9之后，我获得_0而不是_10，然后，在_0之后，它继续使用_1，导致行中的名称重复。

有没有人可以帮我处理这个重新编号问题？

非常感谢！

亲爱的，
Marc

解决方案

容易的方法摆脱这些。突出显示要删除其中的复选框和相关文字的列或单元格。然后使用CTRL + C切割它们，之后移动到不同的工作表中，粘贴它们。 （此方法是，如果你想删除一列复选框，但保留另一个，或删除随机复选框）。现在点击F5，然后特殊，然后对象（在工作表上，您粘贴的那些删除），他们都将被选中，现在只是剪它们，完成！ :)

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

解决方案

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

这篇关于Excel vba重命名复选框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！