Dynamic dependent ComboBox VBA - error

[stemby]

I'm trying to do a VBA userform with a dynamic ComboBox - using dynamic named ranges. It should read the columns from sheet1 and populate the ComboBoxes.

However, when I run the userform I'm getting a 'Runtime Error: 1004 - Application Defined or Object Defined Error'.

Any ideas how I fix this? I'm not an experienced coder, so be gentle with me


In sheet1, where I have the lookup values for the combo boxes, I have:

Where	Location	Event_Type
Where1	Loc1	Type1
Where2	Loc2	Type2
Where3	Loc3	Type3
Where4	Loc4	Type4
Where5	Loc5	Type5
	Loc6	Type6
		Type7
		Type8
		Type9

The code for the userform is:

Private Sub UserForm_Initialize()

Dim lastrow As Long
Dim lastcolumn As Long
Dim i As Integer

lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(x1toleft).Column

With Worksheets("sheet1")

    For i = 1 To lastcolumn

        With .Columns(i)
            lastrow = Sheet1.Cells(Rows.Count, i).End(x1up).Row
           
            With Range(Cells(1, i), Cells(lastrow, i))
                Range(Cells(1, i), Cells(lastrow, i)).Select
                Selection.CreateNames Top:=True
            End With
        End With
    Next i
End With

'Just doing the 1st ComboBox to start with
Me.ComboBox1.RowSource = "Where"

End Sub

Thanks for any help....

 

[DanteAmor]

Hello, check the following notes:

lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(x1toleft).Column

lastrow = Sheet1.Cells(Rows.Count, i).End(x1up).Row

It should be the letter "l" and you have the number "1"

To identify this type of error and some others, I recommend using the Option Explicit statement at the beginning of all the code.

For example if you have data in columns A, B and C, that is, columns 1, 2 and 3, then you must have ComboBox1, ComboBox2 and ComboBox3.
Replace all your code with the following:

Option Explicit

Private Sub UserForm_Initialize()
  Dim j As Long
 
  With Worksheets("Sheet1")
    For j = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
      .Range(.Cells(1, j), .Cells(Rows.Count, j).End(xlUp)).CreateNames Top:=True
      Controls("Combobox" & j).RowSource = .Cells(1, j).Value
    Next j
  End With
End Sub

----- --
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[DanteAmor]

Other tips, try to be consistent in the references to the sheets.
In your code you have three references for the same sheet and in other ranges you do not use.

Regards
Dante Amor

 

[stemby]

Thanks for the help Dante, much appreciated. I'm just a novice who does a bit of googling to get by, all advice welcome