Prev: Conditional Formatting with Hyperlinks
Next: Can we set the UI color scheme via VB[A} or XML?
From: mooresk257 on 28 May 2010 10:25
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
>
>
> .
>
From: GS on 28 May 2010 12:03
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


First  |  Prev  | 
Pages: 1 2
Prev: Conditional Formatting with Hyperlinks
Next: Can we set the UI color scheme via VB[A} or XML?