Excel vba 重命名复选框
我有一个包含许多复选框的工作表。 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
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
但是，每行上的第一个重命名的复选框不会从 1 开始（由于某种原因，通常从 7 开始）我没注意到），如果连续有超过 10 个复选框，则编号不会超过 10。在 _9 之后，我得到 _0 而不是_10，然后在 _0 之后，再次继续 _1，导致该行中出现名称重复。
有谁可以帮我解决这个重新编号的问题吗？
多谢！
亲切的问候， 马克
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这是摆脱这些的简单方法。突出显示要删除其中的复选框和关联文本的列或单元格。然后使用 CTRL+C 剪切它们，然后移动到另一张纸上，粘贴它们。 （如果您想删除一列复选框但保留另一列，或者删除随机复选框，则可以使用此方法）。现在按 F5，然后按“特殊”，然后按“对象”（在粘贴要删除的对象的工作表上），它们将全部被选中，现在只需剪切它们即可！ :)
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! :)
问题出在内部 If 语句中的
Right(obj.Name, 1)上。重命名仅将旧对象名称的最右边字符粘贴到新名称的末尾。尝试将其替换为以下内容：其中 8 是“CheckBox”的长度。
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:Where 8 is the length of "CheckBox".