Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Option Button on Forms Toolbar | Excel Discussion (Misc queries) | |||
Macro to simply bring up the Find dialogue box?? | Excel Discussion (Misc queries) | |||
Undoing LINKS in Excel 2000 | New Users to Excel | |||
Stubborn toolbars in Excel | Excel Discussion (Misc queries) | |||
Group buttons from the forms toolbar | Excel Discussion (Misc queries) |