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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,884
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,884
Office Version
  1. 365
Platform
  1. Windows
I've not worked with collections that much, can anyone else step in here?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,632
Messages
5,832,772
Members
430,166
Latest member
Nige Leeming

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top