Using Caledar Control 9.0 to select a worksheet

[Steve]

Hi

I'm wondering if this is possible, and if so, how to do it.

My receptionist has to record an awful lot of statistics for every day
of the working week. What we've tradionally done is have a workbook
for each month of the year April - March, and within each workbook
there is a worksheet for every week. The workbook for January 2009,
for example, contains 5 sheets called "w/c 29.12.08", "w/c 05.01.09",
"w/c 12.01.09" and so on.

What I would like to do is have a workbook with 52 worksheets - one
for each week - and a front page with a calendar control, the idea
being that when the receptionist selects a date, the relevant
worksheet opens.

We use Excel 2000 and the object is to use Calendar Control 9.0 to
locate the relevant worksheet.

Can anyone advise, please; it would be much appreciated.

Steve

 

[Bernie Deitrick]

Steve,

Use this in the click event

Private Sub Calendar1_Click()
Dim myName As String
myName = "w/c " & Format(Int((Calendar1.Value - 1) / 7) * 7 + 2, "dd.mm.yy")
'Msgbox myName ' Use this for testing
Worksheets(myName).Activate 'Comment out for initial testing
End Sub

HTH,
Bernie
MS Excel MVP

 

[Roger Govier]

Hi Steve

You could achieve it without the Calendar Control

On your Master sheet
format A1:A2>Custom>mmmm
format B1:F12>Custom>"w_c dd.mm.yy"

Enter your Startdate for the year in B1
In C1 enter
=IF(MONTH(B2+7)>MONTH($A2),"",B2+7)
copy across through D1:F1
then copy C1:F1 down through C2:C12

In B2 enter
=MAX(E2,F2)+7
copy down through B3:B12

I assume your Sheets are named w_c 29.12.08 etc. as you cannot have a / in a
sheet name

Copy this event code to your master sheet, then when you double click on any
date in the "calendar" you will be taken to your sheet.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Dim myrange As Range
Dim shname As String
Set myrange = Range("B2:F13")
If Intersect(Target, myrange) Is Nothing Then Exit Sub
shname = "w_c " & Format(Target.Value, "dd.mm.yy")
Sheets(shname).Activate
End Sub

Copy the code above
Right click on Master sheet tab
View Code
Paste into the white pane that appears
Alt+F11 to return to Excel.