Simple Checkbox Message Code

Hey -
Somewhat newbie to VBA - Can anyone tell me what is wrong with this code?
Error is "Object doesn't support this method". Thanks a ton.
Steve

Sub CheckBoxChange()
If ActiveSheet.CheckBox2.Value = True Then
If MsgBox("Do you really want to change the risk level?", _
vbQuestion + vbYesNo) = vbYes Then
ActiveSheet.CheckBox2.Value = False
End If
ElseIf ActiveSheet.CheckBox2.Value = False Then
If MsgBox("Do you really want to change the risk level?", _
vbQuestion + vbYesNo) = vbYes Then
ActiveSheet.CheckBox2.Value = True
End If
End If
End Sub

Are you using the CheckBox control from the Controls toolbar? Is
you check box actually named CheckBox2?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"nemadrias"
<nemadrias.29i7kz_1150476301.7062@excelforum-nospam.com> wrote in
message
news:nemadrias.29i7kz_1150476301.7062@excelforum-nospam.com...
>
> Hey -
> Somewhat newbie to VBA - Can anyone tell me what is wrong with
> this
> code?
> Error is "Object doesn't support this method". Thanks a ton.
> Steve
>
> Sub CheckBoxChange()
> If ActiveSheet.CheckBox2.Value = True Then
> If MsgBox("Do you really want to change the risk level?", _
> vbQuestion + vbYesNo) = vbYes Then
> ActiveSheet.CheckBox2.Value = False
> End If
> ElseIf ActiveSheet.CheckBox2.Value = False Then
> If MsgBox("Do you really want to change the risk level?", _
> vbQuestion + vbYesNo) = vbYes Then
> ActiveSheet.CheckBox2.Value = True
> End If
> End If
> End Sub
>
>
> --
> nemadrias
> ------------------------------------------------------------------------
> nemadrias's Profile:
> http://www.excelforum.com/member.php...o&userid=24613
> View this thread:
> http://www.excelforum.com/showthread...hreadid=552736
>

Chip -
I'm actually using it from the forms toolbar. But it is called CheckBox2.

Originally Posted by Chip Pearson

Are you using the CheckBox control from the Controls toolbar? Is
you check box actually named CheckBox2?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"nemadrias"
<nemadrias.29i7kz_1150476301.7062@excelforum-nospam.com> wrote in
message
news:nemadrias.29i7kz_1150476301.7062@excelforum-nospam.com...
>
> Hey -
> Somewhat newbie to VBA - Can anyone tell me what is wrong with
> this
> code?
> Error is "Object doesn't support this method". Thanks a ton.
> Steve
>
> Sub CheckBoxChange()
> If ActiveSheet.CheckBox2.Value = True Then
> If MsgBox("Do you really want to change the risk level?", _
> vbQuestion + vbYesNo) = vbYes Then
> ActiveSheet.CheckBox2.Value = False
> End If
> ElseIf ActiveSheet.CheckBox2.Value = False Then
> If MsgBox("Do you really want to change the risk level?", _
> vbQuestion + vbYesNo) = vbYes Then
> ActiveSheet.CheckBox2.Value = True
> End If
> End If
> End Sub
>
>
> --
> nemadrias
> ------------------------------------------------------------------------
> nemadrias's Profile:
> http://www.excelforum.com/member.php...o&userid=24613
> View this thread:
> http://www.excelforum.com/showthread...hreadid=552736
>

So the macro is in a general module and it's assigned to that checkbox?

Option Explicit
Sub CheckBoxChange()
Dim myCBX As CheckBox
Dim myOtherCBX As CheckBox

'the one you just changed
Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
Set myOtherCBX = ActiveSheet.CheckBoxes("Checkbox2")

If MsgBox("Do you really want to change the risk level?", _
vbQuestion + vbYesNo) = vbYes Then
If myCBX.Value = xlOn Then
myOtherCBX.Value = xlOff
Else
myOtherCBX.Value = xlOn
End If
Else
If myCBX.Value = xlOn Then
myCBX.Value = xlOff
Else
myCBX.Value = xlOn
End If

End If

End Sub

The checkbox names are pretty unusual for checkboxes from the Forms toolbar and
I'm not sure I'd have one checkbox control another one (why not just use that
single checkbox), but I think that this works.

nemadrias wrote:
>
> Chip -
> I'm actually using it from the forms toolbar. But it is called
> CheckBox2.
>
> Chip Pearson Wrote:
> > Are you using the CheckBox control from the Controls toolbar? Is
> > you check box actually named CheckBox2?
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "nemadrias"
> > <nemadrias.29i7kz_1150476301.7062@excelforum-nospam.com> wrote in
> > message
> > news:nemadrias.29i7kz_1150476301.7062@excelforum-nospam.com...
> > >
> > > Hey -
> > > Somewhat newbie to VBA - Can anyone tell me what is wrong with
> > > this
> > > code?
> > > Error is "Object doesn't support this method". Thanks a ton.
> > > Steve
> > >
> > > Sub CheckBoxChange()
> > > If ActiveSheet.CheckBox2.Value = True Then
> > > If MsgBox("Do you really want to change the risk level?", _
> > > vbQuestion + vbYesNo) = vbYes Then
> > > ActiveSheet.CheckBox2.Value = False
> > > End If
> > > ElseIf ActiveSheet.CheckBox2.Value = False Then
> > > If MsgBox("Do you really want to change the risk level?", _
> > > vbQuestion + vbYesNo) = vbYes Then
> > > ActiveSheet.CheckBox2.Value = True
> > > End If
> > > End If
> > > End Sub
> > >
> > >
> > > --
> > > nemadrias
> > >
> > ------------------------------------------------------------------------
> > > nemadrias's Profile:
> > > http://www.excelforum.com/member.php...o&userid=24613
> > > View this thread:
> > > http://www.excelforum.com/showthread...hreadid=552736
> > >
>
> --
> nemadrias
> ------------------------------------------------------------------------
> nemadrias's Profile: http://www.excelforum.com/member.php...o&userid=24613
> View this thread: http://www.excelforum.com/showthread...hreadid=552736

--

Dave Peterson

Hey -
Thanks so much for your help so far. That does execute, but I think I explained what I'm trying to do badly. If any certain checkboxes out of (N number of) checkboxes are checked, I want to be able to ask if the user really wants to change TO THAT checkbox and then uncheck the others of the group. So, for instance, in a group of 3 checkboxes, if the first is checked and the user checks the 3rd, it will ask for confirmation, uncheck the first, then check the third. Can you give me some assistance with that? Thanks again, and sorry for the lack of clarity.
Steve

I'm not sure I understand, but as a user, I think I would rather click on the
checkboxes themselves to change them--rather than answering several prompts that
ask me a question about each checkbox.

Are you trying to have a "master" checkbox that turns all the other checkboxes
on or off?

And if you are, how many checkboxes does that master checkbox control and how
many groups are there?

nemadrias wrote:
>
> Hey -
> Thanks so much for your help so far. That does execute, but I think I
> explained what I'm trying to do badly. If any certain checkboxes out
> of (N number of) checkboxes are checked, I want to be able to ask if
> the user really wants to change TO THAT checkbox and then uncheck the
> others of the group. So, for instance, in a group of 3 checkboxes, if
> the first is checked and the user checks the 3rd, it will ask for
> confirmation, uncheck the first, then check the third. Can you give me
> some assistance with that? Thanks again, and sorry for the lack of
> clarity.
> Steve
>
> --
> nemadrias
> ------------------------------------------------------------------------
> nemadrias's Profile: http://www.excelforum.com/member.php...o&userid=24613
> View this thread: http://www.excelforum.com/showthread...hreadid=552736

--

Dave Peterson

The user will click on the checkbox they are going to change to, but I want to ask for confirmation before they change to it because there are many checkboxes grouped closely that if changed incorrectly could cause some confusion, and it's fairly easy to select the wrong one by accident. The checkboxes are signifying a level of risk involved with each phase of a project, and if the risk level is changed by accident the reports will be off, so I just want to have this added security built in. Any further questions please let me know. Thanks again -
Steve

Originally Posted by Dave Peterson

I'm not sure I understand, but as a user, I think I would rather click on the
checkboxes themselves to change them--rather than answering several prompts that
ask me a question about each checkbox.

Are you trying to have a "master" checkbox that turns all the other checkboxes
on or off?

And if you are, how many checkboxes does that master checkbox control and how
many groups are there?

nemadrias wrote:
>
> Hey -
> Thanks so much for your help so far. That does execute, but I think I
> explained what I'm trying to do badly. If any certain checkboxes out
> of (N number of) checkboxes are checked, I want to be able to ask if
> the user really wants to change TO THAT checkbox and then uncheck the
> others of the group. So, for instance, in a group of 3 checkboxes, if
> the first is checked and the user checks the 3rd, it will ask for
> confirmation, uncheck the first, then check the third. Can you give me
> some assistance with that? Thanks again, and sorry for the lack of
> clarity.
> Steve
>
> --
> nemadrias
> ------------------------------------------------------------------------
> nemadrias's Profile: http://www.excelforum.com/member.php...o&userid=24613
> View this thread: http://www.excelforum.com/showthread...hreadid=552736

--

Dave Peterson

Isn't this a case where OptionButtons would be more appropriate?

BTW As a quick reference:

For controls from the FORMS toolbar, the VBA syntax is:

Sheet.DrawingObjects("ControlName").Property|Method ... *

and...
For controls from the CONTROL TOOLBOX toolbar, the VBA syntax is:

Sheet.ControlName.Property|Method ...

Regards,
BizMark

P.S. * You may substitute 'DrawingObjects' for 'EditBoxes',
'DropDowns', 'Checkboxes', etc. etc. but it is easier to let Excel
evaluate the type for you - especially if you redesign your form with
other types of controls; if you then give them the same names you have
less code to change.

Conversely, doing this with Control Toolbox controls is dodgy, as
deleting an object and re-defining one with the same name as the
deleted object can sometimes send the VBA compiler into a spin and not
associate event procedures with the new control - or decide to wait
till runtime to tell you if parameter declarations are incorrect.

Dave Peterson Wrote:
> I'm not sure I understand, but as a user, I think I would rather click
> on the
> checkboxes themselves to change them--rather than answering several
> prompts that
> ask me a question about each checkbox.
>
> Are you trying to have a "master" checkbox that turns all the other
> checkboxes
> on or off?
>
> And if you are, how many checkboxes does that master checkbox control
> and how
> many groups are there?
>
> nemadrias wrote:
>
> Hey -
> Thanks so much for your help so far. That does execute, but I think
> I
> explained what I'm trying to do badly. If any certain checkboxes out
> of (N number of) checkboxes are checked, I want to be able to ask if
> the user really wants to change TO THAT checkbox and then uncheck the
> others of the group. So, for instance, in a group of 3 checkboxes,
> if
> the first is checked and the user checks the 3rd, it will ask for
> confirmation, uncheck the first, then check the third. Can you give
> me
> some assistance with that? Thanks again, and sorry for the lack of
> clarity.
> Steve
>
> --
> nemadrias
>
> ------------------------------------------------------------------------
> nemadrias's Profile:
> http://www.excelforum.com/member.php...o&userid=24613
> View this thread:
> http://www.excelforum.com/showthread...hreadid=552736
>
> --
>
> Dave Peterson


--
BizMark

I guess I don't have any other suggestions except to take that first suggestion
and modify it to include all the checkboxes you need. I guess I still don't
understand what controls what and how the checkboxes are "grouped".

nemadrias wrote:
>
> The user *will *click on the checkbox they are going to change to, but I
> want to ask for confirmation before they change to it because there are
> many checkboxes grouped closely that if changed incorrectly could cause
> some confusion, and it's fairly easy to select the wrong one by
> accident. The checkboxes are signifying a level of risk involved with
> each phase of a project, and if the risk level is changed by accident
> the reports will be off, so I just want to have this added security
> built in. Any further questions please let me know. Thanks again -
> Steve
>
> Dave Peterson Wrote:
> > I'm not sure I understand, but as a user, I think I would rather click
> > on the
> > checkboxes themselves to change them--rather than answering several
> > prompts that
> > ask me a question about each checkbox.
> >
> > Are you trying to have a "master" checkbox that turns all the other
> > checkboxes
> > on or off?
> >
> > And if you are, how many checkboxes does that master checkbox control
> > and how
> > many groups are there?
> >
> > nemadrias wrote:
> > >
> > > Hey -
> > > Thanks so much for your help so far. That does execute, but I think
> > I
> > > explained what I'm trying to do badly. If any certain checkboxes
> > out
> > > of (N number of) checkboxes are checked, I want to be able to ask if
> > > the user really wants to change TO THAT checkbox and then uncheck
> > the
> > > others of the group. So, for instance, in a group of 3 checkboxes,
> > if
> > > the first is checked and the user checks the 3rd, it will ask for
> > > confirmation, uncheck the first, then check the third. Can you give
> > me
> > > some assistance with that? Thanks again, and sorry for the lack of
> > > clarity.
> > > Steve
> > >
> > > --
> > > nemadrias
> > >
> > ------------------------------------------------------------------------
> > > nemadrias's Profile:
> > http://www.excelforum.com/member.php...o&userid=24613
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=552736
> >
> > --
> >
> > Dave Peterson
>
> --
> nemadrias
> ------------------------------------------------------------------------
> nemadrias's Profile: http://www.excelforum.com/member.php...o&userid=24613
> View this thread: http://www.excelforum.com/showthread...hreadid=552736

--

Dave Peterson

I think I'm just going to use option boxes. I wanted check boxes because I wanted to be able to select more than one in a group, but I can do without. Thanks so much for all of your help, though - have a great week.
Steve

Originally Posted by Dave Peterson

I guess I don't have any other suggestions except to take that first suggestion
and modify it to include all the checkboxes you need. I guess I still don't
understand what controls what and how the checkboxes are "grouped".

nemadrias wrote:
>
> The user *will *click on the checkbox they are going to change to, but I
> want to ask for confirmation before they change to it because there are
> many checkboxes grouped closely that if changed incorrectly could cause
> some confusion, and it's fairly easy to select the wrong one by
> accident. The checkboxes are signifying a level of risk involved with
> each phase of a project, and if the risk level is changed by accident
> the reports will be off, so I just want to have this added security
> built in. Any further questions please let me know. Thanks again -
> Steve
>
> Dave Peterson Wrote:
> > I'm not sure I understand, but as a user, I think I would rather click
> > on the
> > checkboxes themselves to change them--rather than answering several
> > prompts that
> > ask me a question about each checkbox.
> >
> > Are you trying to have a "master" checkbox that turns all the other
> > checkboxes
> > on or off?
> >
> > And if you are, how many checkboxes does that master checkbox control
> > and how
> > many groups are there?
> >
> > nemadrias wrote:
> > >
> > > Hey -
> > > Thanks so much for your help so far. That does execute, but I think
> > I
> > > explained what I'm trying to do badly. If any certain checkboxes
> > out
> > > of (N number of) checkboxes are checked, I want to be able to ask if
> > > the user really wants to change TO THAT checkbox and then uncheck
> > the
> > > others of the group. So, for instance, in a group of 3 checkboxes,
> > if
> > > the first is checked and the user checks the 3rd, it will ask for
> > > confirmation, uncheck the first, then check the third. Can you give
> > me
> > > some assistance with that? Thanks again, and sorry for the lack of
> > > clarity.
> > > Steve
> > >
> > > --
> > > nemadrias
> > >
> > ------------------------------------------------------------------------
> > > nemadrias's Profile:
> > http://www.excelforum.com/member.php...o&userid=24613
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=552736
> >
> > --
> >
> > Dave Peterson
>
> --
> nemadrias
> ------------------------------------------------------------------------
> nemadrias's Profile: http://www.excelforum.com/member.php...o&userid=24613
> View this thread: http://www.excelforum.com/showthread...hreadid=552736

--

Dave Peterson

Sorry I couldn't help.

nemadrias wrote:
>
> I think I'm just going to use option boxes. I wanted check boxes
> because I wanted to be able to select more than one in a group, but I
> can do without. Thanks so much for all of your help, though - have a
> great week.
> Steve
>
> Dave Peterson Wrote:
> > I guess I don't have any other suggestions except to take that first
> > suggestion
> > and modify it to include all the checkboxes you need. I guess I still
> > don't
> > understand what controls what and how the checkboxes are "grouped".
> >
> > nemadrias wrote:
> > >
> > > The user *will *click on the checkbox they are going to change to,
> > but I
> > > want to ask for confirmation before they change to it because there
> > are
> > > many checkboxes grouped closely that if changed incorrectly could
> > cause
> > > some confusion, and it's fairly easy to select the wrong one by
> > > accident. The checkboxes are signifying a level of risk involved
> > with
> > > each phase of a project, and if the risk level is changed by
> > accident
> > > the reports will be off, so I just want to have this added security
> > > built in. Any further questions please let me know. Thanks again -
> > > Steve
> > >
> > > Dave Peterson Wrote:
> > > > I'm not sure I understand, but as a user, I think I would rather
> > click
> > > > on the
> > > > checkboxes themselves to change them--rather than answering
> > several
> > > > prompts that
> > > > ask me a question about each checkbox.
> > > >
> > > > Are you trying to have a "master" checkbox that turns all the
> > other
> > > > checkboxes
> > > > on or off?
> > > >
> > > > And if you are, how many checkboxes does that master checkbox
> > control
> > > > and how
> > > > many groups are there?
> > > >
> > > > nemadrias wrote:
> > > > >
> > > > > Hey -
> > > > > Thanks so much for your help so far. That does execute, but I
> > think
> > > > I
> > > > > explained what I'm trying to do badly. If any certain
> > checkboxes
> > > > out
> > > > > of (N number of) checkboxes are checked, I want to be able to ask
> > if
> > > > > the user really wants to change TO THAT checkbox and then
> > uncheck
> > > > the
> > > > > others of the group. So, for instance, in a group of 3
> > checkboxes,
> > > > if
> > > > > the first is checked and the user checks the 3rd, it will ask
> > for
> > > > > confirmation, uncheck the first, then check the third. Can you
> > give
> > > > me
> > > > > some assistance with that? Thanks again, and sorry for the lack
> > of
> > > > > clarity.
> > > > > Steve
> > > > >
> > > > > --
> > > > > nemadrias
> > > > >
> > > >
> > ------------------------------------------------------------------------
> > > > > nemadrias's Profile:
> > > > http://www.excelforum.com/member.php...o&userid=24613
> > > > > View this thread:
> > > > http://www.excelforum.com/showthread...hreadid=552736
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > >
> > > --
> > > nemadrias
> > >
> > ------------------------------------------------------------------------
> > > nemadrias's Profile:
> > http://www.excelforum.com/member.php...o&userid=24613
> > > View this thread:
> > http://www.excelforum.com/showthread...hreadid=552736
> >
> > --
> >
> > Dave Peterson
>
> --
> nemadrias
> ------------------------------------------------------------------------
> nemadrias's Profile: http://www.excelforum.com/member.php...o&userid=24613
> View this thread: http://www.excelforum.com/showthread...hreadid=552736

--

Dave Peterson