Solved Sheet Tabs to show date of month

January 31, 2012 at 07:20:18
Specs: Windows XP
My friend changes her tabs manually to show the month, day and year. I want to enter the first day of the month in cell D1 as an example and D1 for all other sheets will show the date incremented to the correct date. The tabs should refer to D1 and then show the date to show as she is now doing manually. I found this code but have not been able to get it to work so please tell me what I am doing wrong. Working for the state I cannot obligate them to anything but may be able to pay something myself. I am new to Visual Basic. Would also like for it to show only the tabs for any given month such as 29 for February but if can't they can go into the next month.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Name = Range("D1").Value
End Sub

Thanks


See More: Sheet Tabs to show date of month

Report •

✔ Best Answer
January 31, 2012 at 13:52:06
The following code should do what the user requested.

I suggested that it be tested ina backup copy of the workbook.

It has been set up to hide the sheets for last day(s) of the month for the shorter months. It will also handle Leap Years.

1 - There must be 31 sheets in the workbook for this to work correctly.

2 - Right click the sheet tab for Sheet1 and choose View Code.

3 - Paste the code into the pane that opens.

4 - Enter a valid date in Sheet1!D1

Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see if a valid Date was entered into D1
  If Target.Address = "$D$1" Then
   If Not IsDate(Range("D1")) Then
      MsgBox "D1 Must Contain A Valid Date"
      Exit Sub
   End If
Application.ScreenUpdating = False
'Make all Sheets visible and reset Sheet Names to prevent errors
   For shtNum = 1 To Sheets.Count
    Sheets(shtNum).Visible = True
    Sheets(shtNum).Name = shtNum
   Next
'Rename Sheet1 to match Date e.g. January 1 2012
    Sheets(1).Name = MonthName(Month(Sheets(1).Range("D1"))) & " " & _
                     Day(Sheets(1).Range("D1")) & " " & _
                     Year(Sheets(1).Range("D1"))
'Loop through sheets
      For shtNum = 2 To Sheets.Count
'Place formula in D1 to increment date in each sheet
        Sheets(shtNum).Range("D1").Formula = _
                     "='" & Sheets(shtNum - 1).Name & "'!D1 + 1"
'Rename Sheets to match Date e.g. January 2 2012 , January 3 2012 , etc.
        Sheets(shtNum).Name = _
                     MonthName(Month(Sheets(shtNum).Range("D1"))) & " " & _
                     Day(Sheets(shtNum).Range("D1")) & " " & _
                     Year(Sheets(shtNum).Range("D1"))
      Next
'Hide Sheets 29 - 31 based on number of days in Month
      Select Case Month(Range("D1"))
'30 Days has September, April, June and November
        Case 4, 6, 9, 11
          Sheets(31).Visible = False
'February has 28 or 29...
        Case 2
          Sheets(30).Visible = False
          Sheets(31).Visible = False
'...so we need to check for Leap Year
            If Not IsDate("2/29/" & Year(Sheets(1).Range("D1"))) Then
              Sheets(29).Visible = False
            End If
      End Select
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
January 31, 2012 at 09:45:44
re: " I want to enter the first day of the month in cell D1 as an example and D1 for all other sheets will show the date incremented to the correct date."

re: "Would also like for it to show only the tabs for any given month such as 29 for February but if can't they can go into the next month."

Do all of the sheets for a given month already exist? Are all sheets supposed to match each other, i.e. are they templates?

We need to know some more details about the workbook layout before we can offer any code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 31, 2012 at 09:55:56
Yes, the sheets already exist and the same information is in each sheet as far as column and row headers go with the difference being the numbers entered in each column. In the columns a particular cell in one sheet is not linked to the same cell in the adjacent sheet. Hope this helps.

Report •

#3
January 31, 2012 at 10:33:54
Let me see if I understand this.

1 - You have a workbook with 31 sheets, one for each day of a given month. I assume you have 31 sheets in order to handle the longest months.

2 - You want to enter e.g. April 1 in Sheet1!D1 and have D1 on Sheets 2 - 30 show April 2, April 3, etc.

3 - You want to rename the sheet tabs to match the date in D1 of each sheet.

4 - You want to hide Sheet31 since April only has 30 days. On months with 31 days, all sheets will be visible. In February the number of visible sheets will be 28 or 29, depending on the year.

Is that right?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 31, 2012 at 13:06:48
That is correct. After receiving your last message the user of the spreadsheet said she would like for it to show month, day and year but just month and day would suffice. Also, I know it is asking a lot but if the tabs could stop with the last day of the month that would be great. If there is anything I am leaving out let me know. Thanks

Report •

#5
January 31, 2012 at 13:52:06
✔ Best Answer
The following code should do what the user requested.

I suggested that it be tested ina backup copy of the workbook.

It has been set up to hide the sheets for last day(s) of the month for the shorter months. It will also handle Leap Years.

1 - There must be 31 sheets in the workbook for this to work correctly.

2 - Right click the sheet tab for Sheet1 and choose View Code.

3 - Paste the code into the pane that opens.

4 - Enter a valid date in Sheet1!D1

Private Sub Worksheet_Change(ByVal Target As Range)
'Check to see if a valid Date was entered into D1
  If Target.Address = "$D$1" Then
   If Not IsDate(Range("D1")) Then
      MsgBox "D1 Must Contain A Valid Date"
      Exit Sub
   End If
Application.ScreenUpdating = False
'Make all Sheets visible and reset Sheet Names to prevent errors
   For shtNum = 1 To Sheets.Count
    Sheets(shtNum).Visible = True
    Sheets(shtNum).Name = shtNum
   Next
'Rename Sheet1 to match Date e.g. January 1 2012
    Sheets(1).Name = MonthName(Month(Sheets(1).Range("D1"))) & " " & _
                     Day(Sheets(1).Range("D1")) & " " & _
                     Year(Sheets(1).Range("D1"))
'Loop through sheets
      For shtNum = 2 To Sheets.Count
'Place formula in D1 to increment date in each sheet
        Sheets(shtNum).Range("D1").Formula = _
                     "='" & Sheets(shtNum - 1).Name & "'!D1 + 1"
'Rename Sheets to match Date e.g. January 2 2012 , January 3 2012 , etc.
        Sheets(shtNum).Name = _
                     MonthName(Month(Sheets(shtNum).Range("D1"))) & " " & _
                     Day(Sheets(shtNum).Range("D1")) & " " & _
                     Year(Sheets(shtNum).Range("D1"))
      Next
'Hide Sheets 29 - 31 based on number of days in Month
      Select Case Month(Range("D1"))
'30 Days has September, April, June and November
        Case 4, 6, 9, 11
          Sheets(31).Visible = False
'February has 28 or 29...
        Case 2
          Sheets(30).Visible = False
          Sheets(31).Visible = False
'...so we need to check for Leap Year
            If Not IsDate("2/29/" & Year(Sheets(1).Range("D1"))) Then
              Sheets(29).Visible = False
            End If
      End Select
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question