With....End With

Nomis_Eswod

Board Regular
Joined
Jul 27, 2005
Messages
153
Hi,

I know you can use With...End With to reduce the amount of times you have to write something like TextBox1.BackColor = ....., TextBox1.Value = ..... So you just do..

Code:
With TextBox1
.BackColor = .....
.Value = .....
End With

But I actually want to do it the other way round. I.e. with TexBoxes 1-12 I want to change the backcolor and lock them all. Reading the help it would appear you cannot nest multiple controls within a With statement, do you know if there is any other way of doing it so I don't have to write With...End With statements 12 times??

Many thanks!
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
Hi Simon

You should be able to do a For Each........Next with this i.e.

For each textbox in (whatever the textbox collection is called)
with textbox
. backcolor =
.locked = true
etc etc
end with

next textbox

You'll have to substitute in the correct references to the textbox control, and to the collection, but that's the principal behind it.
 

Nomis_Eswod

Board Regular
Joined
Jul 27, 2005
Messages
153
Many thanks for this. This is what I have so far...

Code:
Dim myBoxes As Collection

With myBoxes
.Add (TextBox1)
.Add (TextBox2)
.Add (TextBox3)
.Add (TextBox4)
.Add (ComboBox2)
.Add (TextBox15)
.Add (TextBox16)
.Add (TextBox14)
.Add (TextBox9)
.Add (TextBox10)
.Add (TextBox12)
.Add (TextBox13)
.Add (TextBox17)
End With

If ComboBox2.Value = "Closed" Then
    For Each TextBox In myBoxes
    With TextBox
    .BackColor = &H8000000F
    .Locked = True
    End With
    Exit For
    Next
Else
    For Each TextBox In myBoxes
    With TextBox
    .BackColor = &H80000005
    .Locked = False
    End With
    Exit For
    Next
End If

But this is debugging....where am I going wrong?[/code]
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
I've not worked with collections that much, can anyone else step in here?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,144
Messages
5,576,330
Members
412,716
Latest member
thviid
Top