VBA & date 1

[szynszyl]

i'm new about VBA for Excel so i kindly ask your support, i've created a form with a field text and a button.
I manually write a date in the text field and i push the botton in order to transfer this date to excel worksheet but it doesn't happen, the cell is activated but this strange date appear: 00/01/1900

Private Sub CommandButton1_Click()
Dim dateins As Date
Range("A1").NumberFormat = "mm/dd/yyyy"
textfield.Text = dateins
Sheets("foglio1").Range("a1").Value = dateins

Thans in advance for your help!.
Marco

 

[SkipVought]

Hi,

'I manually write a date in the text field 
'[b]NOTE: that your Date String must be in US mm/dd/yyyy format[/b]
WITH Sheets("foglio1").Range("a1")
   .Value = DateValue(textfield.Text) 
   .NumberFormat = "mm/dd/yyyy"
END WITH

The problem is that textfield.Text is TEXT and not a DATE. Dates are NUMBERS, so the DateValue function CONVERTS the STRING to a Date Value.

No need for a dateins variable, unless you need to use it later in your form.

faq68-5827

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[mintjulep]

Note to mentions that the line

textfield.Text = dateins

overwrites whatever you have typed into textfield with the value of the variable dateins, which is 0 (or actually null, since you have never given it a value).

Then you also put 0 into cell A1 with the line

Sheets("foglio1").Range("a1").Value = dateins

The number 0 evaluates to the date 1/0/1900 in Excel's convoluted date system.

 

[szynszyl]

Ok thanks! very nice!
The last question about date verification in case of mistake when manually insert this date. How can VBA/Excel check if the format "mm/dd/yyyy" is respected?
Could you suggest a method to implement this?

 

[Andrzejek]

In your original code:

Dim dateins As Date 
Range("A1").NumberFormat = "mm/dd/yyyy" 
textfield.Text = dateins 
Sheets("foglio1").Range("a1").Value = dateins

You never assign any value to your variable dateins and it remains as a default value - probably as 0 which translates into 00/01/1900.

Instead to what Skip is suggesting, I would avoid allowing user to write any date manually - I use DTPicker or any callendar control. User choose date from calendar, and you don't have to do any date validation for April 31, or Feb. 30, or leap year and such.


Have fun.

---- Andy

 

[Rofeu]

Hi,

I've used datepickers many times and they work great.....unless it turns out that half of your users are missing the necessary dll and the control is removed completely from your form when one of them opens the file.
--> make sure that all your users have the proper installation!

You can verify the used format with the 'like' operator, i.e.:

If dateins like "??/??/????" then FormatOK = True

Cheers,

Roel

 

[PHV]

You may also consider the IsDate function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886