simondopickup
asked on
Date picker returning strange values!
Hi experts.
All night i have been puzzling over this one - it seems ridiculous to me but here goes.
I have a date picker called 'date_start_pick'. When i select a value from the picker, i want to return this date to a cell in a worksheet 'DATA STORE'. Now, although the msgbox in the code beneath are returning the CORRECT values for the day, month and year values of the date i select - the value that is returned to excel has the day and month values swapped. So if i select 1st November 2007 from the date picker - the value returned to my worksheet cell is 11/01/2007. This is completely messing up my script and cant fathom why it would do this!!
Here is my code behind the userform :
Public wksdata As Worksheet
Public wks_week As Worksheet
Public wks_OEE As Worksheet
Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Sub analysis_return_button_Cli ck()
Analysis_Userform.Hide
Startup_Userform.Show
End Sub
Private Sub Fault_Period_Toggle_Click( )
OEE_date_start_pick.Enable d = True
OEE_date_start_pick.Format = dtpLongDate
OEE_date_start_pick.MinDat e = wksdata.Cells(5, 1).Value
OEE_date_start_pick.MaxDat e = wksdata.Cells(5, 2).Value - 1
End Sub
Private Sub OEE_Period_Toggle_Click()
'set the minimum and max dates on range selectors
OEE_date_start_pick.Enable d = True
OEE_date_start_pick.Format = dtpLongDate
OEE_date_start_pick.MinDat e = wksdata.Cells(5, 1).Value
OEE_date_start_pick.MaxDat e = wksdata.Cells(5, 2).Value - 1
'OEE_date_end_pick.MinDate = wksdata.Cells(5, 1).Value
'OEE_date_start_pick.MaxDa te = wksdata.Cells(5, 2).Value
End Sub
Private Sub plot_charts_button_Click()
'Plot user defined period charts
If wksdata.Cells(6, 1).Value <> "" And wksdata.Cells(6, 2).Value <> "" Then
If OEE_Period_Toggle = True Then
Call OEE_Period_Chart
End If
If Fault_Period_Toggle = True Then
Call Fault_Period_Chart
End If
Else
MsgBox ("Select the upper date for Machine OEE Analysis")
Exit Sub
End If
' Call weekly charts
If OEE_Weekly_Toggle = True Then
Call OEE_Weekly_Chart
End If
If Fault_Weekly_Toggle = True Then
Call Fault_Weekly_Chart
End If
End Sub
Private Sub UserForm_Activate()
'set the user defined frame live or disable
Set wksdata = Worksheets("DATA STORE")
Set wks_week = Worksheets("OEE Weekly Summary")
Set wks_OEE = Worksheets("OEE History")
If wksdata.Cells(5, 3) = "RECORDING!" Then
OEE_History_frame.Enabled = True
Else
OEE_History_frame.Enabled = False
End If
Fault_Weekly_Toggle = False
OEE_Weekly_Toggle = False
OEE_Period_Toggle = False
Fault_Period_Toggle = False
OEE_date_start_pick.Enable d = False
OEE_date_end_pick.Enabled = False
End Sub
Private Sub UserForm_Initialize()
'set the user defined frame live or disable
Set wksdata = Worksheets("DATA STORE")
Set wks_week = Worksheets("OEE Weekly Summary")
Set wks_OEE = Worksheets("OEE History")
If wksdata.Cells(5, 3) = "RECORDING!" Then
OEE_History_frame.Enabled = True
Else
OEE_History_frame.Enabled = False
End If
Fault_Weekly_Toggle = False
OEE_Weekly_Toggle = False
OEE_Period_Toggle = False
Fault_Period_Toggle = False
OEE_date_start_pick.Enable d = False
OEE_date_end_pick.Enabled = False
End Sub
Private Sub OEE_date_start_pick_Change ()
wksdata.Cells(6, 1).Formula = OEE_date_start_pick.Day & "/" & OEE_date_start_pick.Month & "/" & OEE_date_start_pick.Year
wksdata.Cells(6, 2) = ""
MsgBox OEE_date_start_pick.Day
MsgBox OEE_date_start_pick.Month
MsgBox OEE_date_start_pick.Year
OEE_date_end_pick.Enabled = True
OEE_date_end_pick.Format = dtpCustom
OEE_date_end_pick.CustomFo rmat = Chr(32)
OEE_date_end_pick.MinDate = OEE_date_start_pick.Day & "/" & OEE_date_start_pick.Month & "/" & OEE_date_start_pick.Year
OEE_date_end_pick.MaxDate = wksdata.Cells(5, 2).Value
End Sub
Private Sub OEE_date_end_pick_Change()
wksdata.Cells(6, 2) = OEE_date_end_pick.Day & "/" & OEE_date_end_pick.Month & "/" & OEE_date_end_pick.Year
End Sub
Sub MakeDtpLongDate1()
OEE_date_start_pick.Format = dtpLongDate
Do
w = GetKeyState(1)
DoEvents
Loop While w <> 1 And w <> 0 'wait until left mouse button is released
mouse_event 2, 0, 0, 0, 0 '2 = mouse dwn
mouse_event 4, 0, 0, 0, 0 '4 = mouse up
End Sub
Sub MakeDtpLongDate2()
OEE_date_end_pick.Format = dtpLongDate
Do
w = GetKeyState(1)
DoEvents
Loop While w <> 1 And w <> 0 'wait until left mouse button is released
mouse_event 2, 0, 0, 0, 0 '2 = mouse dwn
mouse_event 4, 0, 0, 0, 0 '4 = mouse up
End Sub
Private Sub OEE_date_start_pick_MouseD own(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
Dim a As Boolean
a = x > (OEE_date_start_pick.Width - 285) 'a = true if mouse is over the grey DTPicker button
If a = False Then Exit Sub
If Button <> 1 Then Exit Sub 'allow only left mouse button
If OEE_date_start_pick.Format <> dtpLongDate Then Call MakeDtpLongDate1
End Sub
Private Sub OEE_date_end_pick_MouseDow n(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As stdole.OLE_XPOS_PIXELS, ByVal y As stdole.OLE_YPOS_PIXELS)
Dim a As Boolean
a = x > (OEE_date_end_pick.Width - 285) 'a = true if mouse is over the grey DTPicker button
If a = False Then Exit Sub
If Button <> 1 Then Exit Sub 'allow only left mouse button
If OEE_date_end_pick.Format <> dtpLongDate Then Call MakeDtpLongDate2
End Sub
Any help on this would be most appreciated!!
Thanks in advance
Simon
All night i have been puzzling over this one - it seems ridiculous to me but here goes.
I have a date picker called 'date_start_pick'. When i select a value from the picker, i want to return this date to a cell in a worksheet 'DATA STORE'. Now, although the msgbox in the code beneath are returning the CORRECT values for the day, month and year values of the date i select - the value that is returned to excel has the day and month values swapped. So if i select 1st November 2007 from the date picker - the value returned to my worksheet cell is 11/01/2007. This is completely messing up my script and cant fathom why it would do this!!
Here is my code behind the userform :
Public wksdata As Worksheet
Public wks_week As Worksheet
Public wks_OEE As Worksheet
Private Declare Sub mouse_event Lib "user32" (ByVal dwFlags As Long, ByVal dx As Long, ByVal dy As Long, ByVal cButtons As Long, ByVal dwExtraInfo As Long)
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Private Sub analysis_return_button_Cli
Analysis_Userform.Hide
Startup_Userform.Show
End Sub
Private Sub Fault_Period_Toggle_Click(
OEE_date_start_pick.Enable
OEE_date_start_pick.Format
OEE_date_start_pick.MinDat
OEE_date_start_pick.MaxDat
End Sub
Private Sub OEE_Period_Toggle_Click()
'set the minimum and max dates on range selectors
OEE_date_start_pick.Enable
OEE_date_start_pick.Format
OEE_date_start_pick.MinDat
OEE_date_start_pick.MaxDat
'OEE_date_end_pick.MinDate
'OEE_date_start_pick.MaxDa
End Sub
Private Sub plot_charts_button_Click()
'Plot user defined period charts
If wksdata.Cells(6, 1).Value <> "" And wksdata.Cells(6, 2).Value <> "" Then
If OEE_Period_Toggle = True Then
Call OEE_Period_Chart
End If
If Fault_Period_Toggle = True Then
Call Fault_Period_Chart
End If
Else
MsgBox ("Select the upper date for Machine OEE Analysis")
Exit Sub
End If
' Call weekly charts
If OEE_Weekly_Toggle = True Then
Call OEE_Weekly_Chart
End If
If Fault_Weekly_Toggle = True Then
Call Fault_Weekly_Chart
End If
End Sub
Private Sub UserForm_Activate()
'set the user defined frame live or disable
Set wksdata = Worksheets("DATA STORE")
Set wks_week = Worksheets("OEE Weekly Summary")
Set wks_OEE = Worksheets("OEE History")
If wksdata.Cells(5, 3) = "RECORDING!" Then
OEE_History_frame.Enabled = True
Else
OEE_History_frame.Enabled = False
End If
Fault_Weekly_Toggle = False
OEE_Weekly_Toggle = False
OEE_Period_Toggle = False
Fault_Period_Toggle = False
OEE_date_start_pick.Enable
OEE_date_end_pick.Enabled = False
End Sub
Private Sub UserForm_Initialize()
'set the user defined frame live or disable
Set wksdata = Worksheets("DATA STORE")
Set wks_week = Worksheets("OEE Weekly Summary")
Set wks_OEE = Worksheets("OEE History")
If wksdata.Cells(5, 3) = "RECORDING!" Then
OEE_History_frame.Enabled = True
Else
OEE_History_frame.Enabled = False
End If
Fault_Weekly_Toggle = False
OEE_Weekly_Toggle = False
OEE_Period_Toggle = False
Fault_Period_Toggle = False
OEE_date_start_pick.Enable
OEE_date_end_pick.Enabled = False
End Sub
Private Sub OEE_date_start_pick_Change
wksdata.Cells(6, 1).Formula = OEE_date_start_pick.Day & "/" & OEE_date_start_pick.Month & "/" & OEE_date_start_pick.Year
wksdata.Cells(6, 2) = ""
MsgBox OEE_date_start_pick.Day
MsgBox OEE_date_start_pick.Month
MsgBox OEE_date_start_pick.Year
OEE_date_end_pick.Enabled = True
OEE_date_end_pick.Format = dtpCustom
OEE_date_end_pick.CustomFo
OEE_date_end_pick.MinDate = OEE_date_start_pick.Day & "/" & OEE_date_start_pick.Month & "/" & OEE_date_start_pick.Year
OEE_date_end_pick.MaxDate = wksdata.Cells(5, 2).Value
End Sub
Private Sub OEE_date_end_pick_Change()
wksdata.Cells(6, 2) = OEE_date_end_pick.Day & "/" & OEE_date_end_pick.Month & "/" & OEE_date_end_pick.Year
End Sub
Sub MakeDtpLongDate1()
OEE_date_start_pick.Format
Do
w = GetKeyState(1)
DoEvents
Loop While w <> 1 And w <> 0 'wait until left mouse button is released
mouse_event 2, 0, 0, 0, 0 '2 = mouse dwn
mouse_event 4, 0, 0, 0, 0 '4 = mouse up
End Sub
Sub MakeDtpLongDate2()
OEE_date_end_pick.Format = dtpLongDate
Do
w = GetKeyState(1)
DoEvents
Loop While w <> 1 And w <> 0 'wait until left mouse button is released
mouse_event 2, 0, 0, 0, 0 '2 = mouse dwn
mouse_event 4, 0, 0, 0, 0 '4 = mouse up
End Sub
Private Sub OEE_date_start_pick_MouseD
Dim a As Boolean
a = x > (OEE_date_start_pick.Width
If a = False Then Exit Sub
If Button <> 1 Then Exit Sub 'allow only left mouse button
If OEE_date_start_pick.Format
End Sub
Private Sub OEE_date_end_pick_MouseDow
Dim a As Boolean
a = x > (OEE_date_end_pick.Width - 285) 'a = true if mouse is over the grey DTPicker button
If a = False Then Exit Sub
If Button <> 1 Then Exit Sub 'allow only left mouse button
If OEE_date_end_pick.Format <> dtpLongDate Then Call MakeDtpLongDate2
End Sub
Any help on this would be most appreciated!!
Thanks in advance
Simon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Simon