Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > CheckBox to tick other CheckBoxes
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 204
Default CheckBox to tick other CheckBoxes
Hello, I have a userform with checkboxes for each of four years (Y0, Y1, Y2
& Y3)
I want to be able to tick them all by ticking the 'tick-all' checkbox

Similarly, if I deselect any of them the 'tick-all' box should be
automatically unchecked
and if I tick them all manually, the 'tick-all' box should be automatically
checked

I have seen this setup before - so I know it's possible.

My problem is that the event procedures are all triggering each other and so
it's a bit of a mess.

I've tried EnableEvents = false, but it doesn't do what I want

A sample of my code is below, but does anyone know a way to make it work?
please?

'===========================
Private Sub chkHeadAll_Change()
Application.EnableEvents = False
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
Application.EnableEvents = True
End Sub

Private Sub chkHeadY0_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub

Private Sub chkHeadY1_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub
'===========================

Thanks


M

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,533
Default CheckBox to tick other CheckBoxes
Hi

Two things:

In chkHeadAll_Change(), test if chkHeadAll is true, and if it is set all
years true, else do nothing.

In each year change sub, you should only check on the current checkbox:

If Not chkHeadY0.Value Then chkHeadAll.Value = False


See my example below:

Private Sub CheckBox1_change()
If Me.CheckBox1 = True Then
Me.CheckBox2.Value = True
Me.CheckBox3.Value = True
Me.CheckBox4.Value = True
End If
End Sub

Private Sub CheckBox2_change()
If Me.CheckBox2 And Me.CheckBox3 And Me.CheckBox4 Then Me.CheckBox1 = True
If Not Me.CheckBox2 Then Me.CheckBox1 = False
End Sub

Private Sub CheckBox3_change()
If Me.CheckBox2 And Me.CheckBox3 And Me.CheckBox4 Then Me.CheckBox1 = True
If Not Me.CheckBox3 Then Me.CheckBox1 = False
End Sub

Private Sub CheckBox4_change()
If Me.CheckBox2 And Me.CheckBox3 And Me.CheckBox4 Then Me.CheckBox1 = True
If Not Me.CheckBox4 Then Me.CheckBox1 = False
End Sub

Hopes this helps.
...
Per

"Michelle" skrev i meddelelsen
...
Hello, I have a userform with checkboxes for each of four years (Y0, Y1,
Y2 & Y3)
I want to be able to tick them all by ticking the 'tick-all' checkbox

Similarly, if I deselect any of them the 'tick-all' box should be
automatically unchecked
and if I tick them all manually, the 'tick-all' box should be
automatically checked

I have seen this setup before - so I know it's possible.

My problem is that the event procedures are all triggering each other and
so it's a bit of a mess.

I've tried EnableEvents = false, but it doesn't do what I want

A sample of my code is below, but does anyone know a way to make it work?
please?

'===========================
Private Sub chkHeadAll_Change()
Application.EnableEvents = False
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
Application.EnableEvents = True
End Sub

Private Sub chkHeadY0_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub

Private Sub chkHeadY1_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub
'===========================

Thanks


M

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 204
Default CheckBox to tick other CheckBoxes
When I run the check box1 change event (starting with all the boxes
unticked), it checks the first one then doesn't change the others, I don't
get why it doesn't work

M

"Per Jessen" wrote in message
...
Hi

Two things:

In chkHeadAll_Change(), test if chkHeadAll is true, and if it is set all
years true, else do nothing.

In each year change sub, you should only check on the current checkbox:

If Not chkHeadY0.Value Then chkHeadAll.Value = False


See my example below:

Private Sub CheckBox1_change()
If Me.CheckBox1 = True Then
Me.CheckBox2.Value = True
Me.CheckBox3.Value = True
Me.CheckBox4.Value = True
End If
End Sub

Private Sub CheckBox2_change()
If Me.CheckBox2 And Me.CheckBox3 And Me.CheckBox4 Then Me.CheckBox1 = True
If Not Me.CheckBox2 Then Me.CheckBox1 = False
End Sub

Private Sub CheckBox3_change()
If Me.CheckBox2 And Me.CheckBox3 And Me.CheckBox4 Then Me.CheckBox1 = True
If Not Me.CheckBox3 Then Me.CheckBox1 = False
End Sub

Private Sub CheckBox4_change()
If Me.CheckBox2 And Me.CheckBox3 And Me.CheckBox4 Then Me.CheckBox1 = True
If Not Me.CheckBox4 Then Me.CheckBox1 = False
End Sub

Hopes this helps.
..
Per

"Michelle" skrev i meddelelsen
...
Hello, I have a userform with checkboxes for each of four years (Y0, Y1,
Y2 & Y3)
I want to be able to tick them all by ticking the 'tick-all' checkbox

Similarly, if I deselect any of them the 'tick-all' box should be
automatically unchecked
and if I tick them all manually, the 'tick-all' box should be
automatically checked

I have seen this setup before - so I know it's possible.

My problem is that the event procedures are all triggering each other and
so it's a bit of a mess.

I've tried EnableEvents = false, but it doesn't do what I want

A sample of my code is below, but does anyone know a way to make it work?
please?

'===========================
Private Sub chkHeadAll_Change()
Application.EnableEvents = False
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
Application.EnableEvents = True
End Sub

Private Sub chkHeadY0_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value
Or Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub

Private Sub chkHeadY1_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value
Or Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub
'===========================

Thanks


M


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default CheckBox to tick other CheckBoxes
..enableevents won't work (as you've seen). You have to keep track yourself.

Since you named the 4 checkboxes nicely (chkHeadY#), this worked ok for me:

Option Explicit
Dim BlkProc As Boolean
Const MaxCheckBoxes As Long = 4
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub chkHeadAll_Change()

If BlkProc = True Then
Exit Sub
End If

BlkProc = True
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
BlkProc = False
End Sub
Private Sub chkHeadY0_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY1_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY2_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY3_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Function CountIndividualYears() As Long

Dim iCtr As Long
Dim myCount As Long

myCount = 0
For iCtr = 0 To MaxCheckBoxes - 1
If Me.Controls("chkHeadY" & iCtr).Value = True Then
myCount = myCount + 1
End If
Next iCtr

CountIndividualYears = myCount

End Function



Michelle wrote:

Hello, I have a userform with checkboxes for each of four years (Y0, Y1, Y2
& Y3)
I want to be able to tick them all by ticking the 'tick-all' checkbox

Similarly, if I deselect any of them the 'tick-all' box should be
automatically unchecked
and if I tick them all manually, the 'tick-all' box should be automatically
checked

I have seen this setup before - so I know it's possible.

My problem is that the event procedures are all triggering each other and so
it's a bit of a mess.

I've tried EnableEvents = false, but it doesn't do what I want

A sample of my code is below, but does anyone know a way to make it work?
please?

'===========================
Private Sub chkHeadAll_Change()
Application.EnableEvents = False
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
Application.EnableEvents = True
End Sub

Private Sub chkHeadY0_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub

Private Sub chkHeadY1_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub
'===========================

Thanks

M

--

Dave Peterson
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default CheckBox to tick other CheckBoxes
If you decide to change the number of years, you may want to use this:

Private Sub chkHeadAll_Change()

Dim iCtr as long

If BlkProc = True Then
Exit Sub
End If

BlkProc = True
For iCtr = 0 To MaxCheckBoxes - 1
Me.Controls("chkHeadY" & iCtr).Value = chkHeadAll.Value
Next iCtr
BlkProc = false

End Sub

(I didn't notice it before.)



Dave Peterson wrote:

.enableevents won't work (as you've seen). You have to keep track yourself.

Since you named the 4 checkboxes nicely (chkHeadY#), this worked ok for me:

Option Explicit
Dim BlkProc As Boolean
Const MaxCheckBoxes As Long = 4
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub chkHeadAll_Change()

If BlkProc = True Then
Exit Sub
End If

BlkProc = True
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
BlkProc = False
End Sub
Private Sub chkHeadY0_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY1_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY2_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY3_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Function CountIndividualYears() As Long

Dim iCtr As Long
Dim myCount As Long

myCount = 0
For iCtr = 0 To MaxCheckBoxes - 1
If Me.Controls("chkHeadY" & iCtr).Value = True Then
myCount = myCount + 1
End If
Next iCtr

CountIndividualYears = myCount

End Function

Michelle wrote:

Hello, I have a userform with checkboxes for each of four years (Y0, Y1, Y2
& Y3)
I want to be able to tick them all by ticking the 'tick-all' checkbox

Similarly, if I deselect any of them the 'tick-all' box should be
automatically unchecked
and if I tick them all manually, the 'tick-all' box should be automatically
checked

I have seen this setup before - so I know it's possible.

My problem is that the event procedures are all triggering each other and so
it's a bit of a mess.

I've tried EnableEvents = false, but it doesn't do what I want

A sample of my code is below, but does anyone know a way to make it work?
please?

'===========================
Private Sub chkHeadAll_Change()
Application.EnableEvents = False
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
Application.EnableEvents = True
End Sub

Private Sub chkHeadY0_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub

Private Sub chkHeadY1_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub
'===========================

Thanks

M

--

Dave Peterson

--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 204
Default CheckBox to tick other CheckBoxes
Thanks - you're a star.

M

"Dave Peterson" wrote in message
...
If you decide to change the number of years, you may want to use this:

Private Sub chkHeadAll_Change()

Dim iCtr as long

If BlkProc = True Then
Exit Sub
End If

BlkProc = True
For iCtr = 0 To MaxCheckBoxes - 1
Me.Controls("chkHeadY" & iCtr).Value = chkHeadAll.Value
Next iCtr
BlkProc = false

End Sub

(I didn't notice it before.)



Dave Peterson wrote:

.enableevents won't work (as you've seen). You have to keep track
yourself.

Since you named the 4 checkboxes nicely (chkHeadY#), this worked ok for
me:

Option Explicit
Dim BlkProc As Boolean
Const MaxCheckBoxes As Long = 4
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub chkHeadAll_Change()

If BlkProc = True Then
Exit Sub
End If

BlkProc = True
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
BlkProc = False
End Sub
Private Sub chkHeadY0_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY1_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY2_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Private Sub chkHeadY3_Change()

Dim HowManyChecked As Long

If BlkProc = True Then
Exit Sub
End If

HowManyChecked = CountIndividualYears

BlkProc = True
If HowManyChecked = MaxCheckBoxes Then
Me.chkHeadAll = True
Else
Me.chkHeadAll = False
End If
BlkProc = False

End Sub
Function CountIndividualYears() As Long

Dim iCtr As Long
Dim myCount As Long

myCount = 0
For iCtr = 0 To MaxCheckBoxes - 1
If Me.Controls("chkHeadY" & iCtr).Value = True Then
myCount = myCount + 1
End If
Next iCtr

CountIndividualYears = myCount

End Function

Michelle wrote:

Hello, I have a userform with checkboxes for each of four years (Y0,
Y1, Y2
& Y3)
I want to be able to tick them all by ticking the 'tick-all' checkbox

Similarly, if I deselect any of them the 'tick-all' box should be
automatically unchecked
and if I tick them all manually, the 'tick-all' box should be
automatically
checked

I have seen this setup before - so I know it's possible.

My problem is that the event procedures are all triggering each other
and so
it's a bit of a mess.

I've tried EnableEvents = false, but it doesn't do what I want

A sample of my code is below, but does anyone know a way to make it
work?
please?

'===========================
Private Sub chkHeadAll_Change()
Application.EnableEvents = False
chkHeadY0.Value = chkHeadAll.Value
chkHeadY1.Value = chkHeadAll.Value
chkHeadY2.Value = chkHeadAll.Value
chkHeadY3.Value = chkHeadAll.Value
Application.EnableEvents = True
End Sub

Private Sub chkHeadY0_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not
chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub

Private Sub chkHeadY1_Change()
Application.EnableEvents = False
If chkHeadY0.Value And chkHeadY1.Value And chkHeadY2.Value And
chkHeadY3.Value Then chkHeadAll.Value = True
If Not chkHeadY0.Value Or Not chkHeadY1.Value Or Not
chkHeadY2.Value Or
Not chkHeadY3.Value Then chkHeadAll.Value = False
Application.EnableEvents = True
End Sub
'===========================

Thanks

M

--

Dave Peterson

--

Dave Peterson

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro or VBA Code to un tick CheckBox PA Excel Programming 2 June 18th 07 07:09 AM
Alternative to a tick in a checkbox Mike Excel Discussion (Misc queries) 1 April 26th 07 09:38 AM
Macro to tick checkbox automatically PaulJ Excel Discussion (Misc queries) 2 March 2nd 06 10:19 AM
Checkbox to be marked with a cross rather than a tick Den New Users to Excel 1 February 22nd 06 10:04 AM
I would like to have a cross in my checkbox rather than a tick Den Excel Programming 5 December 21st 05 06:50 AM


All times are GMT +1. The time now is 04:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement