For each with GroupItems

[ButtFace]

Trying to iterate (for each...next) through specific shape types (form controls, not activex) in a shape group ("grpNav"). Can't get it to work, and the MSDN article is somewhat vague on syntax. What am I doing wrong?

Function Nav(Optional Source As Worksheet) As Range
  'decs
  Dim ShapeName As String
  Dim rTarget As Range
  
  Dim rb As OptionButton
  Dim cb As CheckBox
  
  'defs
  On Error GoTo errFatal
  fn "Nav"
  If Source Is Nothing Then Set Source = ActiveSheet
  dp "Source set to: " & Source.Name
  ToggleEvents False
  
  'main
  On Error GoTo errView
  For Each rb In Source.Shapes("grpNav").GroupItems
    ShapeName = Replace(opt.Name, "rb", "") & "."
    With Source
      For Each cb In Source.Shapes("grpNav").GroupItems
        Set rTarget = Nothing
        Select Case UCase(cb.Name)
          Case "CHKTOTALCOST": Set rTarget = Application.Union(IIf(rTarget Is Nothing, .Range(ShapeName & ".Cost.Subtotal"), rTarget), .Range(ShapeName & ".Cost.Subtotal"))
          Case "CHKUNITCOST": Set rTarget = Application.Union(IIf(rTarget Is Nothing, .Range(ShapeName & ".Cost.Unit"), rTarget), .Range(ShapeName & ".Cost.Unit"))
          Case "CHKTOTALHOURS": Set rTarget = Application.Union(IIf(rTarget Is Nothing, .Range(ShapeName & ".Hours.Subtotal"), rTarget), .Range(ShapeName & ".Hours.Subtotal"))
          Case "CHKUNITHOURS": Set rTarget = Application.Union( _
            IIf(rTarget Is Nothing, .Range(ShapeName & ".Hours.ST.Unit"), rTarget), _
            .Range(ShapeName & ".Hours.ST.Unit"), _
            .Range(ShapeName & ".Hours.OT.Unit"), _
            .Range(ShapeName & ".Hours.DT.Unit"), _
            .Range(ShapeName & ".Hours.Difficulty"))
          Case Else: dp "Error: " & cb.Name & " unsupported. Resuming"
        End Select
        rTarget.Hidden = Not (rTarget.Hidden)
skip: Next cb
    End With
  Next rb
  GoTo quit

errView:
  dp "Error: Error intersecting view with category. Resuming"
  Resume skip

errFatal:
  dp "** Error: Unhandled. Terminating"
  GoTo quit
  
quit:
  ToggleEvents True
  fn "Nav", True
End Function

 

[Norie]

How is the code not working?

Also, why do you have 2 loops?

 

[ButtFace]

Some more detail about the setup.
On a template page in this workbook, there is a group of shapes called "grpNav".
In grpNav, there are 3 shape types, all form controls: 12 options buttons, 4 checkboxes, and 1 combobox object.
Each option button represents a set of columns (e.g., Material, Freight, Shop, Field, etc...), and each checkbox represents a specific column within that column set (e.g., Material.Cost.Unit, Material.Cost.Subtotal)

The option buttons hide the entire columnset, the checkboxes toggle on/off specific columns within that set.

I get a type mismatch error. Presumably because rb is not a valid object within grpNav. Syntax error?

 

[Norie]

You can't loop through specific types of controls in GroupItems.

As it is your code is looping through all the controls in the group, twice, and you get a type mismatch because you've declared the loop control variables as specific types of controls.

What you need to do is within the loop check the type of control and act accordingly.

For Each ctl In Source.Shapes("grpNav").GroupItems
    If TypeName(ctl.OLEFormat.Object) = "CheckBox" Then
        ' do stuff with checkbox
    End If
Next ctl

 

[ButtFace]

Blargh. OK. Was hoping to avoid having to loop through the shape group by index. Case TypeName it is then. =\

Thanks for the quick response.