Click event for checkbox from Forms toolbar
 

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

Click event for checkbox from Forms toolbar
 

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

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

Click event for checkbox from Forms toolbar
 

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

"Dave Peterson" wrote:

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

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

Click event for checkbox from Forms toolbar
 

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

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

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

Click event for checkbox from Forms toolbar
 

Option Explicit
Sub testme()
Dim OptBTN As OptionButton

Set OptBTN = ActiveSheet.OptionButtons(Application.Caller)

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


Carolyn wrote:

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

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

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


--

Dave Peterson

Click event for checkbox from Forms toolbar
 

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

Click event for checkbox from Forms toolbar
 

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



Carolyn wrote:

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