run time error 424: Object required

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
538
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
VBA Code:
Private Sub UserForm_Initialize()
  
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ComboBox" Then
        With ctrl
           .List = Sheets("Employees").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).Value
        End With
    End If
Next

End Sub

VBA Code:
Private Sub ComboBox1_Change()

Dim cmbBxType As Object

For i = 1 To Me.ComboBox1.ListCount
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "ComboBox" Then
            With Me.ActiveControl.ActiveControl.Text
               Set cmbBxType = Me.ActiveControl.ActiveControl.Name
                If Me.ActiveControl.ActiveControl.Text <> Me.ActiveControl.ActiveControl.List(i - 1) Then .AddItem cmbBxType.List(i - 1)
            End With
        End If
    Next
Next
    
End Sub

Inside my form, I have a frame. Inside the frame there are two textboxes and 20 comboboxes. What I was trying to do was make the code under combobox1_Change dynamic. That way I wouldn't have reference each combobox by name. If this worked, I would of made into a Sub routine, so all I would have to do was call that subroutine for each combobox under its change event. However I get the error stated in the title when I
VBA Code:
Set cmbBxType = Me.ActiveControl.ActiveControl.Name
When I remove the word "Set" and "Dim cmbBxType As Object" from the code above, I get another error, "Compile error: Variable not defined" with the word "cmbBxType" highlighted. I hope what I am trying to say makes sense. Thank you.
 
Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,384
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I see a couple of problems here. First I don't understand what you want your code to do, so it's hard to provide a correction.

You have For Each ctrl but you never use ctrl except to check its type. Perhaps you intend to use ctrl instead of ActiveControl.

VBA Code:
Set cmbBxType = Me.ActiveControl.ActiveControl.Name
.Name is the name of a control, which is a String. cmbBxType is an object. You cannot set an Object equal to a String, it has to be another Object. What are you trying to do in this line of code?

Also, you use ActiveControl.ActiveControl several times. I am pretty sure that ActiveControl does not have a property that is also called ActiveControl. What your intent in that reference?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,827
Office Version
  1. 2013
Platform
  1. Windows
As @6StringJazzer already stated, it's hard to understand what you're after.

so all I would have to do was call that subroutine for each combobox under its change event.
If you're after code reuse in order to handle similar control events with just one VBA procedure, then the use of a class module is the most obvious.
You might be interested in this thread. Although its topic concerns text boxes, this approach is also applicable to combo boxes.
 

Forum statistics

Threads
1,175,885
Messages
5,900,067
Members
434,817
Latest member
nbretscher13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top