| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copy and move check box (check boxes) with new cell link? | Excel Worksheet Functions | |||
| all the check boxes should be checked if i check a particular checkbox in that row | Excel Programming | |||
| How do I increase the size of check in check boxes | Excel Discussion (Misc queries) | |||
| Enable check box in protected sheet + group check boxes | Excel Discussion (Misc queries) | |||
| How do i create a value for check boxes or option boxes | Excel Discussion (Misc queries) | |||
Hybrid Mode
