Referencing checkboxes on a worksheet from a form

[Brian]

Hi,

I am trying to figure out how to reference some checkboxes directly placed
on a worksheet from code attached to a form. In other words, I want to click
a button on a form and tell it to remove some checkboxes on sheet. To
clarify that...yes I do want to "remove" the checkboxes, not simply to
uncheck them.

A second question: is there any way to allow a user to tick some checkboxes
on a form, and then new checkboxes will be automatically created on the
worksheet for those ticked items only?

I am new to VBA. Any help would be most appreciated.

Best regards,
Brian.

 

[NickHK]

Brian,
Like this ?

Private Sub CommandButton1_Click()
With Worksheets(1)
.OLEObjects("CheckBox1").Delete
End With
End Sub

For the 2nd part, record a macro whilst you add a check box to get something
like:

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=373.5, Top:=165.75, Width:=63.75, Height
_
:=20.25).Select

Combine this with the _Click event of your existing Checkbox.
Presumable you would need to delete it also if the check box is then
unchecked, otherwise the user will become confused; or do not use a check
box for this action.

NickHK

 

[Brian]

Hi Nick,

both parts of your answer were extremely useful to me. Thank you very much
indeed!!

Best regards,
Brian.

 

[Brian]

Hi again Nick,

I spoke a little too soon, I looked at your reply and it looked very
straight forward and logical. However, when I try to use the code to delete
a checkbox I get the following error:

Run-time error '1004':
Unable to get the OLEObjects property of the worksheets class, and it
highlights the following line: .OLEObjects(CheckBox10).Delete

I do have a Checkbox10 by the way. I am not sure what that means.

Best regards,
Brian.

 

[Dave Peterson]

Watch your double quotes!

..OLEObjects("CheckBox10").Delete

 

[Brian]

Hi Dave,

thanks for the reminder. I did actually have the double quotes there
originally and received the same error. I kept trying new things, one of
them being removing the double quotes. I forgot to put the quotes back when
I posted in the newsgroup.

I am trying to figure out a different way of going about what I am trying to
achieve. A little more reading is required I think.

Best regards,
Brian.

 

[Dave Peterson]

If you go back to excel and go into design mode (on that same control toolbox
toolbar) and then rightclick on the checkbox you want to delete, what's the name
that shows up in the namebox (to the left of the formula bar)?

Try using that.

And you're sure you used a checkbox from the Control toolbox toolbar--not
checkboxes from the Forms toolbar, right?????

 

[Brian]

Hi Dave,

If you are curious/interested at all as to what I am trying to achieve - I
have placed my test file on my webspace.
My document is at http://www.members.optusnet.com.au/cooloox/Newsgroups/ .

I have placed notes on the first worksheet setting out what the macro is
intended to do.

Best regards,
Brian.

 

[Dave Peterson]

I don't like opening other people's workbooks--especially when they contain
macros.

I'm not sure if you solved your problem, though.

Hi Dave,

If you are curious/interested at all as to what I am trying to achieve - I
have placed my test file on my webspace.
My document is at http://www.members.optusnet.com.au/cooloox/Newsgroups/ .

I have placed notes on the first worksheet setting out what the macro is
intended to do.

Best regards,
Brian.