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.

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.Check Box.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" wrote in message
...
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.

Hi Nick,

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

Best regards,
Brian.



"NickHK" wrote in message
...
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.Check Box.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" wrote in message
...
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.

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.


"NickHK" wrote in message
...
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.Check Box.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" wrote in message
...
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.

Watch your double quotes!

..OLEObjects("CheckBox10").Delete



Brian wrote:

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.

"NickHK" wrote in message
...
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.Check Box.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" wrote in message
...
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.





--

Dave Peterson

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" wrote in message
...
Watch your double quotes!

.OLEObjects("CheckBox10").Delete



Brian wrote:

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.

"NickHK" wrote in message
...
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.Check Box.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" wrote in message
...
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.





--

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 wrote:

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" wrote in message
...
Watch your double quotes!

.OLEObjects("CheckBox10").Delete



Brian wrote:

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.

"NickHK" wrote in message
...
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.Check Box.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" wrote in message
...
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.





--

Dave Peterson

--

Dave Peterson

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" wrote in message
...
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 wrote:

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" wrote in message
...
Watch your double quotes!

.OLEObjects("CheckBox10").Delete



Brian wrote:

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.

"NickHK" wrote in message
...
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.Check Box.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" wrote in message
...
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.





--

Dave Peterson

--

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.

Brian wrote:

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" wrote in message
...
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 wrote:

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" wrote in message
...
Watch your double quotes!

.OLEObjects("CheckBox10").Delete



Brian wrote:

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.

"NickHK" wrote in message
...
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.Check Box.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" wrote in message
...
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.





--

Dave Peterson

--

Dave Peterson

--

Dave Peterson