Hide checkboxes using VBA based on another checkbox

MissingInAction

Board Regular
Joined
Sep 20, 2019
Messages
62
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
Put in a random cell: =G140
Use Private Sub Worksheet_Calculate() to trigger checkbox changed.
 

MissingInAction

Board Regular
Joined
Sep 20, 2019
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,061
Office Version
  1. 2016
Platform
  1. Windows
Private Sub Worksheet_Calculate(ByVal Target As Range) --> Private Sub Worksheet_Calculate()
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,611
Messages
5,625,833
Members
416,138
Latest member
Pizzaman22

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
Top