Calendar Set Up

[sparky3883]

Good Evening all,

I've not been on here for a wee while so I apologise in advance for my lack of knowledge regarding Excel. I am hoping that someone may be able to help me with a query that I may have.

I cureently am using 12 excel documents throughout a year - these documents aresaved on my Desktop and a named Jan, Feb, Mar etc...
When you open the file for that specific month, there is then 30/31 tabs depending on how many days of the month there are. All I simply do is, select the current month, then the current day and then record my relevant information on the Spreadsheet.

To cut down on all this hassle of having 12 different documents, i am wondering if there is a option to have 1 main document, which, when opened, on the 'home page', I can press a buton for the current month and this will then open up the requested months spreadsheet, with all the days of that month on.

Is this something which is possible, or am I just being too ambitious.
I have attached a copy of the Worksheet, which I have just duplicated 12 times, so that you can see what I mean!
Any help/advice/comments would be gratefully appreciated.

Kindest Regards,
Mark

[aclawson]

You can place this code in your Personal.xlsb (or Personal.xls if you're using Excel '03).

It's not quite perfect, but should give you a good starting point.

One stipulation is that you will need to remove the "th"'s and "st"'s in the tab names. Just have them named the number of the day (1, 2, . . 30, 31, etc.)

You can replace the "Sub OpenDateBook()" with "Private Sub Workbook_Open()" to have it run each time you start Excel.

Sub OpenDateBook()

Dim myDate As Date
Dim myDir As String
Dim myMonth As String
Dim myFile As String
Dim myDay As String

StartOver:

    On Error Resume Next
    myDate = Application.InputBox("Please enter a date")
    On Error GoTo 0

    If myDate = 0 Then
        NextAction = MsgBox("Invalid date!  Please try again.", vbRetryCancel)
        Select Case NextAction
        Case 4
            GoTo StartOver
        Case 2
            Exit Sub
        End Select
    End If
            
    myDir = "C:\"  ' Add your directory here, don't forget the final '\'
    myMonth = Application.WorksheetFunction.Text(myDate, "Mmmm")
    myFile = myMonth & ".xlsx"  ' You may need to modify this based on you file naming convention
    myDay = Day(myDate)
    
    On Error Resume Next
    Workbooks.Open Filename:=myDir & myFile
    myerr = Err
    On Error GoTo 0
    
    Select Case myerr
    
    Case 1004
        MsgBox (myFile & " does not exist." & vbCrLf & "Please create the file and try again.")
        Exit Sub

    Case 0
        On Error Resume Next
        Workbooks(myFile).Sheets(myDay).Activate
        myErr2 = Err
        On Error GoTo 0

        Select Case myErr2
        Case 9
            MsgBox ("A page for day " & myDay & " does not exist." & vbCrLf & "You'll have to create it yourself.")
            Exit Sub
        Case 0
        Case Else
            MsgBox ("Unknown sheet error " & myErr2 & vbCrLf & "Please try a different date")
        End Select
            
    Case Else
        MsgBox ("Unknown workbook error " & myerr & vbCrLf & "Please try a different date")
    
    End Select

End Sub

Hope that helps.

[shg]

For anything you want to do with your data, you'd be better off keeping all the days in the month and all the months of the year on one workheet, adding a column for date.