How to hide and unhide sheets with the push of a button?

Question

I have an Excel workbook that contains information on three sheets.

Sheet 2 (called Grund) should be visible to those who succesfully push a password protected button (called "Admin"). When pressing the button again it should instead hide the sheet. This button is located on sheet 1 (called Hela).

Information on sheet 1, in certain columns (BC:BI), is to be hidden and unhidden in the same way through the password protected button.

I set up the button to use the call function to start my macros.
I managed to get the columns to hide and unhide as intended (see code below), but I can't find a way to hide and unhide the sheet in the same way as the columns.

In the code below the worksheet is protected so I tried to work around that, and need the macro for the sheets to be setup the same way I suppose.

I've tried different variation of "xlhidden" and ".hidden=not" and different "if","ifs" etc.

This is the macro to hide and unhide the columns

Sub hideunhidecol()
Dim pwd As String

pwd = "admin"
'
ActiveSheet.Unprotect Password:=pwd 'unprotect the sheet'

Columns("BC:BI").Hidden = Not Columns("BC:BI").Hidden

With ActiveSheet
    .Protect Password:=pwd, DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True
    .EnableSelection = xlNoRestrictions
    'protect the sheet'
End With
End Sub

What code did you use to hide/unhide the sheets? Put it in a workbook module, and don't use ActiveSheet, but rather the name of the sheet you intend. — BruceWayne
Side note, you should be aware that anything done in Excel is absolutely not secure. If somebody wants to unhide your sheet, they can, regardless of workbook/worksheet protections. — Josh Eller
Try maybe activesheet.visible=xlSheetHidden activesheet.visible=xlsSheetVisible and so on. — barneyos

cybernetic.nomad cybernetic.nomad · Accepted Answer · 2019-11-05T16:28:10

To toggle the visiblity of a given sheet you can use:

Worksheets("Grund").Visible = Not Worksheets("Grund").Visible

But do bear in mind that, as @Josh Eller says, this provides no real protection whatsoever

How to hide and unhide sheets with the push of a button?

1 Answers