Syntax for For each ctrl in

[AlexanderBB]

Having trouble applying a name to the line

For Each ctrl In Form1.Controls

The name of my Form is "frmLayout"

How can I change Form1.Controls to "frmLayout".Controls ?

ROFL please don't tell me just to type in it without the quotes, I mean when the name is in a string variable. Thanks.

 

[AD_Taylor]

Use something like:

VBA.UserForms.Add(strVariable).Controls

Or if you're inside the UserForm with this code you could use

Me.Controls

 

[AlexanderBB]

Thanks, that does work, but it runs each Forms Initialize event.
Can that be avoided anyhow?

 

[AD_Taylor]

Probably not with the code as it is.

Only thing I can think of is to switch from using Initialize to using a Display sub instead. Here's an example.

Normally you might use 'UserForm1.Show' and have the following code in that UserForm:

Option Explicit


Private Sub UserForm_Initialize()
    Label1.Caption = "Hello World"
    TextBox1.Value = "Type your name"
End Sub


Private Sub CommandButton1_Click()
    MsgBox "Hello " & TextBox1.Value
End Sub

As you say the Initialize event would fire.
If we remove the Initialize event and instead do all the initial set up with a Public Sub inside the UserForm we still get the same functionality but the Initialize event can't fire because it is not there.

So you would show this UserForm using UserForm1.Display with the code below:

Option Explicit


Public Sub Display()
    Label1.Caption = "Hello World"
    TextBox1.Value = "Type your name"
    
    Me.Show
End Sub


Private Sub CommandButton1_Click()
    MsgBox "Hello " & TextBox1.Value
End Sub

As you can see the Display sub incorporates the .Show command after it is finished initialising.
Tested this and it also works with 'VBA.UserForms.Add(strVariable).Display'

Hopefully you can apply this logic to your example and therefore bypass the Initialize event.
HTH!

 

[AlexanderBB]

Hi,

That's great! I did find another way to get it done without Initialise running, but your suggestion
is very welcome as I can see other uses. One especially - I've found under some (unknown) situations
the Initialise (or Activate) events don't fire consistently and this would solve that problem.

Many thanks