VBA UserForm - Object doesn't support this method

Hi everyone,

I've got to run the same lot of code 3 times over. In order to achieve this I'm using a "For i = 1 to 3" method. Here is an extract of my code:

Private Sub UserForm_Activate()
    Dim Cntrl As MSForms.Control

    For i = 1 To 3
        ' OP Times
        UseFrame = "I_D" & i & "_OT_F"

        For Each Cntrl In Me.Controls(UseFrame)
            If Cntrl.Name Like "*_TH" Then Cntrl.BackColor = RGB(33, 89, 103)
            If Cntrl.Name Like "*_TS" Then Cntrl.BackColor = RGB(218, 238, 243)
            If Cntrl.Name Like "*_TC" Then Cntrl.BackColor = RGB(238, 236, 225)
        Next Cntrl
    Next i
End Sub

Effectively I want to use the "UseFrame" string as the control name to use. It will update each time until it reaches 3. For some reason I'm getting an error on the "For Each Cntrl in Me.Controls(UseFrame)" line. Any ideas why?

Error is: "Run-time error '438': Object doesn't support this property or method".

I'm sure it's an easy fix but can't figure it out.

Many thanks,

Daniel

Moved from: (Office /Excel /Windows 10 /Office 2016 )

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Answer

JasleS

Independent Advisor

I think this will do it for you. It worked on my test file:

Private Sub UserForm_Activate()

Dim Cntrl As MSForms.Control
Dim CntrlOther As MSForms.Control

For I = 1 To 3
    ' OP Times
    useframe = "I_D" & I & "_OT_F"
    
    For Each Cntrl In Me.Controls
    Debug.Print Cntrl.Name
        If Cntrl.Name = useframe Then
            For Each CntrlOther In Cntrl.Controls
                If CntrlOther.Name Like "*_TH" Then CntrlOther.BackColor = RGB(33, 89, 103)
                If CntrlOther.Name Like "*_TS" Then CntrlOther.BackColor = RGB(218, 238, 243)
                If CntrlOther.Name Like "*_TC" Then CntrlOther.BackColor = RGB(238, 236, 225)
            Next CntrlOther
        End If
    Next Cntrl
Next I

Microsoft Learn Q&A needs your feedback!

Contribute to the Microsoft 365 and Office forum!

Excel Forum Top Contributors:

VBA UserForm - Object doesn't support this method

Replies (13)

Question Info

VBA UserForm - Object doesn't support this method

Replies (13) 

Question Info

Replies (13)