Show Tabs by date

[Guest]

Is there a way to create a macro that will show the tabs at the bottom by the
current month? I have the tabs named by month, and only want it to show
January-Current month tabs only as it progresses through the year. So, for
instance, Tabs January, February, March, etc.... through September would be
showing right now, then next month, would include October.

 

[JW]

There are a couple different ways you could do this. I chose to use
an array of month names that correlate with the sheet names. This
will allow any sheets that are stuck inbetween months to not be
hidden. Edit this as you see fit.
Sub sheetHider()
Dim mthList As Variant, sht As Worksheet
mthList = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
If Month(Date) = 12 Then
MsgBox "No months to hide"
Else
For mth = Month(Date) + 1 To 12
For Each sht In ActiveWorkbook.Worksheets
If sht.Name = mthList(mth - 1) Then
sht.Visible = xlSheetHidden
End If
Next sht
Next mth
End If
End Sub

 

[JE McGimpsey]

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
End Sub

Note that the year in DateSerial(year,month,day) could be any valid year.

 

[Guest]

thanks so much, both of these worked!!! The last one serves my purpose
better because I can't have a prompt as the macro is run at night.

 

[Guest]

one more question, is there a way to select the current month worksheet?

 

[JE McGimpsey]

One way:

Public Sub UpToCurrentMonth()
Dim i As Long
Dim nCurrentMonth As Long
nCurrentMonth = Month(Date)
For i = 1 To 12
Worksheets(Format(DateSerial(2007, i, 1), "mmmm")).Visible = _
i <= nCurrentMonth
Next i
Worksheets(Format(DateSerial(2007, nCurrentMonth, 1), "mmmm")).Select
End Sub

 

[JW]

The prompt is not really necessary. I just put it in for
demonstration purposes.
Sub sheetHider()
Dim mthList As Variant, sht As Worksheet
mthList = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _
"Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
If Month(Date) <> 12 Then
For mth = Month(Date) + 1 To 12
For Each sht In ActiveWorkbook.Worksheets
If sht.Name = mthList(mth - 1) Then
sht.Visible = xlSheetHidden
End If
Next sht
Next mth
End If
End Sub

 

[JE McGimpsey]

Tiny niggle: If the user has manually hidden one or more sheets,
including sheet "Jan", it is possible to get a run-time error with this
technique since at least one sheet must remain visible.

 

[JW]

JE, you are correct. That most certainly could happen. But that
could happen with your technique as well, correct?

 

[JE McGimpsey]

Not correct.

With my technique, the January sheet will always be made visible as the
first step, even if the user has hidden it.

 

[Dana DeLouis]

Another idea is the use of "MonthName"

For m = 1 To 12
Worksheets(MonthName(m, False)).Visible = (i <= nCurrentMonth)

 

[JE McGimpsey]

Yeah - MonthName() is a VBA6+ function, so I tend to forget about it
since I develop cross-platform applications, and I still have a
substantial minority of customers using WinXL97.

 

[JW]

Ahhhh, said the blind man. Looked right over that. Smooth code.