I need vba code to hide a worksheet as very hidden when another worksheet tab is selected.

[J1149]

I have a command button that unhides a worksheet after a password is entered. I need some help with vb. code to return the worksheet back to very hidden when another tab (worksheet) is selected.

Here is the code I have to unhide the very hidden worksheet:

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

Any help would be greatly appreciated.

 

[AlphaFrog]

Put this code in the ThisWorkbook code module.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_SheetActivate([color=darkblue]ByVal[/color] Sh [color=darkblue]As[/color] [color=darkblue]Object[/color])
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Sh [color=darkblue]Is[/color] Worksheets("Confidential") [color=darkblue]Then[/color]
        [color=green]'Set worksheet to Very Hidden so that it can only be unhidden by a macro[/color]
        Worksheets("Confidential").Visible = xlSheetVeryHidden
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]

 

[J1149]

I have another issue I am trying to resolve. I need the cells J8, J10, J12, J14 to change from a white interior to a gray pattern when the number in J4 is changed In other words when J4 changes to 2, J8 interior color changes when J4 is changed to 3 J10 then changes and the same for J12 and J14. Also in cell C4 if 5 is selected I need Cells I 16 and J16 to show with J16 interior to be white when the value is "5" in J4. When the workbook is reset for a new period I need the cells J8, J10, J12, J14 interior to be white until J4 is change from 1 to 2, etc with week 1 gray patterned. I have coded it but I cannot get week 4,5 to work properly, nor can I get the cells to change back to a white interior when the workbook is reset.

I have to figure out how to send an attachment. I will send in a separate message.

 

[J1149]

I guess I can't post an attachment. So here is my code

Private Sub Worksheet_Activate()
        
    Me.ScrollArea = "A1:P25"
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 1
    
    If Range("J4") = 1 Then
     Range("J8").Select
    ElseIf Range("J4") = 2 Then
     Range("J10").Select
    ElseIf Range("J4") = 3 Then
     Range("J12").Select
    ElseIf Range("J4") = 4 Then
     Range("J14").Select
    ElseIf Range("J4") = 5 Then
     Range("J16").Select
    End If
'   GrossSalesCalc Macro
    ActiveCell.FormulaR1C1 = _
        "=SUM('STAGING AREA'!R[-5]C[-8],'STAGING AREA'!R[-3]C[-8],'STAGING AREA'!R[-2]C[-8],'STAGING AREA'!RC[-8])"
    Range("J10").Select
    ActiveCell.FormulaR1C1 = _
        "=SUM('STAGING AREA'!R[-7]C[-6],'STAGING AREA'!R[-5]C[-6],'STAGING AREA'!R[-4]C[-6],'STAGING AREA'!R[-2]C[-6])"
    Range("J12").Select
    ActiveCell.FormulaR1C1 = _
        "=SUM('STAGING AREA'!R[-9]C[-4],'STAGING AREA'!R[-7]C[-4],'STAGING AREA'!R[-6]C[-4],'STAGING AREA'!R[-4]C[-4])"
    Range("J14").Select
    ActiveCell.FormulaR1C1 = _
        "=SUM('STAGING AREA'!R[-11]C[-2],'STAGING AREA'!R[-9]C[-2],'STAGING AREA'!R[-8]C[-2],'STAGING AREA'!R[-6]C[-2])"
    Range("J16").Select
    ActiveCell.FormulaR1C1 = _
        "=SUM('STAGING AREA'!R[-13]C,'STAGING AREA'!R[-11]C,'STAGING AREA'!R[-10]C,'STAGING AREA'!R[-8]C)"
    Range("J17").Select
End Sub
Private Sub CheckWeek()
'
        If Range("J4").Value = 1 Then
        Range("J8").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlSolid
            End With
        Range("J10,J12,J14").Select
            With Selection.Interior
            .Pattern = xlGray50
            .PatternColorIndex = 16
            End With
        
        Range("J8").Select
'
        ElseIf Range("J4").Value = 2 Then
        Range("J10").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlSolid
            End With
        Range("J8,J12,J14").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlGray50
            .PatternColorIndex = 16
            End With
        Range("J10").Select
'
        ElseIf Range("J4").Value = 3 Then
        Range("J12").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlSolid
            End With
        Range("J8,J10,J14").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlGray50
            .PatternColorIndex = 16
            End With
        Range("J12").Select
'
        ElseIf Range("J4").Value = 4 Then
        Range("J14").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlSolid
            End With
        Range("J8,J10,J12").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlGray50
            .PatternColorIndex = 16
            End With
        
        Range("J14").Select
'
        ElseIf Range("J4").Value = 5 Then
        Range("J8,J10,J12,J14").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlGray50
            .PatternColorIndex = 16
            End With
        Range("J16").Select
            With Selection.Interior
            .ColorIndex = 2
            .Pattern = xlSolid
            End With
        End If
End Sub
Private Sub DIMWEEK()
        If Range("J4") = 5 Then
            Call ShowWeek5
        Else
            Call NOShowWeek5
        End If
         
End Sub
Private Sub ShowWeek5()
'
       Range("J16").Select
         With Selection.Interior
              .ColorIndex = 2
              .Pattern = xlGray50
              .PatternColorIndex = 16
         End With
         With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .Weight = xlThick
            .ColorIndex = 16
        End With
         With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .Weight = xlThick
            .ColorIndex = 16
        End With
         With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .Weight = xlThick
            .ColorIndex = 16
        End With
         With Selection.Borders(xlEdgeRight)
            .LineStyle = xlDouble
            .Weight = xlThick
            .ColorIndex = 16
        End With
              
End Sub
Private Sub NOShowWeek5()
'
        Range("J16").Select
        With Selection
            .Interior.ColorIndex = 15
            .Interior.Pattern = xlSolid
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
        End With
        '
End Sub

 

[AlphaFrog]

You're welcome for the first solution.

This second request is a duplicate request which is against forum rules.