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

0 Members and 1 Guest are viewing this topic.

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
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.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
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.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

Matt__W

  • Seagull
  • Posts: 12955
  • 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

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
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.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

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: 451
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).

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
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.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

David Hall

  • Automatic Duh Generator
  • King Gator
  • Posts: 4075
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.
Sometimes the question is more important than the answer. (Thanks Kerry for reminding me)

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

  • Guest
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...