Date Formats

[mojo249]

Hi

I have a userform which allows data to be entered onto a worksheet. One of the fields (a textbox with the standard calendar control) is the date. When the transaction saves, it correctly saves the date as dd/mm/yyyy using

ActiveCell.Value = TxtDate.Text

I also have a form which allows the user to amend a transaction by loading the data onto the form, amend it and save it back to the worksheet.

The problem is that when it picks up the data and re-saves it, it's resaved in the format mm/dd/yyyy which is a problem because I use the month as one of the categories that users can filter the data by.

I also have the following line immediately after each time the data is saved from the form to the worksheet:

Selection.NumberFormat = "dd/mm/yyyy;@"

Does anybody have any ideas how I can get around this problem? I can send the excel file if you want it....

Thanks

Dion

[Leith Ross]

Hello Dion,

It would help to see all the code. Can you post the workbook for review?

[mojo249]

Hi Leith

My file is attached. Please forgive my clunky coding - it's only my second attempt at writing a macro....

TransForm.TxtDate saves the data to the 'Transactions' sheet.

TransAmendForm will retireve the data (by entering ComboMonth, selecting the month and scrolling through transaction using the CommandNextTrans button) and re-save it (by hitting the CommandSave button).

Thanks for your help.

Dion

[mojo249]

Sorry...try the attachment again.

Dion

[Leith Ross]

Hello Mojo249,

Which user form has the problem?

[mojo249]

The problem is with TransAmendForm.

The problem arrises when TransAmendForm picks up the date from the Transactions sheet (as dd/mm/yyyy) but resaves it as mm/dd/yyyy.

Regards

Dion

[mojo249]

Hi Leith

Did you have a chance to look at this?

Dion

[mojo249]

Hi

I solved the problem - I forced a ' in front of the date when I saved it onto the worksheet. Not very elegant but it works.

Dion