Lawrence
07-01-2008, 02:25 PM
I have a workbook containing a lot of data tabs and output tabs. Before sending it out, I manually hide all the data tabs and then use a CommandButton to protect/unprotect the sheets (see code below). The problem is that the next day, I have to unhide all the data tabs one by one.
What I would like to do is to have a similar button to Hide/Unhide the data sheets and I don't really understand how this function works. I would like for this CommandButton to appear when the sheets are unprotected (when CommandButton1 caption reads "Sheets are Unprotected"), and disappear when protected (when CommandButton1 caption reads "Sheets are Protected").
If it makes a difference, all the data tabs names are in the format "Name-Dump", are at the end of the workbook, and the tab color is set to "no color" (compared to the ouput tab color being green).
Thanks in advance.
Private Sub CommandButton1_Click()
nCell = ActiveCell.Address
Dim pword As String
pword = InputBox("PLEASE ENTER THE PASSWORD", _
"Enter Password")
If pword <> "123" Then
MsgBox "TRY AGAIN!", _
vbCritical + vbOKOnly, "You are not authorized!"
Exit Sub
Else
Dim ws As Worksheet
Application.ScreenUpdating = False
If CommandButton1.Caption = "Sheets are Unprotected" Then
For Each ws In ThisWorkbook.Worksheets
ws.Protect
Next ws
CommandButton1.Caption = "Sheets are Protected"
Else
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect
Next ws
CommandButton1.Caption = "Sheets are Unprotected"
End If
Application.ScreenUpdating = True
Range(nCell).Select
End If
End Sub
What I would like to do is to have a similar button to Hide/Unhide the data sheets and I don't really understand how this function works. I would like for this CommandButton to appear when the sheets are unprotected (when CommandButton1 caption reads "Sheets are Unprotected"), and disappear when protected (when CommandButton1 caption reads "Sheets are Protected").
If it makes a difference, all the data tabs names are in the format "Name-Dump", are at the end of the workbook, and the tab color is set to "no color" (compared to the ouput tab color being green).
Thanks in advance.
Private Sub CommandButton1_Click()
nCell = ActiveCell.Address
Dim pword As String
pword = InputBox("PLEASE ENTER THE PASSWORD", _
"Enter Password")
If pword <> "123" Then
MsgBox "TRY AGAIN!", _
vbCritical + vbOKOnly, "You are not authorized!"
Exit Sub
Else
Dim ws As Worksheet
Application.ScreenUpdating = False
If CommandButton1.Caption = "Sheets are Unprotected" Then
For Each ws In ThisWorkbook.Worksheets
ws.Protect
Next ws
CommandButton1.Caption = "Sheets are Protected"
Else
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect
Next ws
CommandButton1.Caption = "Sheets are Unprotected"
End If
Application.ScreenUpdating = True
Range(nCell).Select
End If
End Sub