Excel date problem 2

[Tezzie]

Hi and thanks for reading this.

Imagine you have a form with a textbox. This textbox is there to add a date into a cell within a work sheet. Im in the UK so i would enter a date of dd/mmmm/yyyy.

The code behind it would be something like..

let sheets("sheet1&quot.cells(1,1) = textbox1.value

or

let sheets("sheet1&quot.cells(1,1) = textbox1.text

Now - this works to a point. When I enter dd/mmmm/yyyy, by the time I get to the month, excel is switching the date and the month. eg. I enter say 1/2/2003 (1st Feb 2003) Excel then swaps it on the sheet to 2nd Jan 2003.

Typing the date into the textbox as a long date format solves this, but the form created requires many dates and those dates have to be on the sheets correctly for lookup tables to work.

I have read MSDN where they mention CDate, but my VBA is ultra basic and when I have tried to experiment I generate errors. I have also done searches on the web that gives many lines of code. I want something simple.

Can anyone give a VBA novice a helping hand?

Cheers

Terry

ps, I have looked at calender controls and DTpicker. Although they would solve the problem, the forms we use are very limited on space so only textboxes will do.

 

[DaleWatson123321]

Terry,

Have you tried changing the settings for Date under:

Control Panel - Regional Settings - Date

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Tezzie]

Yep, all the regional settings have been checked and are UK

Thanks for the reply!!

Tez

 

[RobBroekhuis]

I changed the date format in my regional options to d/m/y, and immediately Excel (both worksheet and VBA) interprets 1/2/3 as 1 February, 2003. I wonder why it doesn't for you? Does it work when you enter the date directly into the Excel worksheet?

Rob

 

[scottsek]

I know you don't want anything difficult, but if you just put cdate around the textbox1.text like this

let sheets("sheet1&quot.cells(1,1) = CDate(textbox1.text)

it should work. I've tested it with UK settings and it works on my computer. If you still have trouble, please re-post.

HTH,

Scott

 

[getitdaily]

Read through this, it might help.

http://www.fontstuff.com/access/acctut09.htm

 

[Tezzie]

Thanks for the replies. Its all very curious. Can I ask if everyone here is using Windows XP?

I know that XP has about 4 different places where you can set the region, but they all point to the UK.

When I enter the date directly into the worksheet it is fine. The format of the cells are d/mmmm/yyyy.

Regarding the CDate. I used that, but the moment I enter a slash in the textbox I get error 13.

I also looked at the link. Although its for access, I may do something like that on the form, but as I mentioned in the first post, there's very little space on the form for much.


Tez

 

[scottsek]

Where is this code, i.e. in what event have you put it? I'm using XP, to answer one of your questions.

Scott

 

[Tezzie]

all I have is the following code for the form:

Private Sub TextBox1_Change()
Let Sheets("sheet1&quot.Cells(1, 1) = TextBox1.Text
End Sub

 

[RobBroekhuis]

This may be a long shot, but try to use the textbox1_exit event instead of the _change event. The latter will write the incomplete value to the cell after each character typed by the user. This may be confusing Excel, or somehow setting an improper format. Try:

sub textbox1_exit
if isdate(textbox1) then sheets("sheet1&quot.cells(1,1)=textbox1
end sub

As I said, it's a long shot, but since typing directly into Excel cells works, it's worth a try...
Rob

 

[Tezzie]

Rob U are a star.

I had to modify it slightly to produce a long date format as your example put an exact copy into the cell, so 1/2/3 was 1/2/3 and not 1 Feb 2003.

My modified version is...

Private Sub textbox1_exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsDate(TextBox1) Then Sheets("sheet1&quot.Cells(1, 1) = CDate(TextBox1)
End Sub


Basically an amalgum of previous suggestions.

Everyone that contributed thanks a huge amount. I had scratched my head on this for the last 3 days.

Tez