Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 60
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 60
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 60
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 60
Default 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


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Option Button on Forms Toolbar admannj Excel Discussion (Misc queries) 4 January 25th 05 02:07 AM
Macro to simply bring up the Find dialogue box?? marika1981 Excel Discussion (Misc queries) 14 January 14th 05 11:47 PM
Undoing LINKS in Excel 2000 jayceejay New Users to Excel 3 January 4th 05 06:58 PM
Stubborn toolbars in Excel 007 Excel Discussion (Misc queries) 9 December 11th 04 03:02 PM
Group buttons from the forms toolbar GregR Excel Discussion (Misc queries) 1 December 7th 04 02:25 AM


All times are GMT +1. The time now is 11:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"