usform check boxes
 

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If


--
Thanks
Shawn

usform check boxes
 

These things are always fiddly. I find it usually involves looking at both
the OnClick event and the AfterUpdate event for the check box and juggling
around till it works. Good luck!


"Shawn" wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If


--
Thanks
Shawn

usform check boxes
 

These things are always fiddly. I find it usually involves looking at both
the OnClick event and the AfterUpdate event and juggling around till it
works. Good luck!

"Shawn" wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If


--
Thanks
Shawn

usform check boxes
 

I was hoping for some quick simple solution. :)
--
Thanks
Shawn


"Smallweed" wrote:

These things are always fiddly. I find it usually involves looking at both
the OnClick event and the AfterUpdate event for the check box and juggling
around till it works. Good luck!


"Shawn" wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If


--
Thanks
Shawn

usform check boxes
 

If I add and "after event" action to change the appropriate box to true it
works. This just seems to add lenght to the code, though. Any better option?
--
Thanks
Shawn


"Shawn" wrote:

I was hoping for some quick simple solution. :)
--
Thanks
Shawn


"Smallweed" wrote:

These things are always fiddly. I find it usually involves looking at both
the OnClick event and the AfterUpdate event for the check box and juggling
around till it works. Good luck!


"Shawn" wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If


--
Thanks
Shawn

usform check boxes
 

Except now, you can't unclick any single selection. Once you click it, it
stays checked???
--
Thanks
Shawn


"Shawn" wrote:

If I add and "after event" action to change the appropriate box to true it
works. This just seems to add lenght to the code, though. Any better option?
--
Thanks
Shawn


"Shawn" wrote:

I was hoping for some quick simple solution. :)
--
Thanks
Shawn


"Smallweed" wrote:

These things are always fiddly. I find it usually involves looking at both
the OnClick event and the AfterUpdate event for the check box and juggling
around till it works. Good luck!


"Shawn" wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If


--
Thanks
Shawn

usform check boxes
 

This does the trick (where chk3 is the "None of these" checkbox). At least
the separate myCheck routine cuts out some of the repitition!

Private Sub myCheck()
If Not chk1 And Not chk2 Then
chk3 = True
Else
chk3 = False
End If
End Sub

Private Sub chk1_AfterUpdate()
myCheck
End Sub

Private Sub chk2_AfterUpdate()
myCheck
End Sub

Private Sub chk3_AfterUpdate()
If chk3 Then
chk1 = False
chk2 = False
End If
End Sub


"Shawn" wrote:

Except now, you can't unclick any single selection. Once you click it, it
stays checked???
--
Thanks
Shawn


"Shawn" wrote:

If I add and "after event" action to change the appropriate box to true it
works. This just seems to add lenght to the code, though. Any better option?
--
Thanks
Shawn


"Shawn" wrote:

I was hoping for some quick simple solution. :)
--
Thanks
Shawn


"Smallweed" wrote:

These things are always fiddly. I find it usually involves looking at both
the OnClick event and the AfterUpdate event for the check box and juggling
around till it works. Good luck!


"Shawn" wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If


--
Thanks
Shawn

usform check boxes
 

(this forum's got a bit weird on the messages and I have to resend -
hopefully this isn't going to duplicate again!)

This does the trick (where chk3 is the "None of these" checkbox). At least
the separate myCheck routine cuts out some of the repitition:

Private Sub myCheck()
If Not chk1 And Not chk2 Then
chk3 = True
Else
chk3 = False
End If
End Sub

Private Sub chk1_AfterUpdate()
myCheck
End Sub

Private Sub chk2_AfterUpdate()
myCheck
End Sub

Private Sub chk3_AfterUpdate()
If chk3 Then
chk1 = False
chk2 = False
End If
End Sub


"Shawn" wrote:

Except now, you can't unclick any single selection. Once you click it, it
stays checked???
--
Thanks
Shawn


"Shawn" wrote:

If I add and "after event" action to change the appropriate box to true it
works. This just seems to add lenght to the code, though. Any better option?
--
Thanks
Shawn


"Shawn" wrote:

I was hoping for some quick simple solution. :)
--
Thanks
Shawn


"Smallweed" wrote:

These things are always fiddly. I find it usually involves looking at both
the OnClick event and the AfterUpdate event for the check box and juggling
around till it works. Good luck!


"Shawn" wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If


--
Thanks
Shawn

usform check boxes
 

One way is to use something like application.enableevents to stop the changes in
your code from firing the _click events for the other checkboxes.

I created a small userform with 5 checkboxes and a commandbutton on it. This
was the code behind the userform:


Option Explicit
Dim BlkProc As Boolean
Dim MaxCheckBoxes As Long
Dim NoneOfTheseCBXNumber As Long
Private Sub CheckBox1_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(1)
End Sub
Private Sub CheckBox2_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(2)
End Sub
Private Sub CheckBox3_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(3)
End Sub
Private Sub CheckBox4_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(4)
End Sub
Private Sub CheckBox5_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(5)
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Sub ChkCheckBoxes(myNum As Long)

Dim iCtr As Long
Dim NoneOfTheseCBX As Control

Set NoneOfTheseCBX = Me.Controls("Checkbox" & NoneOfTheseCBXNumber)

If myNum = NoneOfTheseCBXNumber Then
If NoneOfTheseCBX.Value = True Then
BlkProc = True
For iCtr = 1 To MaxCheckBoxes
If iCtr = NoneOfTheseCBXNumber Then
'skip it
Else
Me.Controls("checkbox" & iCtr).Value = False
End If
Next iCtr
End If
Else
BlkProc = True
NoneOfTheseCBX.Value = False
End If

BlkProc = False
End Sub
Private Sub UserForm_Initialize()
MaxCheckBoxes = 5 'how many checkboxes?
NoneOfTheseCBXNumber = 3 'any one you want
Me.Controls("Checkbox" & NoneOfTheseCBXNumber).Caption = "None of these"
End Sub


====
I did rely on the fact that all the checkboxes had nice names: Checkbox1, ...,
checkbox5. So I could loop through them easily.




Shawn wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If

--
Thanks
Shawn

--

Dave Peterson

usform check boxes
 

Wow...a lot of code. You would think there would be an easier way!
--
Thanks
Shawn


"Dave Peterson" wrote:

One way is to use something like application.enableevents to stop the changes in
your code from firing the _click events for the other checkboxes.

I created a small userform with 5 checkboxes and a commandbutton on it. This
was the code behind the userform:


Option Explicit
Dim BlkProc As Boolean
Dim MaxCheckBoxes As Long
Dim NoneOfTheseCBXNumber As Long
Private Sub CheckBox1_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(1)
End Sub
Private Sub CheckBox2_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(2)
End Sub
Private Sub CheckBox3_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(3)
End Sub
Private Sub CheckBox4_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(4)
End Sub
Private Sub CheckBox5_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(5)
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Sub ChkCheckBoxes(myNum As Long)

Dim iCtr As Long
Dim NoneOfTheseCBX As Control

Set NoneOfTheseCBX = Me.Controls("Checkbox" & NoneOfTheseCBXNumber)

If myNum = NoneOfTheseCBXNumber Then
If NoneOfTheseCBX.Value = True Then
BlkProc = True
For iCtr = 1 To MaxCheckBoxes
If iCtr = NoneOfTheseCBXNumber Then
'skip it
Else
Me.Controls("checkbox" & iCtr).Value = False
End If
Next iCtr
End If
Else
BlkProc = True
NoneOfTheseCBX.Value = False
End If

BlkProc = False
End Sub
Private Sub UserForm_Initialize()
MaxCheckBoxes = 5 'how many checkboxes?
NoneOfTheseCBXNumber = 3 'any one you want
Me.Controls("Checkbox" & NoneOfTheseCBXNumber).Caption = "None of these"
End Sub


====
I did rely on the fact that all the checkboxes had nice names: Checkbox1, ...,
checkbox5. So I could loop through them easily.




Shawn wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If

--
Thanks
Shawn

--

Dave Peterson

usform check boxes
 

If you look at it, the _click events are just copy|paste and a bit of editing.

Shawn wrote:

Wow...a lot of code. You would think there would be an easier way!
--
Thanks
Shawn

"Dave Peterson" wrote:

One way is to use something like application.enableevents to stop the changes in
your code from firing the _click events for the other checkboxes.

I created a small userform with 5 checkboxes and a commandbutton on it. This
was the code behind the userform:


Option Explicit
Dim BlkProc As Boolean
Dim MaxCheckBoxes As Long
Dim NoneOfTheseCBXNumber As Long
Private Sub CheckBox1_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(1)
End Sub
Private Sub CheckBox2_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(2)
End Sub
Private Sub CheckBox3_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(3)
End Sub
Private Sub CheckBox4_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(4)
End Sub
Private Sub CheckBox5_Click()
If BlkProc = True Then Exit Sub
Call ChkCheckBoxes(5)
End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Sub ChkCheckBoxes(myNum As Long)

Dim iCtr As Long
Dim NoneOfTheseCBX As Control

Set NoneOfTheseCBX = Me.Controls("Checkbox" & NoneOfTheseCBXNumber)

If myNum = NoneOfTheseCBXNumber Then
If NoneOfTheseCBX.Value = True Then
BlkProc = True
For iCtr = 1 To MaxCheckBoxes
If iCtr = NoneOfTheseCBXNumber Then
'skip it
Else
Me.Controls("checkbox" & iCtr).Value = False
End If
Next iCtr
End If
Else
BlkProc = True
NoneOfTheseCBX.Value = False
End If

BlkProc = False
End Sub
Private Sub UserForm_Initialize()
MaxCheckBoxes = 5 'how many checkboxes?
NoneOfTheseCBXNumber = 3 'any one you want
Me.Controls("Checkbox" & NoneOfTheseCBXNumber).Caption = "None of these"
End Sub


====
I did rely on the fact that all the checkboxes had nice names: Checkbox1, ...,
checkbox5. So I could loop through them easily.




Shawn wrote:

I have a userform with several check boxes on it. Users can click as many as
appropriate. The last option is "None of these". If the user clicks any
check box other than the "None of these" it changes the value of "None of
these" to false. If the user clicks the "None of these" checkbox it changes
the value of all other checkboxes to false.

My problem is, for example, when the user clicks "None of these" it clears
the others but leaves the "None of these" option blank. You have to click it
a 2nd time to change its value to true????? I want one click to clear the
others and change the value of the "None of these" option to true. I know I
have a circular reference. How do I get around this?

If

--
Thanks
Shawn

--

Dave Peterson


--

Dave Peterson