Hide checkboxes using VBA based on another checkbox

M

MissingInAction

Board Regular
Joined
Sep 20, 2019
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Hi everyone. I want to hide checkboxes based on the condition of another checkbox. If that box is checked, the other should show, if not checked, then the others should not show.
I have linked the main checkbox to cell G140. When ticking/unticking the checkbox, G140 turns to TRUE/FALSE, as it should.
The problem is the boxes are not showing until I enter a random value in a random cell. I think it has to do with the VBA code not running when a checkbox is checked, but only when the sheet information changes. Here is the code I'm using:
VBA Code: 
    If ActiveSheet.Range("G140").Value = True Then
            ActiveSheet.Shapes("Risk_Auditing").Visible = True
            ActiveSheet.Shapes("Risk_CFO").Visible = True
            ActiveSheet.Shapes("Risk_Committee").Visible = True
            ActiveSheet.Shapes("Risk_Community_Director").Visible = True
            ActiveSheet.Shapes("Risk_Councillors").Visible = True
            ActiveSheet.Shapes("Risk_Emergency").Visible = True
            ActiveSheet.Shapes("Risk_Environment").Visible = True
            ActiveSheet.Shapes("Risk_Expenditure").Visible = True
            ActiveSheet.Shapes("Risk_BTO").Visible = True
            ActiveSheet.Shapes("Risk_Financial").Visible = True
            ActiveSheet.Shapes("Risk_HR").Visible = True
            ActiveSheet.Shapes("Risk_IDP").Visible = True
            ActiveSheet.Shapes("Risk_ICT").Visible = True
            ActiveSheet.Shapes("Risk_LED").Visible = True
            ActiveSheet.Shapes("Risk_Mun_Health").Visible = True
            ActiveSheet.Shapes("Risk_MM").Visible = True
            ActiveSheet.Shapes("Risk_Performance").Visible = True
            ActiveSheet.Shapes("Risk_Revenue").Visible = True
            ActiveSheet.Shapes("Risk_Risk").Visible = True
            ActiveSheet.Shapes("Risk_Roads").Visible = True
            ActiveSheet.Shapes("Risk_SCM").Visible = True
        Else
            ActiveSheet.Shapes("Risk_Auditing").Visible = False
            ActiveSheet.Shapes("Risk_CFO").Visible = False
            ActiveSheet.Shapes("Risk_Committee").Visible = False
            ActiveSheet.Shapes("Risk_Community_Director").Visible = False
            ActiveSheet.Shapes("Risk_Councillors").Visible = False
            ActiveSheet.Shapes("Risk_Emergency").Visible = False
            ActiveSheet.Shapes("Risk_Environment").Visible = False
            ActiveSheet.Shapes("Risk_Expenditure").Visible = False
            ActiveSheet.Shapes("Risk_BTO").Visible = False
            ActiveSheet.Shapes("Risk_Financial").Visible = False
            ActiveSheet.Shapes("Risk_HR").Visible = False
            ActiveSheet.Shapes("Risk_IDP").Visible = False
            ActiveSheet.Shapes("Risk_ICT").Visible = False
            ActiveSheet.Shapes("Risk_LED").Visible = False
            ActiveSheet.Shapes("Risk_Mun_Health").Visible = False
            ActiveSheet.Shapes("Risk_MM").Visible = False
            ActiveSheet.Shapes("Risk_Performance").Visible = False
            ActiveSheet.Shapes("Risk_Revenue").Visible = False
            ActiveSheet.Shapes("Risk_Risk").Visible = False
            ActiveSheet.Shapes("Risk_Roads").Visible = False
            ActiveSheet.Shapes("Risk_SCM").Visible = False
    End If

I have used similar code (only the shape names are different) in other instances where it is linked to a dropdown menu containing "yes" or "no". If yes is selected, the boxes shows perfectly. How can I rectify this?

Thank you
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Put in a random cell: =G140
Use Private Sub Worksheet_Calculate() to trigger checkbox changed.
 
Upvote 0
Hi mart37. I was using Private Sub Worksheet_Change(). I made the changes you suggested, but now I'm getting an error "Compile error: Procedure declaration does not match description of event or procedure having the same name". I suspect something else needs to change as well. Here is the entire macro:

VBA Code: 
Private Sub Worksheet_Calculate(ByVal Target As Range)
    
'   IT Equipment
    If ActiveSheet.Range("E48").Value = "Yes" Or ActiveSheet.Range("E48").Value = "yes" Then
            ActiveSheet.Shapes("Laptop").Visible = True
            ActiveSheet.Shapes("Desktop").Visible = True
            ActiveSheet.Shapes("Other").Visible = True
        Else
            ActiveSheet.Shapes("Laptop").Visible = False
            ActiveSheet.Shapes("Desktop").Visible = False
            ActiveSheet.Shapes("Other").Visible = False
    End If

'   Alarm Code & Building Keys
    If ActiveSheet.Range("B54").Value = "Yes" Or ActiveSheet.Range("B54").Value = "yes" Then
            ActiveSheet.Shapes("Barrydale").Visible = True
            ActiveSheet.Shapes("BredasdorpAnnex").Visible = True
            ActiveSheet.Shapes("BredasdorpFire").Visible = True
            ActiveSheet.Shapes("BredasdorpHeadOffice").Visible = True
            ActiveSheet.Shapes("BredasdorpRoads").Visible = True
            ActiveSheet.Shapes("BredasdorpSCM").Visible = True
            ActiveSheet.Shapes("BredasdorpWorkshop").Visible = True
            ActiveSheet.Shapes("CaledonFire").Visible = True
            ActiveSheet.Shapes("CaledonHealth").Visible = True
            ActiveSheet.Shapes("CaledonRoads").Visible = True
            ActiveSheet.Shapes("DieDam").Visible = True
            ActiveSheet.Shapes("GrabouwFire").Visible = True
            ActiveSheet.Shapes("GrabouwHealth").Visible = True
            ActiveSheet.Shapes("Hermanus").Visible = True
            ActiveSheet.Shapes("Karwyderskraal").Visible = True
            ActiveSheet.Shapes("Kleinmond").Visible = True
            ActiveSheet.Shapes("Struisbaai").Visible = True
            ActiveSheet.Shapes("SwellendamFire").Visible = True
            ActiveSheet.Shapes("SwellendamHealth").Visible = True
            ActiveSheet.Shapes("SwellendamRoads").Visible = True
            ActiveSheet.Shapes("Uilenkraalsmond").Visible = True
            ActiveSheet.Shapes("Villiersdorp").Visible = True
        Else
            ActiveSheet.Shapes("Barrydale").Visible = False
            ActiveSheet.Shapes("BredasdorpAnnex").Visible = False
            ActiveSheet.Shapes("BredasdorpFire").Visible = False
            ActiveSheet.Shapes("BredasdorpHeadOffice").Visible = False
            ActiveSheet.Shapes("BredasdorpRoads").Visible = False
            ActiveSheet.Shapes("BredasdorpSCM").Visible = False
            ActiveSheet.Shapes("BredasdorpWorkshop").Visible = False
            ActiveSheet.Shapes("CaledonFire").Visible = False
            ActiveSheet.Shapes("CaledonHealth").Visible = False
            ActiveSheet.Shapes("CaledonRoads").Visible = False
            ActiveSheet.Shapes("DieDam").Visible = False
            ActiveSheet.Shapes("GrabouwFire").Visible = False
            ActiveSheet.Shapes("GrabouwHealth").Visible = False
            ActiveSheet.Shapes("Hermanus").Visible = False
            ActiveSheet.Shapes("Karwyderskraal").Visible = False
            ActiveSheet.Shapes("Kleinmond").Visible = False
            ActiveSheet.Shapes("Struisbaai").Visible = False
            ActiveSheet.Shapes("SwellendamFire").Visible = False
            ActiveSheet.Shapes("SwellendamHealth").Visible = False
            ActiveSheet.Shapes("SwellendamRoads").Visible = False
            ActiveSheet.Shapes("Uilenkraalsmond").Visible = False
            ActiveSheet.Shapes("Villiersdorp").Visible = False
    End If

'   Eunomia
    If ActiveSheet.Range("D136").Value = "Yes" Or ActiveSheet.Range("D136").Value = "yes" Then
            ActiveSheet.Shapes("Eunomia_Action_Owner").Visible = True
            ActiveSheet.Shapes("Eunomia_Approver").Visible = True
            ActiveSheet.Shapes("Eunomia_Administrator").Visible = True
            ActiveSheet.Shapes("Eunomia_Assist_Administrator").Visible = True
        Else
            ActiveSheet.Shapes("Eunomia_Action_Owner").Visible = False
            ActiveSheet.Shapes("Eunomia_Approver").Visible = False
            ActiveSheet.Shapes("Eunomia_Administrator").Visible = False
            ActiveSheet.Shapes("Eunomia_Assist_Administrator").Visible = False
    End If

'   Risk Management
    If ActiveSheet.Range("B136").Value = "Yes" Or ActiveSheet.Range("B136").Value = "yes" Then
            ActiveSheet.Shapes("Risk_Administrator").Visible = True
            ActiveSheet.Shapes("Risk_Assist_Administrator").Visible = True
            ActiveSheet.Shapes("Risk_Risk_Owner").Visible = True
            ActiveSheet.Shapes("Risk_Action_Owner").Visible = True
        Else
            ActiveSheet.Shapes("Risk_Administrator").Visible = False
            ActiveSheet.Shapes("Risk_Assist_Administrator").Visible = False
            ActiveSheet.Shapes("Risk_Risk_Owner").Visible = False
            ActiveSheet.Shapes("Risk_Action_Owner").Visible = False
    End If

'   Risk Management - Action Owner
    If ActiveSheet.Range("G140").Value = True Then
            ActiveSheet.Shapes("Risk_Auditing").Visible = True
            ActiveSheet.Shapes("Risk_CFO").Visible = True
            ActiveSheet.Shapes("Risk_Committee").Visible = True
            ActiveSheet.Shapes("Risk_Community_Director").Visible = True
            ActiveSheet.Shapes("Risk_Councillors").Visible = True
            ActiveSheet.Shapes("Risk_Emergency").Visible = True
            ActiveSheet.Shapes("Risk_Environment").Visible = True
            ActiveSheet.Shapes("Risk_Expenditure").Visible = True
            ActiveSheet.Shapes("Risk_BTO").Visible = True
            ActiveSheet.Shapes("Risk_Financial").Visible = True
            ActiveSheet.Shapes("Risk_HR").Visible = True
            ActiveSheet.Shapes("Risk_IDP").Visible = True
            ActiveSheet.Shapes("Risk_ICT").Visible = True
            ActiveSheet.Shapes("Risk_LED").Visible = True
            ActiveSheet.Shapes("Risk_Mun_Health").Visible = True
            ActiveSheet.Shapes("Risk_MM").Visible = True
            ActiveSheet.Shapes("Risk_Performance").Visible = True
            ActiveSheet.Shapes("Risk_Revenue").Visible = True
            ActiveSheet.Shapes("Risk_Risk").Visible = True
            ActiveSheet.Shapes("Risk_Roads").Visible = True
            ActiveSheet.Shapes("Risk_SCM").Visible = True
        Else
            ActiveSheet.Shapes("Risk_Auditing").Visible = False
            ActiveSheet.Shapes("Risk_CFO").Visible = False
            ActiveSheet.Shapes("Risk_Committee").Visible = False
            ActiveSheet.Shapes("Risk_Community_Director").Visible = False
            ActiveSheet.Shapes("Risk_Councillors").Visible = False
            ActiveSheet.Shapes("Risk_Emergency").Visible = False
            ActiveSheet.Shapes("Risk_Environment").Visible = False
            ActiveSheet.Shapes("Risk_Expenditure").Visible = False
            ActiveSheet.Shapes("Risk_BTO").Visible = False
            ActiveSheet.Shapes("Risk_Financial").Visible = False
            ActiveSheet.Shapes("Risk_HR").Visible = False
            ActiveSheet.Shapes("Risk_IDP").Visible = False
            ActiveSheet.Shapes("Risk_ICT").Visible = False
            ActiveSheet.Shapes("Risk_LED").Visible = False
            ActiveSheet.Shapes("Risk_Mun_Health").Visible = False
            ActiveSheet.Shapes("Risk_MM").Visible = False
            ActiveSheet.Shapes("Risk_Performance").Visible = False
            ActiveSheet.Shapes("Risk_Revenue").Visible = False
            ActiveSheet.Shapes("Risk_Risk").Visible = False
            ActiveSheet.Shapes("Risk_Roads").Visible = False
            ActiveSheet.Shapes("Risk_SCM").Visible = False
    End If

'   SDBIP
    If ActiveSheet.Range("B136").Value = "Yes" Or ActiveSheet.Range("B136").Value = "yes" Then
            ActiveSheet.Shapes("SDBIP_Administrator").Visible = True
            ActiveSheet.Shapes("SDBIP_Assist_Administrator").Visible = True
            ActiveSheet.Shapes("SDBIP_HOD").Visible = True
            ActiveSheet.Shapes("SDBIP_KPI_Owner").Visible = True
        Else
            ActiveSheet.Shapes("SDBIP_Administrator").Visible = False
            ActiveSheet.Shapes("SDBIP_Assist_Administrator").Visible = False
            ActiveSheet.Shapes("SDBIP_HOD").Visible = False
            ActiveSheet.Shapes("SDBIP_KPI_Owner").Visible = False
    End If

'   SDBIP - KPI Owner
    If ActiveSheet.Range("G153").Value = True Then
            ActiveSheet.Shapes("SDBIP_Auditing").Visible = True
            ActiveSheet.Shapes("SDBIP_CFO").Visible = True
            ActiveSheet.Shapes("SDBIP_Committee").Visible = True
            ActiveSheet.Shapes("SDBIP_Community_Director").Visible = True
            ActiveSheet.Shapes("SDBIP_Councillors").Visible = True
            ActiveSheet.Shapes("SDBIP_Emergency").Visible = True
            ActiveSheet.Shapes("SDBIP_Environment").Visible = True
            ActiveSheet.Shapes("SDBIP_Expenditure").Visible = True
            ActiveSheet.Shapes("SDBIP_BTO").Visible = True
            ActiveSheet.Shapes("SDBIP_Financial").Visible = True
            ActiveSheet.Shapes("SDBIP_HR").Visible = True
            ActiveSheet.Shapes("SDBIP_IDP").Visible = True
            ActiveSheet.Shapes("SDBIP_ICT").Visible = True
            ActiveSheet.Shapes("SDBIP_LED").Visible = True
            ActiveSheet.Shapes("SDBIP_Mun_Health").Visible = True
            ActiveSheet.Shapes("SDBIP_MM").Visible = True
            ActiveSheet.Shapes("SDBIP_Performance").Visible = True
            ActiveSheet.Shapes("SDBIP_Revenue").Visible = True
            ActiveSheet.Shapes("SDBIP_Risk").Visible = True
            ActiveSheet.Shapes("SDBIP_Roads").Visible = True
            ActiveSheet.Shapes("SDBIP_SCM").Visible = True
        Else
            ActiveSheet.Shapes("SDBIP_Auditing").Visible = False
            ActiveSheet.Shapes("SDBIP_CFO").Visible = False
            ActiveSheet.Shapes("SDBIP_Committee").Visible = False
            ActiveSheet.Shapes("SDBIP_Community_Director").Visible = False
            ActiveSheet.Shapes("SDBIP_Councillors").Visible = False
            ActiveSheet.Shapes("SDBIP_Emergency").Visible = False
            ActiveSheet.Shapes("SDBIP_Environment").Visible = False
            ActiveSheet.Shapes("SDBIP_Expenditure").Visible = False
            ActiveSheet.Shapes("SDBIP_BTO").Visible = False
            ActiveSheet.Shapes("SDBIP_Financial").Visible = False
            ActiveSheet.Shapes("SDBIP_HR").Visible = False
            ActiveSheet.Shapes("SDBIP_IDP").Visible = False
            ActiveSheet.Shapes("SDBIP_ICT").Visible = False
            ActiveSheet.Shapes("SDBIP_LED").Visible = False
            ActiveSheet.Shapes("SDBIP_Mun_Health").Visible = False
            ActiveSheet.Shapes("SDBIP_MM").Visible = False
            ActiveSheet.Shapes("SDBIP_Performance").Visible = False
            ActiveSheet.Shapes("SDBIP_Revenue").Visible = False
            ActiveSheet.Shapes("SDBIP_Risk").Visible = False
            ActiveSheet.Shapes("SDBIP_Roads").Visible = False
            ActiveSheet.Shapes("SDBIP_SCM").Visible = False
    End If
    
End Sub
 
Upvote 0
Private Sub Worksheet_Calculate(ByVal Target As Range) --> Private Sub Worksheet_Calculate()
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA issue with adding formulas and locking cells
4 5 6
Replies
52
Views
5K
MissingInAction
M

Forum statistics

Threads
1,224,813
Messages
6,181,117
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top