I Have a UserForm with many identical TextBoxes. I need to
populate them with varying types of format (ie backcolor,
forecolor, fontcolor etc) How can I do so, perhaps with
a "For Each" loop, rather than writing a separate code
block for each one.
I have tried things like:-
"For each TextBox in UserForm1"...... and
"For Count = 8 to 49 step 1
TextBox(Count).BackColor = something"
but either get "method not supported" or my syntax is
wrong.
Can anyone suggest code which will do this and if so,
where would it go - ie in a separate module, or in the
code for the TextBox and if the latter would the
Subroutine be a "Sub Initialise_???"
Any ideas would be greatly appreciated.
Don

Hi Don,

here is how you can access all the textboxes

Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.BackColor = RGB(&H80, &H80, &H80)
End If
Next ctl

as to where to put it, that is more difficult. You need to determine which
event will change the textbox properties and then ad the code at that point.
There is no 'best' place.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don" wrote in message
...
I Have a UserForm with many identical TextBoxes. I need to
populate them with varying types of format (ie backcolor,
forecolor, fontcolor etc) How can I do so, perhaps with
a "For Each" loop, rather than writing a separate code
block for each one.
I have tried things like:-
"For each TextBox in UserForm1"...... and
"For Count = 8 to 49 step 1
TextBox(Count).BackColor = something"
but either get "method not supported" or my syntax is
wrong.
Can anyone suggest code which will do this and if so,
where would it go - ie in a separate module, or in the
code for the TextBox and if the latter would the
Subroutine be a "Sub Initialise_???"
Any ideas would be greatly appreciated.
Don

If the colours are not likely to change again, UserForm_Initialize() is an
OK place to put it.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Bob Phillips" wrote in message
...
Hi Don,

here is how you can access all the textboxes

Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
ctl.BackColor = RGB(&H80, &H80, &H80)
End If
Next ctl

as to where to put it, that is more difficult. You need to determine which
event will change the textbox properties and then ad the code at that
point.
There is no 'best' place.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Don" wrote in message
...
I Have a UserForm with many identical TextBoxes. I need to
populate them with varying types of format (ie backcolor,
forecolor, fontcolor etc) How can I do so, perhaps with
a "For Each" loop, rather than writing a separate code
block for each one.
I have tried things like:-
"For each TextBox in UserForm1"...... and
"For Count = 8 to 49 step 1
TextBox(Count).BackColor = something"
but either get "method not supported" or my syntax is
wrong.
Can anyone suggest code which will do this and if so,
where would it go - ie in a separate module, or in the
code for the TextBox and if the latter would the
Subroutine be a "Sub Initialise_???"
Any ideas would be greatly appreciated.
Don