Ok, try this.
ThisWorkbook
ModulePrivate Sub Workbook_Open() HideUnhide True End Sub
Public Sub HideUnhide(Optional LockSheet As Boolean) 'LockSheet = true forces sheet(s) to be locked. Use on workbook open. 'LockSheet not specified or false; sheet(s) locked based on button caption. Dim shp As Object: Set shp = Sheets("Product").Shapes("Button 1").DrawingObject If Not LockSheet And shp.Caption = "Unhide" Then If Unhide(InputBox("Enter password"), "London", "NY", "Paris") Then shp.Caption = "Hide" End If Else If Hide("Plovdiv", "London", "NY", "Paris") Then shp.Caption = "Unhide" End If End If End Sub Private Function Hide(pw As String, ParamArray sheetname() As Variant) As Boolean On Error Resume Next For Each sht In sheetname With Sheets(sht) Select Case Err.Number Case 0 .Range( _ "4:4,5:5,10:10,11:11,13:13,14:14,17:17,18:18,34:34,35:35,38:38," & _ "45:45,46:46,49:49,50:50,72:72,73:73,76:76,77:77,78:78,79:79" _ ).EntireRow.hidden = True Select Case Err.Number Case 0, 1004 'already hidden? Case Else: Debug.Print Err.Number, Err.Description End Select Err.Clear .Protect pw, DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFiltering:=True Select Case Err.Number Case Is <> 0: Debug.Print Err.Description End Select Err.Clear Case Else MsgBox "Sheet " & sht & " not found!" Err.Clear End Select End With Next Hide = True End Function Private Function Unhide(pw As String, ParamArray sheetname() As Variant) As Boolean On Error Resume Next For Each sht In sheetname With Sheets(sht) Select Case Err.Number Case 0 .Unprotect Password:=pw .Cells.EntireRow.hidden = False If Err.Number <> 0 Then MsgBox "Wrong password", vbExclamation Exit Function End If Case Else MsgBox "Sheet " & sht & " not found!" Err.Clear End Select End With Next Unhide = True End Function




Reply With Quote