Topic: "(foreach" equivalent in VBA UserForm?

[ScottBolton]

Is there one?

I have 20 OptionButtons - OP01, OP02... OP20 - and I want to disable them all. Is there a way to do this without using:

OP01.Enabled = False
OP02.Enabled = False
...
OP20.Enabled = False

S

[Matt__W]

Select all of your OptionButtons and in the Properties window find the GROUPNAME property.  Give it a value (in the example below I called it 'Buttons').  The code below will disable all OptionButtons whose GroupName property equals "Buttons".

Code - vb.net: [Select]

1.     Dim x As OptionButton
2.     For Each x In Me.Controls
3.         If x.GroupName = "Buttons" Then
4.             x.Enabled = False
5.         End If
6.     Next x

[ScottBolton]

Matt, thanks for the speedy reply. The code looks good (and simple) but I get a Type mismatch on the For Each line. Any idea why? I'm using it like this:

Code - vb.net: [Select]

1. Private Sub SizeBIG_Click()
2.     Dim x As OptionButton
3.     For Each x In Me.Controls
4.         If x.GroupName = "Buttons" Then
5.             x.Enabled = False
6.         End If
7.     Next x
8.     Proceed.SetFocus
9. End Sub

S

[Matt__W]

Try commenting out the PROCEED.SETFOCUS line and see what happens.

[ScottBolton]

Same thing.

[David Hall]

can you post your dvb?

[David Hall]

Did you assign a group name to the OPs?  I had to assign a name to make it work

[David Hall]

A quick test allowed me to select them all and change the properties for them all at once

[David Hall]

then this worked

Code: [Select]

For Each x In Me.Controls
If x.GroupName = "Test" Then
MsgBox "Found it"
End If
Next x

[David Hall]

Next question, and it may not matter, but you could grab them all and set to false in the designer.  Are you wanting them all tied together so that if you pick one the rest are disabled?  Are there more than one group?  if so, you have to group them together so a choice in group one does not prevent a choice in group 2

[ScottBolton]

Phew! Fast fingers.

Ok, a few answers:

Here's a simplified dvb. You'll see that on the left I have standard sheet sizes; on the right I have additional sizes.

My idea is that if I change the standard size the additional ones initially are disabled then I will enable only the ones that are relevant to that standard size. At present only a couple of the additional buttons are grouped in "Buttons" and only SizeA2 tries the For Each.

So, I could set everything to disabled in the designer but I still need a quickie for when the standard size changes. At present there is only one group so nothing should be interfering.

Hopefully you should be able to glean something from this...?

S

[owenwengerd]

... I get a Type mismatch on the For Each line. Any idea why?

Your x variable needs to be a type that can hold Control objects. Try typing it as Control instead of OptionButton.

[ScottBolton]

That bit worked. Now I get the error on the line,

Code - vb.net: [Select]

1. If x.GroupName = "Buttons" Then

- "Object doesn't support this property or method".

S

[ScottBolton]

Ok,

I tried this:

Code - vb.net: [Select]

1. Private Sub SizeA2_Click()
2.     Dim x As Control
3.     For Each x In Me.Controls
4.         If x.Name = "Length06" Then
5.            If x.GroupName = "Buttons" Then
6.                x.Enabled = False
7.            End If
8.         End If
9.     Next x
10.     Proceed.SetFocus
11. End Sub

Putting in a check on Line 4 that I have the right button. Of course, I would need to say If x.Name = "Length01, Length02..." which sort of defeats the porpoise. But, it shows that when the code is looping through ALL the Controls it finds one of them that doesn't support the GroupName property.

So, how to (efficiently) select only OptionButtons? Is there a 'Type' property? Or even Controls that start with 'Length...'?

S

[David Hall]

OK, I would make a couple of changes.  In your Proceed_Click, I would change the IF-ElseIf s to a Select Case

Code: [Select]

Select Case True
Case SizeA0.Value
Print #FileNumber, "0"
Case SizeA1.Value
Print #FileNumber, "1"
Case SizeA2.Value
Print #FileNumber, "2"
Case SizeA3.Value
Print #FileNumber, "3"
Case SizeA4.Value
Print #FileNumber, "4"
End Select


This is easier to follow than nested IF statements.  Select will eval each "Case" until it finds one that is true.


I would do the same for the Length ElseIfs


Another change I made was to Group all the Size buttons together in one GroupName and group the Lengths as well under another name. (see picture).  This will allow the SizeA2_Click code you have to work (Which I changed to the new group names)




As this was simplified, I couldn't test fully, but it should get you going