Problems with dates entered via userform

[Brian C]

I'm trying to create a date range in Excel in a column on
a worksheet I use a userform to enter a start and end
date, copy this data to locations on a worksheet in a
worksheet (in cell A2 and a cell called
A1Enddate respectively on the AutumnHT1 workshet).

I execute the following code when they hit the FINISH
button on the userform:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 15/04/2004 by blcutler
'
Sheets("AutumnHT1").Select

Range("A2").Select

Selection.DataSeries Rowcol:=xlColumns,
Type:=xlChronological, Date:= _
xlWeekday, Step:=1, Stop:=Range("A1Enddate"), Trend:=False

End Sub


I get the following error:

Run time error 1004

DataSeries method of range class failed

Any ideas of what may be happening? There is defintely
data put in the cells (A2 and A1Enddate) on AutumnHT1 by
the userform. The Userform executes on a different sheet
hence the Sheets("AutumnHT1").Select

The macro works if I enter dates directly into the cells
and then run it rather than using a userform. Whereas via
the userform the 2 fields of data appears as dd/mm/yyyy
when I enter them directly they appear as a 5 digit number
which I assume is what Excel needs. Presumably when data
is enterd via my Sheets("AutumnHT1").Cells(2, 1) =
UserForm1.TB_A1_SDt.Text it does not recognise it as a
date!

I tried Sheets("AutumnHT1").Cells(2, 1).Formula =
UserForm1.TB_A1_SDt.Text but it made no difference

 

[Guest]

ok lets try this again.
Sheets("AutumnHT1").Cells(2, 1).Formula =
CvDate( UserForm1.TB_A1_SDt.Text)
You need to make sure the format of the data thats placed in the cell is recognizable as a date
How are you validating that the value is a valid Date format?
----- Brian C wrote: ----

I'm trying to create a date range in Excel in a column on
a worksheet I use a userform to enter a start and end
date, copy this data to locations on a worksheet in a
worksheet (in cell A2 and a cell called
A1Enddate respectively on the AutumnHT1 workshet)

I execute the following code when they hit the FINISH
button on the userform

Sub Macro3(

' Macro3 Macr
' Macro recorded 15/04/2004 by blcutle

Sheets("AutumnHT1").Selec

Range("A2").Selec

Selection.DataSeries Rowcol:=xlColumns,
Type:=xlChronological, Date:=
xlWeekday, Step:=1, Stop:=Range("A1Enddate"), Trend:=Fals

End Su


I get the following error

Run time error 100

DataSeries method of range class faile

Any ideas of what may be happening? There is defintely
data put in the cells (A2 and A1Enddate) on AutumnHT1 by
the userform. The Userform executes on a different sheet
hence the Sheets("AutumnHT1").Selec

The macro works if I enter dates directly into the cells
and then run it rather than using a userform. Whereas via
the userform the 2 fields of data appears as dd/mm/yyyy
when I enter them directly they appear as a 5 digit number
which I assume is what Excel needs. Presumably when data
is enterd via my Sheets("AutumnHT1").Cells(2, 1) =
UserForm1.TB_A1_SDt.Text it does not recognise it as a
date

I tried Sheets("AutumnHT1").Cells(2, 1).Formula =
UserForm1.TB_A1_SDt.Text but it made no differenc

 

[Guest]

That works. I don't know how to val;idate the dates but
I've set the fields up on the userform that it rejects
anything that isn't a date. Problem is that it comes up
with the VBA popup allowing you to get into ebug etc. Not
to user friendly. I believe that there is a way around
this to redisplay the userform with a message in the title.

How should I validate dates?

Brian

-----Original Message-----
ok lets try this again.
Sheets("AutumnHT1").Cells(2, 1).Formula =
CvDate( UserForm1.TB_A1_SDt.Text)
You need to make sure the format of the data thats

placed in the cell is recognizable as a date.

 

[Guest]

Inyour textboxe's Exit event type put this in:

if Not IsDate(Me.TB_A1_SDt.text) Then msgbox("Must be a valid Date!): Me.TB_A1_SDt.text = ""

----- (e-mail address removed) wrote: -----

That works. I don't know how to val;idate the dates but
I've set the fields up on the userform that it rejects
anything that isn't a date. Problem is that it comes up
with the VBA popup allowing you to get into ebug etc. Not
to user friendly. I believe that there is a way around
this to redisplay the userform with a message in the title.

How should I validate dates?

Brian

-----Original Message-----
ok lets try this again.
Sheets("AutumnHT1").Cells(2, 1).Formula =
CvDate( UserForm1.TB_A1_SDt.Text)
You need to make sure the format of the data thats

placed in the cell is recognizable as a date.