vba excel hide worksheet on date cell value

Question

I am a vba newbie, but enjoy the thought of creating programs for other users to enjoy and make their workload easier. I have a workbook that contains 14 worksheets. I have been looking for vba code that hides 3 of these sheets according to a date value. The date value is situated on sheet 1 cell B7. The 3 sheets I wish to hide are sheets 2, sheets 3 and sheets 4. These 3 sheets are required to be made unhidden until 3 days before date in sheet 1 cell B7. From the date value I want the workbook to hide the 3 sheets and can only be unhidden if a correct password is used to unhide these 3 sheets. I have spent countless hours trawling many sites trying to find the correct code. Please can you help?

Mark, if you enjoy the thought of creating programs for other users then you don't search for thee correct code :) You create one! Anyways. Can you show us what have you tried and where are you stuck? Questions asking for code must demonstrate a minimal understanding of the problem being solved.Include attempted solutions, why they didn't work, and the expected results. See also: Stack Overflow question checklist — Siddharth Rout

Ahmad Ahmad · Accepted Answer · 2014-01-09T13:44:30

What you need to do is not possible with only Excel and with what you have given as requirements. Even if the sheets are hidden, the users will still be able to Un-Hide them because EXCEL allows them to do so.

If the users are okay with the possibility of the sheets to be un-hidden without the consent of the code, then go ahead and try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myDate As Date
    myDate = Range("b7").Value
    Dim curr_date As Date
    curr_date = Date


    If curr_date >= CDate((myDate) - 3) Then

        ActiveWorkbook.Sheets(2).Visible = xlSheetVeryHidden
        ActiveWorkbook.Sheets(3).Visible = xlSheetVeryHidden
        ActiveWorkbook.Sheets(4).Visible = xlSheetVeryHidden

    Else
        ActiveWorkbook.Sheets(2).Visible = xlSheetVisible
        ActiveWorkbook.Sheets(3).Visible = xlSheetVisible
        ActiveWorkbook.Sheets(4).Visible = xlSheetVisible

    End If
End Sub

Remember that you have to invoke VB Editor to type the above code. To do that, you will need to press Alt + F11 then you will be presented with a list of 14 sheets that you have. Double click SHEET1 to be able to write the above code. Just copy and paste it there. Alternatively you could choose Worksheet from the combobox on the top left side of the bar, and Change from the top right side, then paste the code there.

Here is a simplification image that will help you understand what I mean: Hiding Sheets based on Date

vba excel hide worksheet on date cell value

1 Answers