Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Caledar Control 9.0 to select a worksheet
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Caledar Control 9.0 to select a worksheet
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 "Steve" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using Caledar Control 9.0 to select a worksheet
Hi Steve
You could achieve it without the Calendar Control On your Master sheet format A1:A2Custommmmm format B1:F12Custom"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. -- Regards Roger Govier "Steve" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control Select doesn't shade cells | Excel Discussion (Misc queries) | |||
How do I select several control buttons at once on an Excel sheet | Excel Discussion (Misc queries) | |||
Dragging to select multiple rows causes out of control scroll | Excel Discussion (Misc queries) | |||
Control + Shift + Up/Down not allowing me to select multiple cells. | Excel Discussion (Misc queries) | |||
Control plus click will not select multiple boxes | Excel Worksheet Functions |