VBA Help! How to enable toggle button in a protected sheet

xlingg

New Member
Joined
Jul 21, 2020
Messages
5
Platform
  1. Windows
Hi there, I am a really new to VBA and I'm trying to insert a toggle button to hide/unhide columns but it has to be protected.

The code that I found protect/unprotect upon clicking the button to hide/unhide which is not what I want.

I need the toggle button to just hide/unhide in a protected sheet which cannot be unprotected by other users using the file.

Below is the full code:

VBA Code:
'Name macro
Sub HideColumn()

ActiveSheet.Unprotect Password:="123"

'Check if column F is hidden
If Range("A:CC").EntireColumn.Hidden = True Then

 
    'Show column F
    Range("A:D").EntireColumn.Hidden = False
    Range("F:G").EntireColumn.Hidden = False
    Range("J:K").EntireColumn.Hidden = False
    Range("M:q").EntireColumn.Hidden = False
    Range("s:u").EntireColumn.Hidden = False
    Range("w:y").EntireColumn.Hidden = False
    Range("AA:AD").EntireColumn.Hidden = False
    Range("Ag:Ai").EntireColumn.Hidden = False
    Range("Ak:Ak").EntireColumn.Hidden = False
    Range("Am:Aq").EntireColumn.Hidden = False
    Range("Ax:bi").EntireColumn.Hidden = False
    Range("bk:bl").EntireColumn.Hidden = False
    Range("bn:bo").EntireColumn.Hidden = False
    Range("bq:br").EntireColumn.Hidden = False
    
 
'Continue here if column F is visible
Else
 
    'Hide column F
    Range("A:D").EntireColumn.Hidden = True
    Range("F:G").EntireColumn.Hidden = True
    Range("J:K").EntireColumn.Hidden = True
    Range("M:q").EntireColumn.Hidden = True
    Range("s:u").EntireColumn.Hidden = True
    Range("w:y").EntireColumn.Hidden = True
    Range("AA:AD").EntireColumn.Hidden = True
    Range("Ag:Ai").EntireColumn.Hidden = True
    Range("Ak:Ak").EntireColumn.Hidden = True
    Range("Am:Aq").EntireColumn.Hidden = True
    Range("Ax:bi").EntireColumn.Hidden = True
    Range("bk:bl").EntireColumn.Hidden = True
    Range("bn:bo").EntireColumn.Hidden = True
    Range("bq:br").EntireColumn.Hidden = True
    
ActiveSheet.Protect Password:="123"

End If

End Sub

Appreciate any help please! Thank you! :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,165
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,951
Members
410,713
Latest member
TaremyLunsil
Top