| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format Thingymabob
Hi All
This is driving me crazy -- I have used Bob Phillips Date SpinButton Sub which I copied off this forum see code below. Private Sub SB3_Spinup() SD = Date If Tb6.Value = "" Then Tb6.Value = Format(Date, "dd/mm/yy") Else If Not Tb6.Value = "" Then SD2 = DateAdd("d", 1, CDate(Tb6.Value)) Tb6.Value = Format(SD2, "dd/mm/yy") End If End If End Sub Now Bob always gets it right for us UK residents but I need help with this one. Enter the date 04/07/07 in the TextBox -- in the Cell on the Sheet it displays 07/04/07 USA style Enter the date 13/07/07 in the TextBox -- in the Cell on the Sheet it displays 13/07/07 UK style which is what I need being resident in the UK. As soon as the date passes the 12th day of every month it displays UK style in the Cell. Have tried formatting the Cell as Date and as Text and always get the same result -- have tried changing the TextBox from Value to Text still the same problem occurs. Any help much appreciated -- using Excel 2007 and XP Pro. -- Many Thanks Sue |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format Thingymabob
Sue,
Have you got the textbox bound to the cell via ControlSource? If so, perhaps remove that binding, and add Range("M1").Value = CDate(Tb6.Text) after that code. BTW, have you seen you have two variables, SD and SD2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi All This is driving me crazy -- I have used Bob Phillips Date SpinButton Sub which I copied off this forum see code below. Private Sub SB3_Spinup() SD = Date If Tb6.Value = "" Then Tb6.Value = Format(Date, "dd/mm/yy") Else If Not Tb6.Value = "" Then SD2 = DateAdd("d", 1, CDate(Tb6.Value)) Tb6.Value = Format(SD2, "dd/mm/yy") End If End If End Sub Now Bob always gets it right for us UK residents but I need help with this one. Enter the date 04/07/07 in the TextBox -- in the Cell on the Sheet it displays 07/04/07 USA style Enter the date 13/07/07 in the TextBox -- in the Cell on the Sheet it displays 13/07/07 UK style which is what I need being resident in the UK. As soon as the date passes the 12th day of every month it displays UK style in the Cell. Have tried formatting the Cell as Date and as Text and always get the same result -- have tried changing the TextBox from Value to Text still the same problem occurs. Any help much appreciated -- using Excel 2007 and XP Pro. -- Many Thanks Sue |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format Thingymabob
Hi Bob
I apologise for the delay in answering been watching the Open Golf and then dog walking. How I missed the SD and SD2 variables must be getting short sighted, however if I change all of Row1 to Text Format at the first time of asking the date is correct in "I1" with all the other data in the column. But if I enter the date again the date in "I1" along with all the data is moved to column "J" and the new date enters "I1" and at the same time the Format of Text on Row1 goes to General and it all goes haywire again. It has to be bits and pieces of coding for inserting a Column that I have used off this forum and perhaps if I physically enter the date each time it will keep on working - trouble is there are 20 odd Sheets in the WB. Once again thanks for your help -- might start again tomorrow - DEFRA insist we retain the data for six months - but they don't write the code for us to be able to do it. -- Many Thanks Sue "Bob Phillips" wrote: Sue, Have you got the textbox bound to the cell via ControlSource? If so, perhaps remove that binding, and add Range("M1").Value = CDate(Tb6.Text) after that code. BTW, have you seen you have two variables, SD and SD2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi All This is driving me crazy -- I have used Bob Phillips Date SpinButton Sub which I copied off this forum see code below. Private Sub SB3_Spinup() SD = Date If Tb6.Value = "" Then Tb6.Value = Format(Date, "dd/mm/yy") Else If Not Tb6.Value = "" Then SD2 = DateAdd("d", 1, CDate(Tb6.Value)) Tb6.Value = Format(SD2, "dd/mm/yy") End If End If End Sub Now Bob always gets it right for us UK residents but I need help with this one. Enter the date 04/07/07 in the TextBox -- in the Cell on the Sheet it displays 07/04/07 USA style Enter the date 13/07/07 in the TextBox -- in the Cell on the Sheet it displays 13/07/07 UK style which is what I need being resident in the UK. As soon as the date passes the 12th day of every month it displays UK style in the Cell. Have tried formatting the Cell as Date and as Text and always get the same result -- have tried changing the TextBox from Value to Text still the same problem occurs. Any help much appreciated -- using Excel 2007 and XP Pro. -- Many Thanks Sue |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format Thingymabob
So Sue, I am not clear here. Does my suggestion solve the problem, or do you
still need assistance? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi Bob I apologise for the delay in answering been watching the Open Golf and then dog walking. How I missed the SD and SD2 variables must be getting short sighted, however if I change all of Row1 to Text Format at the first time of asking the date is correct in "I1" with all the other data in the column. But if I enter the date again the date in "I1" along with all the data is moved to column "J" and the new date enters "I1" and at the same time the Format of Text on Row1 goes to General and it all goes haywire again. It has to be bits and pieces of coding for inserting a Column that I have used off this forum and perhaps if I physically enter the date each time it will keep on working - trouble is there are 20 odd Sheets in the WB. Once again thanks for your help -- might start again tomorrow - DEFRA insist we retain the data for six months - but they don't write the code for us to be able to do it. -- Many Thanks Sue "Bob Phillips" wrote: Sue, Have you got the textbox bound to the cell via ControlSource? If so, perhaps remove that binding, and add Range("M1").Value = CDate(Tb6.Text) after that code. BTW, have you seen you have two variables, SD and SD2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi All This is driving me crazy -- I have used Bob Phillips Date SpinButton Sub which I copied off this forum see code below. Private Sub SB3_Spinup() SD = Date If Tb6.Value = "" Then Tb6.Value = Format(Date, "dd/mm/yy") Else If Not Tb6.Value = "" Then SD2 = DateAdd("d", 1, CDate(Tb6.Value)) Tb6.Value = Format(SD2, "dd/mm/yy") End If End If End Sub Now Bob always gets it right for us UK residents but I need help with this one. Enter the date 04/07/07 in the TextBox -- in the Cell on the Sheet it displays 07/04/07 USA style Enter the date 13/07/07 in the TextBox -- in the Cell on the Sheet it displays 13/07/07 UK style which is what I need being resident in the UK. As soon as the date passes the 12th day of every month it displays UK style in the Cell. Have tried formatting the Cell as Date and as Text and always get the same result -- have tried changing the TextBox from Value to Text still the same problem occurs. Any help much appreciated -- using Excel 2007 and XP Pro. -- Many Thanks Sue |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format Thingymabob
Hi Bob
It was not bound to the Control Source -- however been having a go at the Range that you suggested --- Range("M1").Value = CDate(Tb6.Text) changed it to ("I1") and at the moment seems to be working OK -- was probably in to much of a rush yesterday evening -- when something like this is bugging me can't relax and the Golf kept on catching my attention like it will later today. Shouldn't bring work home at weekends. Can I just ask another question the following =SUM(J.Bloggs:A.Smith!A100) adds all the sheets totals to Cell A100 How would I code it to put a value from a Textbox into Cell Z100 on the all the sheets between J.Bloggs:A.Smith Again many thanks for your help -- Many Thanks Sue "Bob Phillips" wrote: So Sue, I am not clear here. Does my suggestion solve the problem, or do you still need assistance? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi Bob I apologise for the delay in answering been watching the Open Golf and then dog walking. How I missed the SD and SD2 variables must be getting short sighted, however if I change all of Row1 to Text Format at the first time of asking the date is correct in "I1" with all the other data in the column. But if I enter the date again the date in "I1" along with all the data is moved to column "J" and the new date enters "I1" and at the same time the Format of Text on Row1 goes to General and it all goes haywire again. It has to be bits and pieces of coding for inserting a Column that I have used off this forum and perhaps if I physically enter the date each time it will keep on working - trouble is there are 20 odd Sheets in the WB. Once again thanks for your help -- might start again tomorrow - DEFRA insist we retain the data for six months - but they don't write the code for us to be able to do it. -- Many Thanks Sue "Bob Phillips" wrote: Sue, Have you got the textbox bound to the cell via ControlSource? If so, perhaps remove that binding, and add Range("M1").Value = CDate(Tb6.Text) after that code. BTW, have you seen you have two variables, SD and SD2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi All This is driving me crazy -- I have used Bob Phillips Date SpinButton Sub which I copied off this forum see code below. Private Sub SB3_Spinup() SD = Date If Tb6.Value = "" Then Tb6.Value = Format(Date, "dd/mm/yy") Else If Not Tb6.Value = "" Then SD2 = DateAdd("d", 1, CDate(Tb6.Value)) Tb6.Value = Format(SD2, "dd/mm/yy") End If End If End Sub Now Bob always gets it right for us UK residents but I need help with this one. Enter the date 04/07/07 in the TextBox -- in the Cell on the Sheet it displays 07/04/07 USA style Enter the date 13/07/07 in the TextBox -- in the Cell on the Sheet it displays 13/07/07 UK style which is what I need being resident in the UK. As soon as the date passes the 12th day of every month it displays UK style in the Cell. Have tried formatting the Cell as Date and as Text and always get the same result -- have tried changing the TextBox from Value to Text still the same problem occurs. Any help much appreciated -- using Excel 2007 and XP Pro. -- Many Thanks Sue |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format Thingymabob
You would have to run a loop
Dim i As Long For i = Worksheets("J.Bloggs").Index To Worksheets("A.Smith").Index Worksheets(i).Range("A100").Value = TextBox1.Text Next i -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi Bob It was not bound to the Control Source -- however been having a go at the Range that you suggested --- Range("M1").Value = CDate(Tb6.Text) changed it to ("I1") and at the moment seems to be working OK -- was probably in to much of a rush yesterday evening -- when something like this is bugging me can't relax and the Golf kept on catching my attention like it will later today. Shouldn't bring work home at weekends. Can I just ask another question the following =SUM(J.Bloggs:A.Smith!A100) adds all the sheets totals to Cell A100 How would I code it to put a value from a Textbox into Cell Z100 on the all the sheets between J.Bloggs:A.Smith Again many thanks for your help -- Many Thanks Sue "Bob Phillips" wrote: So Sue, I am not clear here. Does my suggestion solve the problem, or do you still need assistance? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi Bob I apologise for the delay in answering been watching the Open Golf and then dog walking. How I missed the SD and SD2 variables must be getting short sighted, however if I change all of Row1 to Text Format at the first time of asking the date is correct in "I1" with all the other data in the column. But if I enter the date again the date in "I1" along with all the data is moved to column "J" and the new date enters "I1" and at the same time the Format of Text on Row1 goes to General and it all goes haywire again. It has to be bits and pieces of coding for inserting a Column that I have used off this forum and perhaps if I physically enter the date each time it will keep on working - trouble is there are 20 odd Sheets in the WB. Once again thanks for your help -- might start again tomorrow - DEFRA insist we retain the data for six months - but they don't write the code for us to be able to do it. -- Many Thanks Sue "Bob Phillips" wrote: Sue, Have you got the textbox bound to the cell via ControlSource? If so, perhaps remove that binding, and add Range("M1").Value = CDate(Tb6.Text) after that code. BTW, have you seen you have two variables, SD and SD2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi All This is driving me crazy -- I have used Bob Phillips Date SpinButton Sub which I copied off this forum see code below. Private Sub SB3_Spinup() SD = Date If Tb6.Value = "" Then Tb6.Value = Format(Date, "dd/mm/yy") Else If Not Tb6.Value = "" Then SD2 = DateAdd("d", 1, CDate(Tb6.Value)) Tb6.Value = Format(SD2, "dd/mm/yy") End If End If End Sub Now Bob always gets it right for us UK residents but I need help with this one. Enter the date 04/07/07 in the TextBox -- in the Cell on the Sheet it displays 07/04/07 USA style Enter the date 13/07/07 in the TextBox -- in the Cell on the Sheet it displays 13/07/07 UK style which is what I need being resident in the UK. As soon as the date passes the 12th day of every month it displays UK style in the Cell. Have tried formatting the Cell as Date and as Text and always get the same result -- have tried changing the TextBox from Value to Text still the same problem occurs. Any help much appreciated -- using Excel 2007 and XP Pro. -- Many Thanks Sue |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Date Format Thingymabob
Hi Bob
Wonderful Golf and a great result for you helping me to solve my DEFRA ( its all about injections for livestock) Much appreciated -- Many Thanks Sue "Bob Phillips" wrote: You would have to run a loop Dim i As Long For i = Worksheets("J.Bloggs").Index To Worksheets("A.Smith").Index Worksheets(i).Range("A100").Value = TextBox1.Text Next i -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi Bob It was not bound to the Control Source -- however been having a go at the Range that you suggested --- Range("M1").Value = CDate(Tb6.Text) changed it to ("I1") and at the moment seems to be working OK -- was probably in to much of a rush yesterday evening -- when something like this is bugging me can't relax and the Golf kept on catching my attention like it will later today. Shouldn't bring work home at weekends. Can I just ask another question the following =SUM(J.Bloggs:A.Smith!A100) adds all the sheets totals to Cell A100 How would I code it to put a value from a Textbox into Cell Z100 on the all the sheets between J.Bloggs:A.Smith Again many thanks for your help -- Many Thanks Sue "Bob Phillips" wrote: So Sue, I am not clear here. Does my suggestion solve the problem, or do you still need assistance? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi Bob I apologise for the delay in answering been watching the Open Golf and then dog walking. How I missed the SD and SD2 variables must be getting short sighted, however if I change all of Row1 to Text Format at the first time of asking the date is correct in "I1" with all the other data in the column. But if I enter the date again the date in "I1" along with all the data is moved to column "J" and the new date enters "I1" and at the same time the Format of Text on Row1 goes to General and it all goes haywire again. It has to be bits and pieces of coding for inserting a Column that I have used off this forum and perhaps if I physically enter the date each time it will keep on working - trouble is there are 20 odd Sheets in the WB. Once again thanks for your help -- might start again tomorrow - DEFRA insist we retain the data for six months - but they don't write the code for us to be able to do it. -- Many Thanks Sue "Bob Phillips" wrote: Sue, Have you got the textbox bound to the cell via ControlSource? If so, perhaps remove that binding, and add Range("M1").Value = CDate(Tb6.Text) after that code. BTW, have you seen you have two variables, SD and SD2? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Hi All This is driving me crazy -- I have used Bob Phillips Date SpinButton Sub which I copied off this forum see code below. Private Sub SB3_Spinup() SD = Date If Tb6.Value = "" Then Tb6.Value = Format(Date, "dd/mm/yy") Else If Not Tb6.Value = "" Then SD2 = DateAdd("d", 1, CDate(Tb6.Value)) Tb6.Value = Format(SD2, "dd/mm/yy") End If End If End Sub Now Bob always gets it right for us UK residents but I need help with this one. Enter the date 04/07/07 in the TextBox -- in the Cell on the Sheet it displays 07/04/07 USA style Enter the date 13/07/07 in the TextBox -- in the Cell on the Sheet it displays 13/07/07 UK style which is what I need being resident in the UK. As soon as the date passes the 12th day of every month it displays UK style in the Cell. Have tried formatting the Cell as Date and as Text and always get the same result -- have tried changing the TextBox from Value to Text still the same problem occurs. Any help much appreciated -- using Excel 2007 and XP Pro. -- Many Thanks Sue |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
| How do I convert dd/mm/yy date format to yyddd Julian date format | Excel Worksheet Functions | |||
| convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
| code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
| Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming | |||
Linear Mode
