Excel Forum

Use name manager name as sheet password

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

May 25, 2022 - 1:19 pm

I want to protect the content and objects on the sheets in my workbook, but I need my VBA to be able to manipulate them. I thought maybe I could add a named range in the Workbook_Open() procedure and then refer to the value in that range when I need to know what the password is. The problem is that the procedures run but the password is not set when re-protecting the sheets.

Something like this in the "ThisWorkbook" object:

Private Sub Workbook_Open()

Dim ws As Worksheet
' Create a named range called "pswd"
' Assign the value "TrainingHub" to the range
ThisWorkbook.Names.Add "pswd", "TrainingHub"

msgbox "The password is: " & [pswd]

' Now protect every sheet with the password
For Each ws In ThisWorkbook.Worksheets
Debug.Print "Protecting sheet " & ws.Name & " with password " & [pswd]
ws.Protect Password:=[pswd]

' Not sure if I need this or not. And if so, not sure how to use it!
' ws.Protect UserInterFaceOnly:=True
Next ws

End Sub

Sub DoSomething

  ' Unprotect sheet before action
  If ProtectSheet (False) = True then
    debug.print "Unprotect sheet SUCCESS"
  Else
   debug.print "Unprotect sheet FAILED"
  Endif

  ' Perform some action here, such as adding new slicers (this is why I need to unprotect the sheet)

 ' Protect the sheet before exiting
  If ProtectSheet (True) = True then
    debug.print "Protect sheet SUCCESS"
  Else
    debug.print "Protect sheet FAILED"
  Endif

End Sub

Function ProtectSheet (byval bSwitch as boolean) as boolean

  ProtectSheet = False

  If bSwitch = TRUE then ' Protect

    ActiveSheet.Protect Password:=[pswd], _
      DrawingObjects:=True, _
      Contents:=True, _
      Scenarios:=True
    ProtectSheet = True

  Else

    ActiveSheet.unprotect password:=[pswd]
    ProtectSheet = True

  Endif

End Function

See Answer

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 627

Member Since:
November 1, 2018

Online

May 25, 2022 - 5:12 pm

That code works fine here.

Just as a side note, there is no need to compare a boolean to a boolean to get the same boolean, so you can just use If bSwitch Then rather than

If bSwitch = TRUE then

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

May 26, 2022 - 8:39 am

I got the code to work except for one issue...

Sub ProtectThisSheet()

  If Not AddProtection(ActiveSheet.Index) Then
    MsgBox "Failed to protect sheet"
  Else
    MsgBox "Protected the sheet"
  End If

End Sub

Function AddProtection(ByVal iSheetIndex As Integer) As Boolean

  AddProtection = False

  MsgBox "START -- The sheet index passed is " & iSheetIndex & vbNewLine & "Protected = " & Sheets(iSheetIndex).ProtectContents

  ' Hard code password for the training hub forum
  Dim strPswd As String
  strPswd = "pass123"  

  On Error Resume Next

  ' This simple line of code works. The sheet gets protected with the password. The msgbox at the end proves it.
  Sheets(iSheetIndex).Protect Password:=strPswd

  ' This more complex code FAILS. The sheet does not get protected with any password. The msgbox at the end proves it.
  ' I am wondering if some property I'm setting is somehow negating the password property
  ' I want the sheet protected with a password and I need the pivot tables to work, but I want to apply these other restrictions

  Sheets(iSheetIndex).Protect Password:=strPswd, _
    AllowUsingPivotTables:=True, _
    DrawingObjects:=False, _
    Contents:=False, _
    Scenarios:=False, _
    AllowFormattingCells:=False, _
    AllowFormattingColumns:=False, _
    AllowFormattingRows:=False, _
    AllowInsertingColumns:=False, _
    AllowInsertingRows:=False, _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=False, _
    AllowDeletingRows:=False, _
    AllowSorting:=False, _
    AllowFiltering:=False

  ' Check whether we were successful
  If Err.Number = 0 Then
    AddProtection = True
    Debug.Print "Protected sheet: " & Sheets(iSheetIndex).Name

  Else
    Debug.Print "Failed to protect sheet: " & Sheets(iSheetIndex).Name
    MsgBox "Failed to protect sheet: " & Sheets(iSheetIndex).Name & _
      vbNewLine & "Protection Status = " & UCase(Sheets(iSheetIndex).ProtectContents)

  End If

  MsgBox "END -- The sheet index passed is " & iSheetIndex & vbNewLine & "Protected = " & Sheets(iSheetIndex).ProtectContents

End Function

Thanks!

Velouria

London or thereabouts

Moderator

Members

Trusted Members

Moderators

Forum Posts: 627

Member Since:
November 1, 2018

Online

May 26, 2022 - 6:31 pm

It works for me but your message box is misleading since it relies on the ProtectContents property, which you set to False in your protection.