Loop through controls on a form 2

[xlbo]

Hi y'all, hope someone can help me with this

What I've got is a form with a bunch of different controls on it

Certain controls need to preclude other controls from being enabled ie one combobox selection will disable a set of checkboxes and enable another combobox
so........

what I'm looking for is how to set up a For Each....Next loop that loops through the controls on a form, decides whether they're a checkbox or not and then diasables them if they are. I can do all of the above except, it seems, to reference the set of controls correctly. Can't seem to find the right syntax for checking the TYPE of control (ie checkbox)

eg
dim ctrl as control
For Each ctrl in me.Controls
if ctrl. type = xlcheckbox ---this is where i need the right syntax
then do stuff
else
end if

TIA
Geoff HTH
~Geoff~

 

[jebry]

Hi Geoff!

I haven't tried this in Excel, but I assume it is the same as Access. If so, you would use:


If ctrl.ControlType = xlCheckBox Then etc.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[xlbo]

Hi Jeff - doesn't seem to work I'm afraid - keep getting the ol'"Object Doesn't support this property or method" error. I just can't seem to find the right property for identifying the type of control

Thx anyway HTH
~Geoff~

 

[segmentationfault]

It seems that Controls don't have any sort of type property, or I'm just missing the boat. To see the available properties, do

    Dim c as Control
    c.

as your cursor sits at the period, the methods and properties appear. There isn't anything very helpful in there, I'm afraid.

If you name your controls with some prefix to indicate their type, you could get by with examining the names:

    For Each ctrl In UserForm1.Controls
        If Left(ctrl.Name, 5) = "Check" Then
            ctrl.Enabled = False
        End If
    Next ctrl

 

[xlbo]

Nice idea Mr Fault - I may have to go along with that - a star for a good suggestion

However, I refuse to believe that there isn't an "internal" way of deriving a control's type. Forms controls have a "type" - xlcheckbox etc, I can't accept that controls toolbox controls don't.

Anyone else ?? HTH
~Geoff~

 

[mdav2]

Its a bit of a pain but you could add a value to the tag of each control then query the value.

 

[JustinEzequiel]

Dim ctrl As Control

For Each ctrl In Me.Controls
  Debug.Print TypeName(ctrl)
Next ctrl

 

[xlbo]

Many thanks Justin - I now see the error of my ways - CheckBox needs to be "CheckBox" - I didn't use the " " 'cos when I typed it in, it capitalised so I thought it was a recognised object, whereas the typename function needs a string.....cool

Also, have never really used the immediate window before....very useful


Rgds
Geoff HTH
~Geoff~

 

[JustinEzequiel]

Geoff,

no problem