Calendar Control -> Switch to Sheet X

  • Thread starter Thread starter DartCatch14
  • Start date Start date
D

DartCatch14

I am trying to allow a user to select a date from a Calendar Control, then
based upon the user's selection, move to a specific Sheet

Here is my code so far

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat = "mm/dd/yyyy"
If ActiveCell.Value = "04/12/2008" Then
Sheets("Week 2").Select
End If
Unload Me
End Sub

When the user selects 04/12/2008, the active cell changes, but the cursor
stays on this cell and the user does not go to Sheet, "Week 2".

Any ideas? Suggestions?

thanks
 
Try..
If ActiveCell.Text = "04/12/2008" Then
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"DartCatch14"
wrote in message
I am trying to allow a user to select a date from a Calendar Control, then
based upon the user's selection, move to a specific Sheet
Here is my code so far

Private Sub Calendar1_Click()
ActiveCell = Calendar1.Value
ActiveCell.NumberFormat = "mm/dd/yyyy"
If ActiveCell.Value = "04/12/2008" Then
Sheets("Week 2").Select
End If
Unload Me
End Sub

When the user selects 04/12/2008, the active cell changes, but the cursor
stays on this cell and the user does not go to Sheet, "Week 2".
Any ideas? Suggestions?
thanks
 
Thanks, Jim, but for future programming support, I have moved away from hard
coding specific days as in my initial code (=4/13, =4/14, =4/15, etc.) to
something more easier to maintain going forward.

No matter what date a user selects from the Calendar Control, I have a
formula which computes the week ending Saturday date in the selected week -
this is working fine.

I know need to jump to a different sheet based upon the week ending Saturday
date.

Here is my code right now, but it returns a 1004 error message

Sub Calendar1_Click()
Range("P1").Select
'P1 is where the Calendar Control links the date
'Q1 is the computed Week Ending Saturday

If Range("Q1" = "4/5/2008") Then
Sheets("Week 1").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "4/12/2008") Then
Sheets("Week 2").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "4/19/2008") Then
Sheets("Week 3").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "4/26/2008") Then
Sheets("Week 4").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "5/3/2008") Then
Sheets("Week 5").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "5/10/2008") Then
Sheets("Week 6").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "5/17/2008") Then
Sheets("Week 7").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "5/24/2008") Then
Sheets("Week 8").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "5/31/2008") Then
Sheets("Week 9").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "6/7/2008") Then
Sheets("Week 10").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "6/14/2008") Then
Sheets("Week 11").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "6/21/2008") Then
Sheets("Week 12").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "6/28/2008") Then
Sheets("Week 13").Activate
ActiveWindow.Zoom = 75
End If
End If
End If
End If
End If
End If
End If
Unload Me
 
Yes, you get errors when misplacing parentheses...
If Range("Q1" = "4/5/2008")
-should be-
If Range("Q1") = "4/5/2008"

However, I doubt that will work. See my first post.
There can be major differences between a cell's value and its displayed text.
The default property for a range is "Value". So omitting the property is setting
you up for failure in this case. A date is a numeric value not a text string.
4/5/2008 is actually carried in the cell as 39543.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"DartCatch14"
wrote in message
Thanks, Jim, but for future programming support, I have moved away from hard
coding specific days as in my initial code (=4/13, =4/14, =4/15, etc.) to
something more easier to maintain going forward.

No matter what date a user selects from the Calendar Control, I have a
formula which computes the week ending Saturday date in the selected week -
this is working fine.

I know need to jump to a different sheet based upon the week ending Saturday
date.

Here is my code right now, but it returns a 1004 error message

Sub Calendar1_Click()
Range("P1").Select
'P1 is where the Calendar Control links the date
'Q1 is the computed Week Ending Saturday

If Range("Q1" = "4/5/2008") Then
Sheets("Week 1").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "4/12/2008") Then
Sheets("Week 2").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "4/19/2008") Then
Sheets("Week 3").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "4/26/2008") Then
Sheets("Week 4").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "5/3/2008") Then
Sheets("Week 5").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "5/10/2008") Then
Sheets("Week 6").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "5/17/2008") Then
Sheets("Week 7").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "5/24/2008") Then
Sheets("Week 8").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "5/31/2008") Then
Sheets("Week 9").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "6/7/2008") Then
Sheets("Week 10").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "6/14/2008") Then
Sheets("Week 11").Activate
ActiveWindow.Zoom = 75
ElseIf Range("Q1" = "6/21/2008") Then
Sheets("Week 12").Activate
ActiveWindow.Zoom = 75
Else
If Range("Q1" = "6/28/2008") Then
Sheets("Week 13").Activate
ActiveWindow.Zoom = 75
End If
End If
End If
End If
End If
End If
End If
Unload Me
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calendar Form Update 4
Calendar control code 3
Calendar staying open 6
calendar question 3
“Calendar Control†to Insert Dates 4
2nd question - Calendar Control 8.0 2
Calendar Pop up issue 2
Calendar Input 2

Back
Top