Naming Check Boxes

[Brad E.]

I am using quite a few Check Boxes on an input page for my co-workers. When
I right-click the first CB, "Check Box 1" appears left of the Formula Bar. I
can also manipulate this CB in VBA using "ActiveSheet.Shapes(1).". This also
works for CB 2 and CB 3. However, when I right click the CB which VBA knows
as Shape(4), "Check Box 19" appears left of the Formula Bar.

I want "Check Box X" to coordinate with Shape(X) so I know what I am going
to be changing in VBA. Is there a way to do that? Yesterday, when I clicked
Shape(3), it was labelled as "Check Box 4". I clicked on that entry and
changed the "4" to "3", so now Shape(3) has at least two names. When I
attempt to change "Check Box 19" (which is Shape(4)) to "4", Shape(3) is
selected.

I hope it didn't get too confusing.

 

[JLGWhiz]

You might be getting into something that can keep you busy for a while. As
you make changes to your sheet configuration, deleting, adding and moving
controls around, the index numbers for the controls are assigned by VBA as
you add and delete them by category of control. The shape index number is
assigned by VBA as a shape is added or deleted no matter what category. I
find it personally convenient to just name the controls so that I know what
it is supposed to do and keep track of it that way. Then use the control
name in my code instead of the index numbers.

 

[Brad E.]

Thank you, GWhiz.

While waiting for your response, I kept playing with the VBA and realized
this would ultimately be the code I want.

ActiveSheet.Shapes(4).Name = "Check Box 4"

However, I get a "Run-time error (70) // Permission denied" message.

I appreciate your suggestion about conventionally naming the shapes. I will
take that approach.

 

[Dave Peterson]

You can rename the checkboxes manually by
select a checkbox
type the new name in the namebox (and hit enter)

Is there a reason that you want to use the Shapes collection to get to the
Checkboxes?

You can use something like:

dim cbx as checkbox
for each cbx in activesheet.checkboxes
'do something
with cbx
msgbox .name & .topleftcell.address & vblf & .value
end with
next cbx

or you could loop through the first 17 via:

dim iCtr as long
for ictr = 1 to 17
with activesheet.checkboxes("Checkbox " & ictr)
msgbox .name & .topleftcell.address & vblf & .value
end with
next ictr

Then you could avoid the Shapes collection altogether.