For Each statement problem - Loop through controls on Excel User Form 1

[SQLBI]

Hi,

I have a UserForm in Excel 2k and i'm trying to assign a value to each CheckBox on the form when it is closed.

I have part of the code as below;

Private Sub cmdExit_Click()

Dim c As Control

    For Each c In Me.Controls
    
        If Left(c.Name, 3) = "chk" Then
        

                                
        End If
    
    Next c
    
    Me.Hide
    
End Sub

This loops through each control and identifies the CheckBoxes, but when i try to assign a false value to each one, i find that it is not possible to use the

.Value

method.

When i attempt to use

c.Value = False

i get an error stating "Object does not support this method".

Can anyone lend a hand with this please?

Thanks in advance.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[RobBroekhuis]

When you go into debug after getting the error message, check in the immediate window
?typename(c)
My guess is that somehow you found something that's not a checkbox - maybe a corresponding label?
By the way, you shouldn't have to use c.value=false - c = false should work just fine too.


Rob

 

[combo]

Hi,
get first object from the control:

[tt]c.Object.Value=False[/tt]

combo

 

[SQLBI]

Rob,

c = False works perfectly... thanks.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[RobBroekhuis]

Combo,
That would be necessary if the control was an OLEObject on a sheet - on a userform, the code as written should work just fine. I think there's a different problem.


Rob

 

[combo]

You are right, Rob. I haven't checked the simplest (it also works with Object).

combo