Calendar Control -- Is this possible?
 

Hi

I've a workbook which has 380 worksheets in it. 365 are for daily
records; they sheet names are their equivalent datevalue. The sheet for
today, for examplle, is called "39080."

I want to put a Calendar Control on a different worksheet so that Excel
will automatically go to the appropriate sheet for a selected date.
Obviously the 'today' value would have to be ignored unless
specifically selected.

Does anyone know if this is possible, and if so, how to do it?

Thanks

Steve

Calendar Control -- Is this possible?
 

On your "control toolbox" toolbar there is an icon that looks like a hammer
and wrench. Click this an find the Calendar Control x.x to add a clendar
control to your sheet. Now add the following code to the sheet itself...

'***********************
Private Sub Calendar1_Click()
If SheetExists(Format(Calendar1.Value, "0")) Then
Sheets(Format(Calendar1.Value, "0")).Select
Else
MsgBox "Sorry but sheet " & Format(Calendar1.Value, "0") & _
" does not exist"
End If
End Sub

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
'***********************

Now click the triangle and pencil icon to exit design mode and you should be
good to go...
--
HTH...

Jim Thomlinson


"Steve" wrote:

Hi

I've a workbook which has 380 worksheets in it. 365 are for daily
records; they sheet names are their equivalent datevalue. The sheet for
today, for examplle, is called "39080."

I want to put a Calendar Control on a different worksheet so that Excel
will automatically go to the appropriate sheet for a selected date.
Obviously the 'today' value would have to be ignored unless
specifically selected.

Does anyone know if this is possible, and if so, how to do it?

Thanks

Steve

Calendar Control -- Is this possible?
 

Jim

This worked perfectly. Thank you.

Steve