for Looping in VBA/Form

[Excel 2003 - SPB]

I have many areas of code that could be cleaned if I only knew how
I think it would be a "For" statement in both cases
example 1
Pax1.Caption = Range("d5")
Pax2.Caption = Range("d6")
Pax3.Caption = Range("d7")
etc
example 2
TextBox31.Visible = False
TextBox32.Visible = False
TextBox33.Visible = False

I tried this

For i = 1 To 6

Seat " & i &.Caption = Range(""a" & i + l2 & """)"
next i

with no luck, I get a compile error highlighting "Seat"

Many thanks for any help

[Toppers]

For i = 1 To 3
Controls("Pax" & i).Caption = Range("d" & i+4)
Next i


For i = 31 To 33
Controls("Textbox" & i).Visible = False
Next i

HTH

"Excel 2003 - SPB" wrote:

> I have many areas of code that could be cleaned if I only knew how
> I think it would be a "For" statement in both cases
> example 1
> Pax1.Caption = Range("d5")
> Pax2.Caption = Range("d6")
> Pax3.Caption = Range("d7")
> etc
> example 2
> TextBox31.Visible = False
> TextBox32.Visible = False
> TextBox33.Visible = False
>
> I tried this
>
> For i = 1 To 6
>
> Seat " & i &.Caption = Range(""a" & i + l2 & """)"
> next i
>
> with no luck, I get a compile error highlighting "Seat"
>
> Many thanks for any help

[Bob Phillips]

For i = 1 To 10
me.Controls("Pax" & i).Caption = Range("D" & i + 4).Value
Next i

etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Excel 2003 - SPB" <[email protected]> wrote in message
news:[email protected]...
> I have many areas of code that could be cleaned if I only knew how
> I think it would be a "For" statement in both cases
> example 1
> Pax1.Caption = Range("d5")
> Pax2.Caption = Range("d6")
> Pax3.Caption = Range("d7")
> etc
> example 2
> TextBox31.Visible = False
> TextBox32.Visible = False
> TextBox33.Visible = False
>
> I tried this
>
> For i = 1 To 6
>
> Seat " & i &.Caption = Range(""a" & i + l2 & """)"
> next i
>
> with no luck, I get a compile error highlighting "Seat"
>
> Many thanks for any help

[Dave Peterson]

Maybe...

dim i as long

for i = 1 to 3
me.controls("Pax" & i).caption = cells(i+4,"D").value
next i

for i = 31 to 33
me.controls("textbox" & i).visible = false
next i

But I don't see how either example comes close to the stuff you tried. Where
does Seat fit in?

Excel 2003 - SPB wrote:
>
> I have many areas of code that could be cleaned if I only knew how
> I think it would be a "For" statement in both cases
> example 1
> Pax1.Caption = Range("d5")
> Pax2.Caption = Range("d6")
> Pax3.Caption = Range("d7")
> etc
> example 2
> TextBox31.Visible = False
> TextBox32.Visible = False
> TextBox33.Visible = False
>
> I tried this
>
> For i = 1 To 6
>
> Seat " & i &.Caption = Range(""a" & i + l2 & """)"
> next i
>
> with no luck, I get a compile error highlighting "Seat"
>
> Many thanks for any help

--

Dave Peterson

[Excel 2003 - SPB]

"Toppers" wrote:

>
>
> For i = 1 To 3
> Controls("Pax" & i).Caption = Range("d" & i+4)
> Next i
>
>
> For i = 31 To 33
> Controls("Textbox" & i).Visible = False
> Next i
>
> HTH
>
> "Excel 2003 - SPB" wrote:
>
> > I have many areas of code that could be cleaned if I only knew how
> > I think it would be a "For" statement in both cases
> > example 1
> > Pax1.Caption = Range("d5")
> > Pax2.Caption = Range("d6")
> > Pax3.Caption = Range("d7")
> > etc
> > example 2
> > TextBox31.Visible = False
> > TextBox32.Visible = False
> > TextBox33.Visible = False
> >
> > I tried this
> >
> > For i = 1 To 6
> >
> > Seat " & i &.Caption = Range(""a" & i + l2 & """)"
> > next i
> >
> > with no luck, I get a compile error highlighting "Seat"
> >
> > Many thanks for any help


Great! I knew it would be simple
Thanks to both of you