Excel VBA ComboBox populate based on previous ComboBox Values

[MaxMiller]

I am trying to populate a second combobox in a userform using the values of the previous combo box using Excel 2010. The first combobox is populated using properties - rowsource - List1Values (I defined dynamic names in the Data spreadsheet). I am attempting to use the following code to populate cboSecondary.

Option Explicit


Public Const kApp As String = "(xld) Dynamic DropDowns"
Public Const kList1Hnd As String = "List1Values"
Public Const kList2Hnd As String = "List2_"


Public kList1 As String
Public klist2 As String






'---------------------------------------------------------------------
Public Function fzPopulatList2(idx As Long)
'---------------------------------------------------------------------
Dim i As Long
Dim formula As String


Application.EnableEvents = False
'On Error GoTo pl2_exit

formula = kList2Hnd & CStr(idx)

With PSk5.cboSecondary
.Clear
For i = 1 To Range(formula).Count
.AddItem Range(formula).Cells(i, 1).Value
Next i

Application.EnableEvents = True
.ListIndex = 0
End With


pl2_exit:
Application.EnableEvents = True
End Function


The error is generating on "For i = 1 To Range(formula).Count". I changed the cboPrimary values from being listed horizontally in row 1 to vertically in column A and think this might be the problem.

My dynamic names are as follows:
List1Values=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1)
List2_1=OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1,1)
List2_2=OFFSET(Data!$C$2,0,0,COUNTA(Data!$C:$C)-1,1)
List2_3=OFFSET(Data!$D$2,0,0,COUNTA(Data!$D:$D)-1,1)

Finally, I have code in the userform to update cboSecondary as follows:


Option Explicit


Dim fReEntry As Boolean


'---------------------------------------------------------------------
Private Sub cboPrimary_Change()
'---------------------------------------------------------------------
Dim idx As Long
Dim iTargetCol As Long
Dim oFoundCell As Range

Application.ScreenUpdating = False

With Data.Range(kList1Hnd)
Data.Activate
Set oFoundCell = .Find(what:=cboPrimary.Value, _
LookIn:=xlValues)
On Error GoTo 0
If oFoundCell Is Nothing Then
MsgBox "Critical error", vbCritical, kApp
Exit Sub
End If

End With

'load the List2 dropdown and set the default to item 1
iTargetCol = oFoundCell.Column - 1
fzPopulatList2 iTargetCol

Application.ScreenUpdating = True


End Sub


Any help making the cboSecondary populate correctly based on cboPrimary is greatly appreciated!