Command button to Hide/Unhide tabs

[Lawrence]

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.

[vba]
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
[/vba]

[Bob Phillips]

[vba]

Private Sub CommandButton2_Click()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets

If ws.Name Like "*-Dump" Then

If ws.Visible = xlSheetVisible Then

ws.Visible = xlSheetHidden
Else
ws.Visible = xlSheetVisible
End If
End If
Next ws

End Sub
[/vba]

[Lawrence]

Thank you, works like a charm. I took your code and inserted it into the CommandButton1 code to hide/unhide the data tabs when the sheets are protected/unprotected.

Edit: On a side note, is there a way to hide/unhide a command button based on the sheet being protected/unprotected? Right now I am cheating with this code, but I am afraid that if I forget to hide it, people would be tempted to click on it.

[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address <> "$P$1" Then Exit Sub
ActiveSheet.Shapes("CommandButton5").Visible = Not ActiveSheet.Shapes("CommandButton5").Visible
[B1].Select
End Sub
[/VBA]

[Aussiebear]

Why not make a blank cell somewhere on the worksheet, but in a position which you will always remember, subject to a double click event which then fires Bob's code?

[Lawrence]

Why not make a blank cell somewhere on the worksheet, but in a position which you will always remember, subject to a double click event which then fires Bob's code?

Bob's code works like a charm. I combined it with the protect/unprotect code I posted so that when I lock the sheets prior to sending the book, it hides the data sheets. Then, when I unlock to update the information, it reveals the data sheets.

My other questions, was about command buttons that I click to update certain portions of the report. The code I posted hides/unhides the command button when I double click in cell P1. However, I have several of them and I am afraid that I may forget to hide all of them. So, I was wondering if it is possible for the command button to be automatically hidden when the sheet is locked?

If not, it is not a big deal as when you click on it with the sheet protected, a VB window pops up telling the user that the sheet is protected and that the operation cannot be performed.