saving dates to worksheet with regional settings (english)

[DirkBekkers]

Hi,

I've made a userform. The data entered is then saved into a combobox.
The data in the combobox is saved to a worksheet using the following code:

Please Login or Register  to view this content.

In the Userform are some textboxes with a format dd/mm/yyyy. When i save the data in the combobox it saves as dd/mm/yyyy (but i'm not sure)
When I add the data from the combobox to the worksheet the dates that start with day 1 till 12 change into mm/dd/yyyy.

I want to add something to my cmdAdd sub that saves everything with regional settings, not American.
I've tried serveral things that i've found on the web but nothing seems to work.

Can anybody give me a solution?

[OllieB]

Assuming keus.list contains a value in the following format "dd/mm/yyyy"

Please Login or Register  to view this content.

The value written into cell(1,1) is a proper date and the format is determined by the cell formatting. If no specific data format has been selected it should default to the date format of your regional settings

[DirkBekkers]

Hi!

keus.list contains about 40 values with different formats.
The date format only applies to column D and E. Those have the format "dd/mm/yyyy"

how do I focus your code only on column D and E?

Groet, Dirk

[OllieB]

Sorry Dirk, but you will have to execute the code provided for every cell that needs to hold a date value, on a cell per cell basis. You could execute a loop to re-load/re-format the desired cells after having loaded the values using your existing code.

[DirkBekkers]

Hi,

I'm trying to adjust my current code by adding your suggestion. However i'm a newbie :-) and having some troubles.

Please Login or Register  to view this content.

So for my understanding: i've added this line:

Please Login or Register  to view this content.

with the .offset the code goes to cell (2,4) in my WORKSHEET. after that the dateserial defines the value. But it does that for the entire keus.list ?? or is vba smart enough to match de choice of row/column to the list?

Thanks in advance for your replies!!!!!

[DirkBekkers]

i'll add an example:


20130229 - V2 5 2- DB -datum probleem forum.xlsm

[OllieB]

Dirk,

This is not going to work as Excel is not that smart. You will have to use the DateSerial solution for the specific entry from keus.list. Plus you would need to ppopulate the specific cell with he DateSerial after you have populated the entire range, otherwise the last statement would override the date again.

[DirkBekkers]

I've just found the solution:

I have an textbox exit sub where i check if the date is a valid date. When i format the date as dd/mmmm/yyyy instead of dd/mm/yyyy it works as a charm. I guess because no mistakes kan be made while using the long variant of the month....

Thanks for your replies!

[OllieB]

Yes, that is another way of solving it