Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar Control - Switch to Sheet X
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar Control - Switch to Sheet X
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar Control - Switch to Sheet X
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 "Jim Cone" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar Control - Switch to Sheet X
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 "Jim Cone" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calendar control | Excel Programming | |||
Calendar Control | Excel Worksheet Functions | |||
how to switch pages in a multipage control by a commandbutton | Excel Programming | |||
Control disappeared problem - specifically, the Calendar Control | Excel Programming | |||
Calendar Control: Can't exit design mode because control can't be created | Excel Programming |