Calendar userform is not writing a valid date.

[sark666]

I am using a calendar userform for the first time and things are going well except the value that gets written to a cell.

Initially I had this:

Private Sub Calendar1_Click()
Sheets("options menu").Range("H10") = Calendar1.Value

The above is writing what looks like a date value to the cell, but it's formatting can't be modified and simply trying Range("H10") + 1 results in a value error. So it looks like a date but it's not recognized as a date. It seems to be just text.

So I then tried:

cal_month = Month(Calendar1.Value)
cal_year = Year(Calendar1.Value)
cal_day = Day(Calendar1.Value)
cal_date = DateSerial(cal_year, cal_month, cal_day)
Sheets("options menu").Range("H10") = cal_date

But it's resulting in the same thing. However, I put a watch on my code and in the immediate window doing: print cal_date + 1 results in the selected date + 1 day. So obviously it's a date but somehow it's not being written to the sheet as such. I cleared that cell (H10) and manually typed in a date and verified it was behaving as a date, but then running the calendar userform results in it writing an invalid date again.

What am I missing?

 

[VoG]

Try like this

Private Sub Calendar1_Click()
With Sheets("options menu").Range("H10")
    .NumberFormat = "dd/mm/yyyy"
    .Value = Calendar1.Value
End With

 

[Trevor G]

First question is this just a VBA Calendar added on the form, or is it a DatePicker? (Which version should help here).

Try this out. Add an extra textbox on your form, then use the same code to copy the selected date to the textbox and does it then show in the correct format etc. If so then perhaps use the textbox date into the cell. If this works you can always make the textbox invisible on the form.

 

[sark666]

It's the vba calendar in from the control toolbox --> additional controls

Calendar control 11.0. This is excel 2003.

This had no effect:

Private Sub Calendar1_Click()
With Sheets("options menu").Range("H10")
    .NumberFormat = "dd/mm/yyyy"
    .Value = Calendar1.Value
End With

It's really strange what happens to the cell once it's written to H10.

2009-08-31 will appear as 2009-Aug-31

If I type 2009-05-13 in H10 it will display 2009-May-13 so it seems like a date. However, right-clicking on the cell and choosing formats for dates and choosing any type of date has no effect. In addition to doing any type of date manipulation will fail. For ex, if in cell h11 I type =year(h10) I get a value error.

To reset the cell and make it behave like a date I either copy a valid date from any other cell or change it to a number format first, clear it's value then type in a date and change it back to date.

Ok, I solved it. But still it seems strange as I haven't changed my regional settings in ages and any date manipulation in VBA has never given me an error

My regional settings for short date were: yyyy-MMM-dd

The calendar utility was writing dates like 2010-Aug-13 which could not be manipulated as a date.

Changing my regional settings to yyyy-mm-dd solved the issue.

Again, I use vba and date functions in excel all the time and have not had any issue with dates. Kind of puzzled why it was causing an issue here.

Oh and even with the prior settings of yyyy-MMM-dd I always entered dates in excel as 2009-08-31 and they remained as such. They didn't display as 2009-Aug-31, unless I manually selected that format. Only using the calendar control tool were they being displayed as 2009-Aug-31.

I can say oh well problem solved, but others will run this utility and I'd rather it not break depending on what regional setting they are using.

Oh and with the .numberformat: .NumberFormat = "yyyy/MMM/dd" just to see but that didn't help.

Is there a way to ensure the calendar date tool works reliably regardless of what regional date format the person has set? Again it's really strange that if I halt the code the cal_date variable I was using is behaving as a date until it's written to the cell.


Oh and regarding the other suggestion, I am actually already displaying the dates in a text box within the user form so the user can see which date they selected. Previously it was displaying in there as 2009-Aug-13 but now it's displaying as 2009-08-13. The text box is just referencing cell H10

 

[sark666]

Ok I solved it so it doesn't matter what regional date format the person uses. I used this in H10 =DATE(YEAR(I10),MONTH(I10),DAY(I10))

and in I10 I put the date but formatted it to general so it would store the number.

I made the calendar write to I10 instead as so:

Sheets("options menu").Range("I10") = CLng(Calendar1.Value)

But then my text boxes weren't working correctly with the rowsource set to reference I10 (as it would just display 40035 for ex). So I thought point it to H10 but it ended up overwriting my formulas?!

So I had to put

UserForm1.TextBox1.Value = Sheets("options menu").Range("H10")

throughout my code anytime the calendar date was referenced or changed.

Actually, I have used clng in the past with date issues, but how I coded this, I thought it wouldn't be an issue. That certainly wasted a lot of time. Maybe there's a better way, but now it works regardless of regional settings. Maybe someone will find this useful.