Change Date Format- User Form Calendar

[MSingh]

Hi Hans,

My questions are related to date formats.

1. How can i check the user input in cell I10 is in the correct format: dd-mm-yyyy?
2. The user also has the option of selecting from the userform calendar. However, frmcalendar inserts the date as mm/dd/yyyy. Here, in S. Africa we use dd-mm-yyyy.
The worksheet has 3 other ranges (for non-date formatting) using the Worksheet_Change(ByVal..) that you supplied.

Please advise.

Kind Regards

[HansV]

Hi Mohamed,

Please don't direct questions at me personally. I'm not always online, and others may answer your questions too.

1. If you format cell I10 as dd-mm-yyyy, dates will be formatted that way regardless of how the user enters them, so even if the user enters 2-Aug-1978 or 23 October 2004, it will be displayed as 02-08-1978 and 23-10-2005, respectively.

2. How do you use the calendar control? Do you have code to fill a text box on the userform, or do you enter the value directly into a cell?

[MSingh]

Hi,

Thanks for that info.

Did away with the frmcalendar & used activex calendar; visible property=true

Private Sub Calendar1_Click()
Range("I10").Value = Calendar1.Value
'On Error Resume Next
Range("I10").NumberFormat = ("dd-mm-yyyy")
'On Error GoTo 0
End Sub

The strange part is that certain times i receieve an error message: RunTime Error '1004': Unable to set the NumberFormat property for the Range Class. This usually happens after the worksheet is cleared for new data input.

My other question is:
Since, the excel 2007 ribbon is hidden can an add-in be assigned to a command button? (Specifically Ron De Bruins RDBMail-Add In).
(Currently,macros that are permissible in my workbook are assigned with application.onkey)

Kind Regards
Mohamed

[HansV]

I would assign the number format before assigning the value, although it probably doesn't matter.
Without seeing the workbook it's not possible to know what causes the error message.

You can check which macros the add-in exposes:
- Customize the QAT.
- Select Macros in the dropdown list "Choose commands from".

You should be able to assign those macros to a command button from the Forms Controls.