Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change checkbox value
Hi,
So, on to my next dilemma - how do I change the value of a check box without initiating a Checkbox_Change sub attached to it? I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to false triggers the change event sub whether the check box is enabled or not. It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change() event. Any suggestions? Thanks! Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change checkbox value
mooresk257 wrote on 5/27/2010 :
Hi, So, on to my next dilemma - how do I change the value of a check box without initiating a Checkbox_Change sub attached to it? I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to false triggers the change event sub whether the check box is enabled or not. It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change() event. Any suggestions? Thanks! Scott Try... Application.EnableEvents = False Checkbox1.Value = False Application.EnableEvents = True -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change checkbox value
I put a check box and a command button on a new sheet, with the following
code for each: Private Sub CheckBox1_Click() If Sheet1.CheckBox1.Value = True Then MsgBox ("Yup") Else: MsgBox ("Nope") End If End Sub Private Sub CommandButton1_Click() Application.EnableEvents = False Sheet1.CheckBox1.Value = False Application.EnableEvents = True End Sub However, when I run the command button code to set the check box value to false, I still get a message box saying "nope". If events are turned off, I don't see why the code for the check box event would run. Or am I going about this the wrong way? "ker_01" wrote: There may be easier ways (consider testing "application.events = false" before you change the checkbox, and "application.events = true" immediately after, that might work?) but for straight (if inelegant) logic, add another variable Sub Checkbox_Change If MyVariable = True then 'all the code here end if End sub Sub MyOtherProcedure MyVariable = False CheckboxA = True CheckboxA = False MyVariable = True end sub Of course, you will need to set MyVariable to True in the workbook_open event, to make sure it is always enabled unless you decide to disable it. I think an uninitialized variable will have a null value, so maybe you could use that to your advantage, but again, I haven't tested that in actual use. HTH, Keith "mooresk257" wrote: Hi, So, on to my next dilemma - how do I change the value of a check box without initiating a Checkbox_Change sub attached to it? I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to false triggers the change event sub whether the check box is enabled or not. It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change() event. Any suggestions? Thanks! Scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change checkbox value
There may be easier ways (consider testing "application.events = false"
before you change the checkbox, and "application.events = true" immediately after, that might work?) but for straight (if inelegant) logic, add another variable Sub Checkbox_Change If MyVariable = True then 'all the code here end if End sub Sub MyOtherProcedure MyVariable = False CheckboxA = True CheckboxA = False MyVariable = True end sub Of course, you will need to set MyVariable to True in the workbook_open event, to make sure it is always enabled unless you decide to disable it. I think an uninitialized variable will have a null value, so maybe you could use that to your advantage, but again, I haven't tested that in actual use. HTH, Keith "mooresk257" wrote: Hi, So, on to my next dilemma - how do I change the value of a check box without initiating a Checkbox_Change sub attached to it? I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to false triggers the change event sub whether the check box is enabled or not. It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change() event. Any suggestions? Thanks! Scott |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change checkbox value
mooresk257 used his keyboard to write :
I put a check box and a command button on a new sheet, with the following code for each: Private Sub CheckBox1_Click() If Sheet1.CheckBox1.Value = True Then MsgBox ("Yup") Else: MsgBox ("Nope") End If End Sub Private Sub CommandButton1_Click() Application.EnableEvents = False Sheet1.CheckBox1.Value = False Application.EnableEvents = True End Sub However, when I run the command button code to set the check box value to false, I still get a message box saying "nope". If events are turned off, I don't see why the code for the check box event would run. Or am I going about this the wrong way? "ker_01" wrote: There may be easier ways (consider testing "application.events = false" before you change the checkbox, and "application.events = true" immediately after, that might work?) but for straight (if inelegant) logic, add another variable Sub Checkbox_Change If MyVariable = True then 'all the code here end if End sub Sub MyOtherProcedure MyVariable = False CheckboxA = True CheckboxA = False MyVariable = True end sub Of course, you will need to set MyVariable to True in the workbook_open event, to make sure it is always enabled unless you decide to disable it. I think an uninitialized variable will have a null value, so maybe you could use that to your advantage, but again, I haven't tested that in actual use. HTH, Keith "mooresk257" wrote: Hi, So, on to my next dilemma - how do I change the value of a check box without initiating a Checkbox_Change sub attached to it? I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to false triggers the change event sub whether the check box is enabled or not. It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change() event. Any suggestions? Thanks! Scott That's because EnableEvents apparently doesn't work in this case. I've never tried this before and so was recommending you 'try' my suggestion. I have tested this as of reading your new post. Here's what I recommend: 1. Add the following line to the declarations section of the sheet code module: Dim bDisableEvents As Boolean In your command button code: bDisableEvents = True Checkbox1.Value = False bDisableEvents = False In your Checkbox1_Click OR _Change event: If bDisableEvents Then Exit Sub Now, whenever you use the command button you won't get the msgbox. Whever you click the checkbox you will only get the msgbox if it's checked. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change checkbox value
This works great - thanks Garry!
"GS" wrote: mooresk257 used his keyboard to write : I put a check box and a command button on a new sheet, with the following code for each: Private Sub CheckBox1_Click() If Sheet1.CheckBox1.Value = True Then MsgBox ("Yup") Else: MsgBox ("Nope") End If End Sub Private Sub CommandButton1_Click() Application.EnableEvents = False Sheet1.CheckBox1.Value = False Application.EnableEvents = True End Sub However, when I run the command button code to set the check box value to false, I still get a message box saying "nope". If events are turned off, I don't see why the code for the check box event would run. Or am I going about this the wrong way? "ker_01" wrote: There may be easier ways (consider testing "application.events = false" before you change the checkbox, and "application.events = true" immediately after, that might work?) but for straight (if inelegant) logic, add another variable Sub Checkbox_Change If MyVariable = True then 'all the code here end if End sub Sub MyOtherProcedure MyVariable = False CheckboxA = True CheckboxA = False MyVariable = True end sub Of course, you will need to set MyVariable to True in the workbook_open event, to make sure it is always enabled unless you decide to disable it. I think an uninitialized variable will have a null value, so maybe you could use that to your advantage, but again, I haven't tested that in actual use. HTH, Keith "mooresk257" wrote: Hi, So, on to my next dilemma - how do I change the value of a check box without initiating a Checkbox_Change sub attached to it? I have a check box (Sheet1.CheckBox1.Value = True) and changing the value to false triggers the change event sub whether the check box is enabled or not. It does not seem to matter if it is a Checkbox_Click() or Checkbox_Change() event. Any suggestions? Thanks! Scott That's because EnableEvents apparently doesn't work in this case. I've never tried this before and so was recommending you 'try' my suggestion. I have tested this as of reading your new post. Here's what I recommend: 1. Add the following line to the declarations section of the sheet code module: Dim bDisableEvents As Boolean In your command button code: bDisableEvents = True Checkbox1.Value = False bDisableEvents = False In your Checkbox1_Click OR _Change event: If bDisableEvents Then Exit Sub Now, whenever you use the command button you won't get the msgbox. Whever you click the checkbox you will only get the msgbox if it's checked. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change checkbox value
on 5/28/2010, mooresk257 supposed :
This works great - thanks Garry! Glad you like it! Thanks for the feedback; -always appreciated! You might also be interested to know that you could easily make your command button toggle the checkbox value by replacing this line: CheckBox1.Value = False with: CheckBox1.Value = Not CheckBox1.Value HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use a checkbox to change calculations | Excel Programming | |||
Checkbox Name Change? | Excel Programming | |||
how do i change the value of a cell with a checkbox | Excel Worksheet Functions | |||
checkbox value change | Excel Programming | |||
CheckBox Change Event | Excel Programming |