Date Format Thingymabob

[Sue]

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.

 

[Bob Phillips]

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]

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

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)

 

[Bob Phillips]

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)

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.

 

[Sue]

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

 

[Bob Phillips]

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]

Hi Bob

Wonderful Golf and a great result for you helping me to solve my DEFRA ( its
all about injections for livestock)

Much appreciated