Hide Tabs

[rhombus4]

I have a spreadsheet with Months Apr11 May11 June11 July11 all the way to MAr12 and 1 at the end which adds up all the figures

What I need is when others view the spreadsheet they can only view the present months and subsequent months. So if it was July they could only see July11 up to Mar12 ad not see the final one as well, the one which shows all the Summary details of all 12 months

 

[MrKowz]

Try:

Private Sub workbook_open()
Dim ws  As Worksheet, _
    mnt As Long
    
Application.ScreenUpdating = False
For Each ws In ActiveSheet.Workbooks
    mnt = Application.Match(Left$(ws.Name, 3), Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)
    If Not IsError(mnt) Then
        If DateSerial(Right$(ws.Name, 2), mnt + 1, 1) - 1 < Date Then
            ws.Visible = False
        End If
    End If
Next ws
Application.ScreenUpdating = True
End Sub

 

[JamesW]

EDIT: Crap, forgot about the years.. Use MrKowz solution

 

[rhombus4]

Try:

Private Sub workbook_open()
Dim ws  As Worksheet, _
    mnt As Long
 
Application.ScreenUpdating = False
For Each ws In ActiveSheet.Workbooks
    mnt = Application.Match(Left$(ws.Name, 3), Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)
    If Not IsError(mnt) Then
        If DateSerial(Right$(ws.Name, 2), mnt + 1, 1) - 1 < Date Then
            ws.Visible = False
        End If
    End If
Next ws
Application.ScreenUpdating = True
End Sub

Where do I put the above. DO I go to VBA This workbook and paste it there or do I need it on every sheet. I went to VBA this workbook and pasted it then changed the first few dates APR11 MAy11 June11 to Jan11 Feb11 and MAr11 but they wasnt hidden. I aslo need a password so the others cant just go to format sheet unhide etc

 

[MrKowz]

You need to place it in the ThisWorkbook module, and it will run when the workbook is opened.

Try this out - by making the sheets "xlVeryHidden", they can ONLY be made visible through the VBA editor. Just protect your VBAProject with a password and that should keep the users from prying into old data .

I also added a password function to the code so that if an incorrect password (or no password) is entered, then it will hide the sheets. If a correct password is entered, then it will make every sheet in the workbook visible.

Private Sub workbook_open()
Dim ws      As Worksheet, _
    mnt     As Long, _
    pword   As String
    
pword = InputBox("Please enter password for Admin access", "Password")
Application.ScreenUpdating = False
If pword <> "abc123" Then
    For Each ws In ActiveSheet.Workbooks
        mnt = Application.Match(Left$(ws.Name, 3), Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)
        If Not IsError(mnt) Then
            If DateSerial(Right$(ws.Name, 2), mnt + 1, 1) - 1 < Date Then
                ws.Visible = xlSheetVeryHidden
            End If
        End If
    Next ws
Else
    For Each ws In ActiveSheet.Workbooks
        ws.Visible = xlSheetVisible
    Next ws
End If
Application.ScreenUpdating = True
End Sub

 

[rhombus4]

You need to place it in the ThisWorkbook module, and it will run when the workbook is opened.

Try this out - by making the sheets "xlVeryHidden", they can ONLY be made visible through the VBA editor. Just protect your VBAProject with a password and that should keep the users from prying into old data .

I also added a password function to the code so that if an incorrect password (or no password) is entered, then it will hide the sheets. If a correct password is entered, then it will make every sheet in the workbook visible.

Private Sub workbook_open()
Dim ws      As Worksheet, _
    mnt     As Long, _
    pword   As String
 
pword = InputBox("Please enter password for Admin access", "Password")
Application.ScreenUpdating = False
If pword <> "abc123" Then
    For Each ws In ActiveSheet.Workbooks
        mnt = Application.Match(Left$(ws.Name, 3), Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)
        If Not IsError(mnt) Then
            If DateSerial(Right$(ws.Name, 2), mnt + 1, 1) - 1 < Date Then
                ws.Visible = xlSheetVeryHidden
            End If
        End If
    Next ws
Else
    For Each ws In ActiveSheet.Workbooks
        ws.Visible = xlSheetVisible
    Next ws
End If
Application.ScreenUpdating = True
End Sub

</p>When I ran above it asked for password so entered abc123 then said error and highlighted For Each ws In ActiveSheet.Workbooks.</p></p>if I didnt enable macros when I opened the sheet would it not hide anyway, as the Summary tab at the end wasnt hidden, and difficult to tell if working as no tabs were hidden</p>

 

[MrKowz]

My apologies, I had workbooks on my mind this morning.

To keep the users out if they don't enable macros, then you will need to have the worksheets hide when you save the workbook, that way they just need to enter a password to view the sheets, as they will already be hidden.

Private Sub Workbook_BeforeSave()
Dim ws      As Worksheet, _
    mnt     As Long, _
    pword   As String
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    mnt = Application.Match(Left$(ws.Name, 3), Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)
    If Not IsError(mnt) Then
        If DateSerial(Right$(ws.Name, 2), mnt + 1, 1) - 1 < Date Then
            ws.Visible = xlSheetVeryHidden
        End If
    End If
Next ws
Application.ScreenUpdating = True
End Sub
 

Private Sub Workbook_BeforeOpen()
Dim ws      As Worksheet, _
    mnt     As Long, _
    pword   As String
    
pword = InputBox("Please enter password for Admin access", "Password")
Application.ScreenUpdating = False
If pword = "abc123" Then
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End If
Application.ScreenUpdating = True
End Sub

 

[rhombus4]

My apologies, I had workbooks on my mind this morning.

To keep the users out if they don't enable macros, then you will need to have the worksheets hide when you save the workbook, that way they just need to enter a password to view the sheets, as they will already be hidden.

Private Sub Workbook_BeforeSave()
Dim ws      As Worksheet, _
    mnt     As Long, _
    pword   As String
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
    mnt = Application.Match(Left$(ws.Name, 3), Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"), 0)
    If Not IsError(mnt) Then
        If DateSerial(Right$(ws.Name, 2), mnt + 1, 1) - 1 < Date Then
            ws.Visible = xlSheetVeryHidden
        End If
    End If
Next ws
Application.ScreenUpdating = True
End Sub
 
 
Private Sub Workbook_BeforeOpen()
Dim ws      As Worksheet, _
    mnt     As Long, _
    pword   As String
 
pword = InputBox("Please enter password for Admin access", "Password")
Application.ScreenUpdating = False
If pword = "abc123" Then
    For Each ws In ActiveWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End If
Application.ScreenUpdating = True
End Sub

</p> stil get debug error and highlights For Each ws In ActiveSheet.Workbooks</p>maybe I'mover complicating things, is ti possible for me to hide the sheets when I need to and the other users cant open them by format sheet hide, as it will ask for a password

 

[MrKowz]

stil get debug error and highlights For Each ws In ActiveSheet.Workbooks
maybe I'mover complicating things, is ti possible for me to hide the sheets when I need to and the other users cant open them by format sheet hide, as it will ask for a password

I altered the code, please try the updated version.