Author Topic: "(foreach" equivalent in VBA UserForm?  (Read 7521 times)

0 Members and 1 Guest are viewing this topic.

ScottBolton

  • Guest
"(foreach" equivalent in VBA UserForm?
« on: April 03, 2013, 02:48:06 PM »
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

  • Seagull
  • Posts: 12954
  • I like my water diluted.
Re: "(foreach" equivalent in VBA UserForm?
« Reply #1 on: April 03, 2013, 03:33:10 PM »
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
Autodesk Expert Elite
Revit Subject Matter Expert (SME)
Owner/FAA sUAS Pilot @ http://skyviz.io

ScottBolton

  • Guest
Re: "(foreach" equivalent in VBA UserForm?
« Reply #2 on: April 03, 2013, 04:31:46 PM »
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

  • Seagull
  • Posts: 12954
  • I like my water diluted.
Re: "(foreach" equivalent in VBA UserForm?
« Reply #3 on: April 03, 2013, 04:51:34 PM »
Try commenting out the PROCEED.SETFOCUS line and see what happens.
Autodesk Expert Elite
Revit Subject Matter Expert (SME)
Owner/FAA sUAS Pilot @ http://skyviz.io

ScottBolton

  • Guest
Re: "(foreach" equivalent in VBA UserForm?
« Reply #4 on: April 03, 2013, 04:58:18 PM »
Same thing.

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #5 on: April 03, 2013, 05:08:21 PM »
can you post your dvb?
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #6 on: April 03, 2013, 05:20:45 PM »
Did you assign a group name to the OPs?  I had to assign a name to make it work
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #7 on: April 03, 2013, 05:21:45 PM »
A quick test allowed me to select them all and change the properties for them all at once
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #8 on: April 03, 2013, 05:22:18 PM »
then this worked

Code: [Select]
For Each x In Me.Controls
If x.GroupName = "Test" Then
MsgBox "Found it"
End If
Next x
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #9 on: April 03, 2013, 05:24:52 PM »
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
Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second

ScottBolton

  • Guest
Re: "(foreach" equivalent in VBA UserForm?
« Reply #10 on: April 03, 2013, 05:49:10 PM »
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

  • Bull Frog
  • Posts: 439
Re: "(foreach" equivalent in VBA UserForm?
« Reply #11 on: April 03, 2013, 11:49:33 PM »
... 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

  • Guest
Re: "(foreach" equivalent in VBA UserForm?
« Reply #12 on: April 04, 2013, 03:02:39 AM »
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

  • Guest
Re: "(foreach" equivalent in VBA UserForm?
« Reply #13 on: April 04, 2013, 03:11:03 AM »
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
« Last Edit: April 04, 2013, 06:34:20 AM by ScottBolton »

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #14 on: April 04, 2013, 09:58:54 AM »
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



Everyone has a photographic memory, Some just don't have film.
They say money can't buy happiness, but it can buy Bacon and that's a close second