Excel VBA checkbox to hide all and change state of other checkboxes

Hi Experts

I have a simple worksheet where 3 checkboxes hide or unhide 3 named range rows called "overseas", "bonds" & "property". This works OK although my VBA is dumb and doesn't know the state of hidden or not.  It just swaps the state.

I have now added another checkbox which hides or shows all the ranges.  How do I change this so that if "ALL" is unticked, the state of the other 'sub' checkboxes changes too.

Eg if
"oversea" is visible and checkbox ticked
"bonds" is hidden and checkbox unticked
"property" is hidden and checkbox unticked

...and I would want "ALL" to be unticked.  Then if I tick "ALL", both bonds and property would unhide, and their checkboxes would change state to ticked. Likewise if I then unticked bonds, the ALL would change state to unticked.

Is this possible?

Thanks

Will



Sub ViewOVERSEAS()
 If ActiveSheet.Range("overseas").EntireRow.Hidden = False Then
 ActiveSheet.Range("overseas").EntireRow.Hidden = True
Else
 ActiveSheet.Range("overseas").EntireRow.Hidden = False
End If
End Sub

Sub ViewBONDS()
 If ActiveSheet.Range("bonds").EntireRow.Hidden = False Then
 ActiveSheet.Range("bonds").EntireRow.Hidden = True
Else
 ActiveSheet.Range("bonds").EntireRow.Hidden = False
End If
End Sub

Sub ViewPROPERTY()
 If ActiveSheet.Range("property").EntireRow.Hidden = False Then
 ActiveSheet.Range("property").EntireRow.Hidden = True
Else
 ActiveSheet.Range("property").EntireRow.Hidden = False
End If
End Sub

Sub ALL()
 If ActiveSheet.Range("overseas,bonds,property").EntireRow.Hidden = False Then
 ActiveSheet.Range("overseas,bonds,property").EntireRow.Hidden = True
Else
 ActiveSheet.Range("overseas,bonds,property").EntireRow.Hidden = False
End If
End Sub

Open in new window

willnjenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris BottomleySoftware Quality Lead EngineerCommented:
Try the following ... though without a file I have no idea what the names are of your checkboxes.  If you need help with that change then a file will help.

Chris
Sub ViewOVERSEAS()
 ActiveSheet.Range("overseas").EntireRow.Hidden = not (activesheet.checkbox1.value = true)
End Sub

Sub ViewBONDS()
 ActiveSheet.Range("bonds").EntireRow.Hidden = not (activesheet.checkbox2.value = true)
End Sub

Sub ViewPROPERTY()
 ActiveSheet.Range("property").EntireRow.Hidden = not (activesheet.checkbox3.value = true)
End Sub

Sub ALL()
 ActiveSheet.Range("overseas").EntireRow.Hidden = not (activesheet.checkbox4.value = true)
 activesheet.checkbox1 = activesheet.checkbox4.value = true
 ActiveSheet.Range("bonds").EntireRow.Hidden = not (activesheet.checkbox4.value = true)
 activesheet.checkbox2 = activesheet.checkbox4.value = true
 ActiveSheet.Range("property").EntireRow.Hidden = not (activesheet.checkbox4.value = true)
 activesheet.checkbox3 = activesheet.checkbox4.value = true
End Sub

Open in new window

0
VipulKadiaCommented:
Here is the solution :
Here 'Check Box 4' is 'Check All'.

Sub CheckAll()
    If Sheet1.Shapes("Check Box 4").OLEFormat.Object.Value = 1 Then
        Sheet1.Shapes("Check Box 1").OLEFormat.Object.Value = 1
        Sheet1.Shapes("Check Box 3").OLEFormat.Object.Value = 1
    Else
        Sheet1.Shapes("Check Box 1").OLEFormat.Object.Value = 0
        Sheet1.Shapes("Check Box 3").OLEFormat.Object.Value = 0
    End If
End Sub

Also see attached excel file and coding in Module1. CheckAll.xlsm CheckAll.xlsm
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Added a bit I missed ... setting checkbox 4 according to the overall state of 1 ... 3.

Chris
Sub ViewOVERSEAS()
 ActiveSheet.Range("overseas").EntireRow.Hidden = not (activesheet.checkbox1.value = true)
 activesheet.checkbox4.value = (activesheet.checkbox1.value and activesheet.checkbox2.value and activesheet.checkbox3.value)
End Sub

Sub ViewBONDS()
 ActiveSheet.Range("bonds").EntireRow.Hidden = not (activesheet.checkbox2.value = true)
 activesheet.checkbox4.value = (activesheet.checkbox1.value and activesheet.checkbox2.value and activesheet.checkbox3.value)
End Sub

Sub ViewPROPERTY()
 ActiveSheet.Range("property").EntireRow.Hidden = not (activesheet.checkbox3.value = true)
 activesheet.checkbox4.value = (activesheet.checkbox1.value and activesheet.checkbox2.value and activesheet.checkbox3.value)
End Sub

Sub ALL()
 ActiveSheet.Range("overseas").EntireRow.Hidden = not (activesheet.checkbox4.value = true)
 activesheet.checkbox1 = activesheet.checkbox4.value = true
 ActiveSheet.Range("bonds").EntireRow.Hidden = not (activesheet.checkbox4.value = true)
 activesheet.checkbox2 = activesheet.checkbox4.value = true
 ActiveSheet.Range("property").EntireRow.Hidden = not (activesheet.checkbox4.value = true)
 activesheet.checkbox3 = activesheet.checkbox4.value = true
End Sub

Open in new window

0
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

willnjenAuthor Commented:
Hi Chris

Thanks for your contribution and sorry I haven't replied sooner (it was night time in New Zealand).

You code doesn't quite behave properly and I can't figure out how to fix it.

Clicking either overseas, bonds or property hides and unhides the named range OK.  Once all three are ticked the ALL becomes ticked OK.  But if I then untick any of the three, all become unticked.  

Also the ALL button also doesn't unhide all if all are hidden.  What happens is if all are unticked the tick ALL doesn't work.  Tick one of the first three and still the tick ALL doesn't work, then tick a second so that two are ticked, then the ALL will work.

Sorry if that sounds complicated.  Have a look at my simple demo using your code.

Hope you can help.

Will
example.xls
0
Chris BottomleySoftware Quality Lead EngineerCommented:
Ah, inadequate testing!

Fixed the bug below I hope!, module 2 code as below, sheet unchanged so not supplied

Chris
Sub ViewOVERSEAS()
 ActiveSheet.Range("overseas").EntireRow.Hidden = Not (ActiveSheet.CheckBox1.Value = True)
 If ActiveSheet.CheckBox1.Value And ActiveSheet.CheckBox2.Value And ActiveSheet.CheckBox3.Value Then
     ActiveSheet.CheckBox4.Value = True
 ElseIf Not ActiveSheet.CheckBox1.Value And Not ActiveSheet.CheckBox2.Value And Not ActiveSheet.CheckBox3.Value Then
     ActiveSheet.CheckBox4.Value = False
 End If
End Sub

Sub ViewBONDS()
 ActiveSheet.Range("bonds").EntireRow.Hidden = Not (ActiveSheet.CheckBox2.Value = True)
 If ActiveSheet.CheckBox1.Value And ActiveSheet.CheckBox2.Value And ActiveSheet.CheckBox3.Value Then
     ActiveSheet.CheckBox4.Value = True
 ElseIf Not ActiveSheet.CheckBox1.Value And Not ActiveSheet.CheckBox2.Value And Not ActiveSheet.CheckBox3.Value Then
     ActiveSheet.CheckBox4.Value = False
 End If
End Sub

Sub ViewPROPERTY()
 ActiveSheet.Range("property").EntireRow.Hidden = Not (ActiveSheet.CheckBox3.Value = True)
 If ActiveSheet.CheckBox1.Value And ActiveSheet.CheckBox2.Value And ActiveSheet.CheckBox3.Value Then
     ActiveSheet.CheckBox4.Value = True
 ElseIf Not ActiveSheet.CheckBox1.Value And Not ActiveSheet.CheckBox2.Value And Not ActiveSheet.CheckBox3.Value Then
     ActiveSheet.CheckBox4.Value = False
 End If
End Sub

Sub ALL()
 ActiveSheet.Range("overseas").EntireRow.Hidden = Not (ActiveSheet.CheckBox4.Value = True)
 ActiveSheet.CheckBox1 = ActiveSheet.CheckBox4.Value = True
 ActiveSheet.Range("bonds").EntireRow.Hidden = Not (ActiveSheet.CheckBox4.Value = True)
 ActiveSheet.CheckBox2 = ActiveSheet.CheckBox4.Value = True
 ActiveSheet.Range("property").EntireRow.Hidden = Not (ActiveSheet.CheckBox4.Value = True)
 ActiveSheet.CheckBox3 = ActiveSheet.CheckBox4.Value = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
willnjenAuthor Commented:
Thanks so much for your help!!  Reading your code I now understand a bit more about coding.

Thanks!

Will
0
willnjenAuthor Commented:
Hi Chris

Sorry to bother you again but need a wee clarification.  

The Sub ALL() has developed into the code below.  It's the same, just larger and I've given things clearer names.

Is there any reason in the code why Application ScreenUpdating = False doesn't seem to work.  I can't stop the screen updating when this code runs so the screen flickers as ranges hide or unhide.  Tested screen updating with other macro OK so it's something to do with this code.

Again, sorry to bug you again.

Will


Private Sub CheckBoxall_Click()
Application.ScreenUpdating = False

ActiveSheet.Range("nz_cash").EntireRow.Hidden = Not (ActiveSheet.CheckBoxall.Value = True)
 ActiveSheet.CheckBoxNZcash = ActiveSheet.CheckBoxall.Value = True

ActiveSheet.Range("nz_equities").EntireRow.Hidden = Not (ActiveSheet.CheckBoxall.Value = True)
 ActiveSheet.CheckBoxNZequities = ActiveSheet.CheckBoxall.Value = True

ActiveSheet.Range("au_equities").EntireRow.Hidden = Not (ActiveSheet.CheckBoxall.Value = True)
 ActiveSheet.CheckBoxAUequities = ActiveSheet.CheckBoxall.Value = True
 
 ActiveSheet.Range("overseas").EntireRow.Hidden = Not (ActiveSheet.CheckBoxall.Value = True)
 ActiveSheet.CheckBoxoverseas = ActiveSheet.CheckBoxall.Value = True
 
 ActiveSheet.Range("bonds").EntireRow.Hidden = Not (ActiveSheet.CheckBoxall.Value = True)
 ActiveSheet.CheckBoxbonds = ActiveSheet.CheckBoxall.Value = True
 
 ActiveSheet.Range("property").EntireRow.Hidden = Not (ActiveSheet.CheckBoxall.Value = True)
 ActiveSheet.CheckBoxproperty = ActiveSheet.CheckBoxall.Value = True
 
  Application.ScreenUpdating = True
End Sub
0
Chris BottomleySoftware Quality Lead EngineerCommented:
If your other unitary subs, i.e. CheckBox1_Click or its sub set and unset updating then you will need to repeat the 'disable' after each checkbox change event in the 'all' script ... or delete it from the subsidiary scripts ... which would be mu suggestion.

Whatever was there in that regard in my code was of course development and therefore reflected different stages of testing.

Chris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.