Solved: Date Picker default date

[MacDaddy]

Hello again,

I am trying to modify a macro in Excel written by an ex-employee of the company.

The macro is basically a form which creates a new sheet with the date selected from a date picker.

The problem I have is that the default date of the date picker is May 8th 2008 and to prevent confusion and errors (as happens quite often) I want to change this to todays date, whatever that may be.

The date picker properties has the default value of '08/05/2008' but whenever I try to change it to anything else that would pass a dynamic date it throws an error and tells me it's an invalid property value.

Being pretty much a VBA novice I haven't a clue how to fix this. I've Googled the problem but most of the results state that it should default to the current date automatically.

Any and all help appreciated.

Thanks.

[lucas]

did you try leaving that property blank?

[MacDaddy]

I did, sorry for not saying, but it doesn't like that either.

[lucas]

If you can't post the workbook, maybe you can post the code. Something is causing the bahavior.

Personally, I never use date picker because it's got a problem with portability.

I use a userform calendar.

[MacDaddy]

Sorry for the late reply.

As it's not my macro I'm not sure what code is associated with it but when I click view code on the form this is what I get, so I'm assuming this is it:


'Create a new shift log sheet based on the date and shift entered into the form
Private Sub NewSheet_Click()
Application.ScreenUpdating = False
If optDay.Value = True Then
Period = "Day"
periodTime = 6
robPeriodTime = 6
End If
'If optAfternoon.Value = True Then
' Period = "Afternoon"
' periodTime = 14
'End If
If optNight.Value = True Then
Period = "Night"
periodTime = 18
robPeriodTime = 18
End If
nameMonth = getMonth(dtpDate.Month)
nameDay = dtpDate.Day
namePeriod = Mid(Period, 1, 1)
sheetName = nameDay & nameMonth & namePeriod
searchDate = dtpDate.Value
If sheetExists(sheetName) Then
MsgBox "That Sheet Already Exists"
Else
Sheets("Template").Select
Range("A1").Select
Sheet5.Unprotect ("optiplex")
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Range("A1").Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=8, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheet5.Protect ("optiplex")
ActiveSheet.Select
ActiveSheet.Name = sheetName
ActiveSheet.Cells(2, 21).Value = searchDate
ActiveSheet.Cells(2, 22).Value = Period

robotShift = getShiftDetail(dtpDate.Value, robPeriodTime, True)
ActiveSheet.Cells(3, 3).Value = robotShift
mWeldShift = getShiftDetail(dtpDate.Value, periodTime, False)
ActiveSheet.Cells(4, 3).Value = mWeldShift
prepShift = getShiftDetail(dtpDate.Value, periodTime, False)
ActiveSheet.Cells(5, 3).Value = prepShift

enterRobOperators (robotShift)
enterMWeldOperators (mWeldShift)
enterPrepOperators (prepShift)

Application.ScreenUpdating = True
End If
robPeriodTime = 0
periodTime = 0
End Sub


Thanks again,

Paul

[lucas]

Paul, there should be a userform with a calendar control in the vbe. It will have some code that runs when the form is initialized. Could you post that code so we can see if it is setting the date?


Private Sub UserForm_Initialize()
'the code in question
End Sub

[MacDaddy]

Hi Steve,

The only bit I can find that looks vaguely like what you're talking about is:

Public Sub cmdNewSheet_Click()
NewSheetDetail.Show
End Sub


I've also found this, which may be part of the problem, but I don't really understand what's going on with this:

Private Sub dtpDate_CallbackKeyDown(ByVal KeyCode As Integer, ByVal Shift As Integer, ByVal CallbackField As String, CallbackDate As Date)
End Sub


Let me know if I've given you the piece of code you need or if I've got it bassackwards again.

Many thanks,

Paul

[lucas]

When you say date picker are you talking about a calendar control?

If so look at the value property of the calendar control.

[MacDaddy]

I'll be honest with you in as much as I haven't a clue really, I've assumed (rightly or wrongly) that it's a date picker as the properties for the date on the form is 'dtpDate DTPicker'.

However, I think I've made a bit of progress, I've changed this bit:


Public Sub cmdNewSheet_Click()
NewSheetDetail.Show
End Sub


To this:

Public Sub cmdNewSheet_Click()
NewSheetDetail.dtpDate.Value = Now
NewSheetDetail.Show
End Sub


....and it seems to be behaving like I want it to, I think it may be a case of job done.

Thanks for all your help and patience, it's been very much appreciated,

Paul

[lucas]

Ok then, can you mark the thread solved using the thread tools at the top of the page?