Hide and unhide the worksheets in vba with dialog box

[designerbabo]

Hi everyone,

I need one vba code that hide the sheets base on name that I give in dialog box for example I have three sheet "Calculation" , "SOR" , "Data"

When I use this vba code to hide sheet that code ask me which sheet you want to hide. For example when I put sheet name "SOR" then this code hide "SOR" Sheet and when I use this Code again and give the Sheet name "Data" then this code hide this sheet and same procedure to unhide also.

Thanks in advance please i really need help from you guys.

[geekgirlau]

This will work - shows the procedure and how to call that procedure. However unless your dialog box is doing other stuff as well, I don't think this is any faster than right-clicking on the sheet tab ...


Sub Other()
...

HideSheet TRUE,<Name of sheet>

...
End Sub

Sub HideSheet(blnVisible As Boolean, strSheet As String)
Worksheets(strSheet).Visible = blnVisible
End Sub

[designerbabo]

thanks for your early reply.

Please the below code .... in the below code we can deep hide only sheet that have name "confidential"

I need in this code to ask the name or give the dialog box to select the sheet that we want to hide or unhide.


'** This code goes in a standard module **
Option Explicit
Option Compare Text
'Password to unhide sheets
Const pWord = "MyPassword"

Sub HideSheets()
'Set worksheet to Very Hidden so that it can only be unhidden by a macro
Worksheets("Confidential").Visible = xlSheetVeryHidden
End Sub

Sub ShowSheets()
'Prompt the user for a password and unhide the worksheet if correct
Select Case InputBox("Please enter the password to unhide the sheet", _
"Enter Password")

Case Is = pWord
With Worksheets("Confidential")
.Visible = xlSheetVisible
.Activate
.Range("A1").Select
End With
Case Else
MsgBox "Sorry, that password is incorrect!", _
vbCritical + vbOKOnly, "You are not authorized!"
End Select
End Sub

'** This code goes in the ThisWorkbook module **
Option Explicit

Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False

'Hide confidential sheet at startup
Call HideSheets

'Activate cell A1 on the Dashboard sheet at startup
With Worksheets("Dashboard")
.Activate
.Range("A1").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub

[BrianMH]

You want to do this at start up for anyone using the workbook and you want to give the option to hide or unhide any sheet at that point?

[Teeroy]

I've expanded on your requirement in a couple of areas (I got carried away :)). You can now hide up to 5 sheets where you code their names into an array ("Confidential" is already added). I've also changed the point that the Worksheet is hidden to the workbook BeforeClose event. Otherwise if the workbook is opened without macros being enabled the user may be able to see the sheets that were to be hidden (if they weren't hidden before the Workbook was last saved).

The Unhide Sub lists all hidden sheets and asks the user to choose a number (next to the name) to unhide the sheet. I considered this to be safe since the user needed the password to call the Sub and less prone to entry error than typing the name.

The following has been tested in Excel 2003.


'** This code goes in a standard module **
Option Explicit

Option Compare Text
'Password to unhide sheets
Const pWord = "MyPassword"

Sub HideSheets()
Dim sheetlist(0 To 5) As String
Dim i As Integer
'creat list of sheets to be hidden
sheetlist(0) = "Confidential"
'Set worksheet to Very Hidden so that it can only be unhidden by a macro
For i = LBound(sheetlist) To UBound(sheetlist)
'if the array position is empty go to next loop
If sheetlist(i) = "" Then GoTo nextloop
'if the sheeet doesn't exist goto next loop
If Sheets(sheetlist(i)) Is Nothing Then GoTo nextloop
'if the sheet is visible set to very hidden
If Worksheets(sheetlist(i)).Visible = xlSheetVisible Then
Worksheets(sheetlist(i)).Visible = xlSheetVeryHidden
End If
nextloop:
Next i
End Sub

Sub ShowSheets()
'Prompt the user for a password and unhide the worksheet if correct
Select Case InputBox("Please enter the password to unhide the sheet", _
"Enter Password")

Case Is = pWord
Call Unhide
Case Else
MsgBox "Sorry, that password is incorrect!", _
vbCritical + vbOKOnly, "You are not authorized!"
End Select
End Sub

Sub Unhide()
Dim hiddenSheets() As String
Dim sht As Worksheet
Dim counter As Integer
Dim i As Integer
Dim text As String
Dim response As String


ReDim hiddenSheets(Sheets.Count)

counter = 0

For Each sht In Sheets
Select Case sht.Visible
Case xlSheetHidden:
hiddenSheets(counter) = sht.Name
counter = counter + 1
Case xlSheetVeryHidden:
hiddenSheets(counter) = sht.Name
counter = counter + 1
End Select
Next
If hiddenSheets(0) = "" Then
MsgBox "There are no hidden sheets."
Else:
text = "The following hidden sheets may be shown:" & vbNewLine & vbNewLine
For i = 0 To counter - 1:
text = text & i + 1 & ": " & hiddenSheets(i) & vbNewLine
Next i
repeat:
response = InputBox(text, Title:="Show hidden sheets")
If response = "" Then Exit Sub
If Not IsNumeric(response) Then GoTo repeat
If Val(response) > counter Or Val(response) < 1 Then
MsgBox "You must choose a number corresponding to the Sheet to be displayed."
GoTo repeat
End If
Sheets(hiddenSheets(Val(response) - 1)).Visible = xlSheetVisible
End If

End Sub



'** This code goes in the ThisWorkbook module **
Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False


'Activate cell A1 on the Dashboard sheet at startup
With Worksheets("Dashboard")
.Activate
.Range("A1").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub


Sub Workbook_BeforeClose(Cancel As Boolean)
'Hide sheets at close
Call HideSheets

End Sub

[Judah]

:banghead: Hi Guys, I am literally like that guy hitting the wall...

I am putting 2gether this dashboard to allow my department to input data. I was looking for some coding to do that and this thread was my salvation... In fact I learned a lot about VB with it...at this point only thing is not working... and that is the ability to open the workbook on the Main_Page sheet after users close their worksheets. The way it is working is: I have this sheet name Main_Page with Buttons that take users to their worksheets by adding a password. Each user has a button that when clicked, close they worksheet and take them to the Main_Page, and this is what I really wanted... The :banghead: is when I tried to add the code that will make users start at the main page all the time...it is not working. The attached images will give an idea. The code I am trying are basically the last part of the codes below.
Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False

'Hide confidential sheet at startup
Call HideSheets

'Activate cell A1 on the Dashboard sheet at startup
With Worksheets("Dashboard")
.Activate
.Range("A1").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub
also tried this:
'** This code goes in the ThisWorkbook module **
Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False


'Activate cell A1 on the Dashboard sheet at startup
With Worksheets("Dashboard")
.Activate
.Range("A1").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub


Sub Workbook_BeforeClose(Cancel As Boolean)
'Hide sheets at close
Call HideSheets

End Sub

None of them is giving me what I want.The msg I receive in the VBA project page is also attached. Please Somebody help me...

[Tom Jones]

Hi,

Teeroy code works for me.

[Judah]

Hi,

Teeroy code works for me.:thumb

Tom, thanks for replying I went back to Teeroy's copied and past it again...Still wasn't working. I then tried to remove the:
Sub Workbook_BeforeClose(Cancel As Boolean)
'Hide sheets at close
Call HideSheets

End Sub

and Magic Happened. Thanks again man.

By the way, this forum rocks

[Tom Jones]

judah you have to thank to Teeroy

You should carefully read Teeroy's code
Put this code in a module.




Option Explicit

Option Compare Text
'Password to unhide sheets
Const pWord = "MyPassword"

Sub HideSheets()
Dim sheetlist(0 To 5) As String
Dim i As Integer
'creat list of sheets to be hidden
sheetlist(0) = "Confidential"
'Set worksheet to Very Hidden so that it can only be unhidden by a macro
For i = LBound(sheetlist) To UBound(sheetlist)
'if the array position is empty go to next loop
If sheetlist(i) = "" Then Goto nextloop
'if the sheeet doesn't exist goto next loop
If Sheets(sheetlist(i)) Is Nothing Then Goto nextloop
'if the sheet is visible set to very hidden
If Worksheets(sheetlist(i)).Visible = xlSheetVisible Then
Worksheets(sheetlist(i)).Visible = xlSheetVeryHidden
End If
nextloop:
Next i
End Sub

Sub ShowSheets()
'Prompt the user for a password and unhide the worksheet if correct
Select Case InputBox("Please enter the password to unhide the sheet", _
"Enter Password")

Case Is = pWord
Call Unhide
Case Else
MsgBox "Sorry, that password is incorrect!", _
vbCritical + vbOKOnly, "You are not authorized!"
End Select
End Sub

Sub Unhide()
Dim hiddenSheets() As String
Dim sht As Worksheet
Dim counter As Integer
Dim i As Integer
Dim text As String
Dim response As String


ReDim hiddenSheets(Sheets.Count)

counter = 0

For Each sht In Sheets
Select Case sht.Visible
Case xlSheetHidden:
hiddenSheets(counter) = sht.Name
counter = counter + 1
Case xlSheetVeryHidden:
hiddenSheets(counter) = sht.Name
counter = counter + 1
End Select
Next
If hiddenSheets(0) = "" Then
MsgBox "There are no hidden sheets."
Else:
text = "The following hidden sheets may be shown:" & vbNewLine & vbNewLine
For i = 0 To counter - 1:
text = text & i + 1 & ": " & hiddenSheets(i) & vbNewLine
Next i
repeat:
response = InputBox(text, Title:="Show hidden sheets")
If response = "" Then Exit Sub
If Not IsNumeric(response) Then Goto repeat
If Val(response) > counter Or Val(response) < 1 Then
MsgBox "You must choose a number corresponding to the Sheet to be displayed."
Goto repeat
End If
Sheets(hiddenSheets(Val(response) - 1)).Visible = xlSheetVisible
End If

End Sub



and this code in ThisWorkbook module


Private Sub Workbook_Open()
'Turn off screen updates
Application.ScreenUpdating = False


'Activate cell A1 on the Dashboard sheet at startup
With Worksheets("Dashboard")
.Activate
.Range("A1").Select
End With

'Restore screen updates
Application.ScreenUpdating = True
End Sub


Sub Workbook_BeforeClose(Cancel As Boolean)
'Hide sheets at close
Call HideSheets

End Sub