Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with dates entered via userform
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with dates entered via userform
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with dates entered via userform
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. 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 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with dates entered via userform
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop based on number entered in userform | Excel Worksheet Functions | |||
how can i re-populate a userform with data already entered? | New Users to Excel | |||
Problem with using dates entered via UserForm | Excel Programming | |||
UserForm Problems | Excel Programming | |||
More UserForm problems | Excel Programming |