Need to reset Check Box ID number

  • Thread starter Thread starter Bill D.
  • Start date Start date
B

Bill D.

I need to reset the numbering for controls placed on a worksheet. For
example: I put a Check Box on a work sheet using the Forms Toolbar and it
is Check Box 77. I want it to be Check Box 1. How can I reset the
numbering?

Bill
 
Bill,

Hold down ctrl and click on the check box, then type the name you want in
the left hand side of the formula bar where it is likely to say check box
77. Note, if there is an existing control with the name it will select it,
so you will have to delete the existing one first.

Robin Hammond
www.enhanceddatasystems.com
 
Bill,

Hold down ctrl and click on the check box, then type the name you want
in the left hand side of the formula bar where it is likely to say
check box 77. Note, if there is an existing control with the name it
will select it, so you will have to delete the existing one first.

Robin Hammond
www.enhanceddatasystems.com
Click to expand...

Robin, thanks for your reply but this is not my problem. I do not need
to merely change the name of the checkbox but need to change how the
worksheet sees the check box.As an example let's take the Check Box 77
and do the ctrl click and change it to MyCheckBox.Now I have two lines
of code in a macro called TestButtonID and assigned to the check box.

ButtonClicked = Application.Caller
Range("A1") = ButtonClicked

When the checkbox is clicked A1 shows check box 77 NOT MyCheckBox

How does Excel know for this particular sheet this is the 77th control
placed? All of the previous controls have been deleted by the way. Is
this a registery entery that keeps track of the controls?

Bill
 
Bill,

I don't think that you can do what you want. Excel maintains it'scount of
these objects internally, and as far as I am aware there is no way to get at
this counter.

Perhaps you would be better creating them programmatically where you could
set the name as you wish, like

Dim oCheck
Set oCheck = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=422.25, _
Top:=86.25, _
Width:=276, _
Height:=74.25) _
.Name = "Bob's Checkbox"


Clearly this could be automated within a loop for several items

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks Bob, for you reply.

I guess I'll have to look at a different approach. My VBA knowledge is
very limited, I am teaching myself as I go. I tried your suggested code
and see how I can use it. Shouldn't be too difficult since all the check
boxes will be in a single column. Three questions though.
1. How can I delete a checkbox created this way?
2. How can I assign a macro to the check box?
3. In the Excel Help files where can I find all of the variables
associated with OLEObjects.Add?

Thanks, Bill

Remove NotValid when replying direct. Thanks
 
You apparently didn't successfully change the name to MyCheckBox.
Application.Caller would Return MyCheckbox if you had.

If you reselect the checkbox and in the Name box see MyCheckbox and still
your code puts in Check Box 77, you have discovered some new behavior - but
I doubt you will see Check Box 77 if the name box doesn't show that.
 
Back
Top