Author Topic: "(foreach" equivalent in VBA UserForm?  (Read 7697 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

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #15 on: April 04, 2013, 09:59:41 AM »
and here is the dvb back
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 #16 on: April 04, 2013, 10:01:20 AM »
One other thing i thought of is you can use the OptionButton.Changed to reset and select the ones you want to enable, that way if a user clicks around, you can control what options are available with each click
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

Matt__W

  • Seagull
  • Posts: 12954
  • I like my water diluted.
Re: "(foreach" equivalent in VBA UserForm?
« Reply #17 on: April 04, 2013, 10:20:29 AM »
Looks like I'm late to the party.   :-)
Autodesk Expert Elite
Revit Subject Matter Expert (SME)
Owner/FAA sUAS Pilot @ http://skyviz.io

ScottBolton

  • Guest
Re: "(foreach" equivalent in VBA UserForm?
« Reply #18 on: April 04, 2013, 12:36:23 PM »
It's been a while since I dabbled in VBA; I remember now about Case - I'll give it a bash.

Your suggestions appear to work on paper, but not in real life! I still have Controls that don't support the GroupName property (I think the problem is with the CommandButtons). If I strip everything out except the OptionButtons it works fine - see the attached image. So, how do I select only the OptionButtons, or how do I filter out the others with an If?

S


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

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #19 on: April 04, 2013, 01:16:47 PM »
I'm confused what isn't working?  If you select A2, it disables all the LengthGroup.  You would still have to follow that up with enabling the ones you want to make available.  If you can, write up what should happen if A2 is selected, and then if A1 is selected after that.

As far as command buttons, I didn't think those affected the option buttons
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 #20 on: April 04, 2013, 01:37:00 PM »
Sorry for the confusion!

The image in my previous post shows what should be happening, i.e. clicking a Size disables ALL Lengths. I'll later add code that will enable individual Lengths based on which Size is selected. So, it's working fine.

However, when the UserForm contains a Control that doesn't support the GroupName property - CommandButton, Frame, in fact anything other than an OptionButton or CheckBox it would appear - then I get an error: "Object doesn't support this property or method".

So, what I'm after is a way to filter out all Controls, (For Each x In Me.Controls), so that I don't get the error.

In pigeon-VBA my code would be something like:

For Each x In Me.Controls(OptionButton)
...

or

For Each x In Me.Controls
  If x.Type = OptionButton
...

or

For Each x In Me.Controls
  If x.GroupName And If x.GroupName = "Buttons"
...

or

For Each x In Me.Controls
  If x.Name [contains] Length
...

S

ScottBolton

  • Guest
Re: "(foreach" equivalent in VBA UserForm?
« Reply #21 on: April 04, 2013, 05:19:13 PM »
Ok, this works about as well as I'm going to get:

Code - vb.net: [Select]
  1.     Dim x As Control
  2.     For Each x In Me.Controls
  3.         If x.Name Like "Length*" Then '<<< Check name of Control >>>
  4.            If x.GroupName = "Buttons" Then
  5.                x.Enabled = False
  6.            End If
  7.         End If
  8.     Next x

That said, isn't the GroupName check redundant? 'spose it is, unless I only want to disable some of the Length buttons (which I probably will). Ok, I'll leave it in for now.

Time for beddie-byes!

S

owenwengerd

  • Bull Frog
  • Posts: 439
Re: "(foreach" equivalent in VBA UserForm?
« Reply #22 on: April 04, 2013, 08:51:20 PM »
Since your code that checks the GroupName is designed to work with OptionButton controls, I think you should check the *type* of the control instead of the name. There are different ways to approach it. You could simply catch and ignore the exception when it's not an OptionButton. I would actually check the type (and I'm not sure offhand what the VBA syntax is for runtime type information).

CmdrDuh

  • Automatic Duh Generator
  • King Gator
  • Posts: 4039
Re: "(foreach" equivalent in VBA UserForm?
« Reply #23 on: April 05, 2013, 10:33:08 AM »
I think the easiest thing to do would be make a function to disable all the buttons in Length, and call that each time a Size change is made.  Then enable only the ones you want
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 #24 on: April 05, 2013, 10:41:03 AM »
maybe something like this
Code: [Select]
Private Sub disablelength()
    LengthS.Enabled = False
    LengthP.Enabled = False
    Length03.Enabled = False
    Length05.Enabled = False
    Length06.Enabled = False
    Length07.Enabled = False
    Length08.Enabled = False
    Length09.Enabled = False
    Length10.Enabled = False
    LengthA0.Enabled = False
    LengthA1.Enabled = False
    LengthA2.Enabled = False
End Sub

then use it like this
Code: [Select]
Private Sub SizeA2_Click()
   disablelength
   LengthA2.Enabled = True
    Me.ListBox1.List() = Array("Standard", "Portrait", "A2 (594 mm)", "03 (630 mm)")
    Proceed.SetFocus
End Sub
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 #25 on: April 05, 2013, 03:10:50 PM »
CmdrDuh,

Thanks for re-writing my original post as a solution!  :-D

I had wanted to avoid:

Code - vb.net: [Select]
  1. LengthS.Enabled = False
  2. LengthP.Enabled = False
  3. Length03.Enabled = False
  4. ...

Let's exaggerate and say I have 200 OptionButtons. True, a bugger to navigate the UserForm but worse to try to code.

My last post had a solution where I check for the name of the Control but I would like to use some sort of check or filter determine the "Type" of a Control. Generally I prefer to use the default Name for a Control: OptionButton1, OptionButton2, etc., but my code requires that I give explicit names to the Controls. The GroupName property would work if all Controls supported this property... but they don't.

Ok, it's late here. Have a good weekend.

S

krampaul82

  • Newt
  • Posts: 179
Re: "(foreach" equivalent in VBA UserForm?
« Reply #26 on: July 08, 2013, 11:27:55 AM »
Looks like I'm late to the party.   :-)
Hey Matt I for one am glad for your continued time support in the swamp...
When i see someone fall, my first instinct is to laugh!
but then i think, wait a minute! they could of fallen on ME!
Mark