Calendar Control -> Switch to Sheet X

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
 
J

Jim Cone

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
 
D

DartCatch14

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
 
J

Jim Cone

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 staying open 11
Calendar control code 4
2nd question - Calendar Control 8.0 2
“Calendar Control†to Insert Dates 4
Run-time error 1004: Unable to set the left property of the OLEObject class 0
Pop Calendar in User Form 2
calendar question 3

Top