Forum Discussion

subhashgc's avatar
subhashgc
Copper Contributor
May 11, 2019

How to hide and unhide selected columns and worksheets sametime using one password in VBA code?

I have 4 worksheets in a workbook. 

Worksheets 1 & 2 are not hidden and worksheets 3 & 4 are veryhidden.

In worksheet 2, columns E to H are hidden.

 

How do I write a VBA code to unhide the selected columns (from E to H) from worksheet 2 and veryhidden worksheets 3 & 4, using a password?

 

Note: These columns and worksheets should not open by any other method.

excel
Macros and VBA

1 Reply

Sort By
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 12, 2019

    subhashgc 

    I use a cell and then use the worksheet change event to any change that the user may make to its contents.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim status As String
    Dim character As String

    Set rng = Range("A1")
    Set Target = Target.Cells(1, 1)

    If Not Intersect(Target, rng) Is Nothing Then
    Application.EnableEvents = False
    Application.ScreenUpdating = False

    status = rng.Value
    rng.Value = checkStatus(status)
    rng.Select

    Application.EnableEvents = True
    Application.ScreenUpdating = True

    End If

    End Sub

     

     

    That takes me to a regular module where I test for a valid password (which may differ from the actual worksheet protection password).

     

    Function checkStatus(ByVal pwd As String) As String
    Dim wkb As Workbook

    checkStatus = pwd
    If pwd = "userpassword" Then bUnprotect = True
    pwd = "actualpassword"

    Set wkb = ThisWorkbook
    If Not bUnprotect Then
    Call unProtectWorkbookAndSheets(pwd)
    Call protectAllSheets(pwd)
    wkb.Protect Password:=pwd, Structure:=True, Windows:=False
    'Range("home.cell") = vbNullString
    ActiveWindow.DisplayWorkbookTabs = False
    Else
    Call unProtectWorkbookAndSheets(pwd)
    checkStatus = "open"
    ActiveWindow.DisplayWorkbookTabs = True
    End If

    End Function

     

    I seem to remember the catch being that even to change the status of a protected sheet, I have to unprotect it first.  The names of the sheets requiring regular protection or 'Very Hidden' status are held as a global constants

    Const strProtectedSheets = "Contents,Section1" 'add to this list to protect other tabs
    Const strHiddenSheets = "Labels,RefValues"

     

    The protection involves statements such as

    vProtected = Split(strHiddenSheets, ",")
    For i = LBound(vProtected) To UBound(vProtected)
    With wkb.Worksheets(vProtected(i))
    .Protect Password:=pwd, DrawingObjects:=False, contents:=True, userInterfaceOnly:=True
    .EnableSelection = xlUnlockedCells 'xlNoRestrictions
    .Visible = xlSheetVeryHidden
    End With
    Next i

     

    Unprotection is simpler

    Set wkb = ThisWorkbook
    wkb.Unprotect Password:=pwd
    For Each wks In wkb.Sheets
    wks.Unprotect Password:=pwd
    wks.Visible = xlSheetVisible
    Next wks

     

    This is code picked out of a very old workbook which may or may not be functional but some of the ideas should carry over.

Resources