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" <(E-Mail Removed)> wrote in message
news:F73A2C0C-EB33-4BD2-B550-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:5587AE13-5657-4D94-9F8B-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:46D66ACA-F716-4BA1-A901-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>
|
Similar Threads
