Click event for checkbox from Forms toolbar

[Carolyn]

Why can't I get this? I create a checkbox from the Forms toolbar. I want to
set up a macro that will change specified worksheet cell values when the
checkbox is clicked. Really, I want to test the checkbox value and react
accordingly. So I should be able to set up a click event macro. Doesn't sound
too hard--but I cannot get the darned thing to work.

I tried the code below from Scott's reply to the thread 8/16/2006
"***Important*** Question about check boxes". Where do I paste the code in
the VB window? I've tried within "sheet 1" and got an error "the macro cannot
be found". So I tried adding a module and putting the code there, but got a
runtime error "object required".

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox2.Enabled = False
CheckBox3.Enabled = True
CheckBox4.Enabled = True
Else
CheckBox2.Enabled = True
CheckBox3.Enabled = False
CheckBox4.Enabled = False
endif
end sub

Appreciate any assistance.
Carolyn

 

[Dave Peterson]

The code that you borrowed belongs to checkboxes from the Control toolbox
toolbar.

Since you used a checkbox from the Forms toolbar, your macro will be placed in a
General module.

And you'll rightclick on the checkbox and choose assign macro to, er, assign the
macro to the checkbox.

This may help you (or not....):

Option Explicit
Sub testme01()

Dim myCBX As CheckBox

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX.Value = xlOn Then
'do what you want if it's checked
Else
'do what you want if it's not checked
End If

End Sub

 

[Carolyn]

Thank you very much Dave! That did help. Now it's working. Carolyn

 

[Carolyn]

Okay, that worked. But I cannot seem to get the same code to work for radio
buttons. Any suggestions?

Here's some code below that I've tried, but I get a runtime error.

Sub testme02()

Dim BTN As Button
'Dim BTN As Shape

'Following line not working for radio buttons. 08-16-06 cjs.
Set BTN = ActiveSheet.Buttons(Application.Caller)
'Set BTN = ActiveSheet.Shapes(Application.Caller)

'Handy code for getting name of checkbox or radio button.
MsgBox Application.Caller & vbLf _
& BTN.Name & vbLf _
& BTN.Caption

End Sub

Thanks,
Carolyn

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim OptBTN As OptionButton

Set OptBTN = ActiveSheet.OptionButtons(Application.Caller)

MsgBox OptBTN.Name & vbLf & OptBTN.Caption
End Sub

 

[Carolyn]

Thank you very much again, Dave.

One more twist. I would also like to be able to disable specific worksheet
cells. First, I clear the contents of the cell, then I want to disable it.
(Note the workbook/sheet will ultimately be protected.) Basically, when one
checkbox is clicked, I want to turn off a cell entry. When the checkbox is
clicked again, I want to turn on cell entry.

Using the following line of code is causing an error.
Range("MSRent").Locked = True
Trying to select the cell first isn't helping.
Range("MSRent").Select
Selection.Locked = True

Here's the code, with the current incorrect lines for locking/disabling the
cell.

Option Explicit
Sub ChkBoxMSFree()

Dim myCBX As CheckBox

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX.Value = xlOn Then
'do what you want if it's checked.
'go to cell(s) and set values.
Range("MSRent").Locked = False
Range("MSRent").Value = ""
Range("MSRent").Locked = True

Else
'do what you want if it's not checked.
'go to cell(s) and set values.
Range("MSRent").Locked = False

End If

End Sub

 

[Dave Peterson]

Remember that locked cells don't mean much unless the worksheet is protected.
And to change the lockedness of a cell, you'll have to unprotect the worksheet.

Option Explicit
Sub ChkBoxMSFree()

Dim myCBX As CheckBox

Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)

If myCBX.Value = xlOn Then
'do what you want if it's checked.
'go to cell(s) and set values.
activesheet.unprotect password:="hi"
Range("MSRent").Locked = False
Range("MSRent").Value = ""
Range("MSRent").Locked = True
activesheet.protect password:="hi"

Else
'do what you want if it's not checked.
'go to cell(s) and set values.
activesheet.unprotect password:="hi"
Range("MSRent").Locked = False
activesheet.protect password:="hi"
End If

End Sub