Dynamic Combobox

[tamzgha]

Hi

I would greatly appreciate some appreciate some help from the experts in this forum, please help if you can.
I am trying to make the listing range for a combobox dynamic. The combobox listing ranges change depending on the value of a cell.
i have 3 listing ranges for one combobox, the ranges are factory1, factory2, factory3, all these ranges are static and i would like to make them dynamic.
i have attached an example file. below is the code i am using:
[Sub MAIN()
Dim PT As Range
Dim i As Long
With ActiveSheet
setNames .Range("a6")
Set PT = .Range("b1")
i = 1
Do Until PT = ""
If .Range("a1").Value = PT.Value Then
On Error Resume Next
.ComboBox1.ListFillRange = ThisWorkbook.Names("factory" & i).RefersToRange.Address
If Err.Number = 1004 Then
MsgBox "not defined name: factory" & i
ElseIf Err.Number <> 0 Then
MsgBox "unexpected error: " & Err.Description
End If
On Error GoTo 0
End If
i = i + 1
Set PT = PT.Offset(0, 1)
Loop
End With
End Sub

Sub setNames(theTopLeft As Range)
Dim theName As Name
Dim nameStr As String
Dim theRng As Range
Dim i As Long
Application.DisplayAlerts = False
theTopLeft.CurrentRegion.CreateNames Top:=True, Left:=False, _
Bottom:=False, Right:=False
Application.DisplayAlerts = True
For Each theName In ThisWorkbook.Names
With theName.RefersToRange
For i = .Cells.Count To 1 Step -1
If .Cells(i) <> "" Then Exit For
Next
End With
If i <> 0 Then theName.RefersTo = theName.RefersToRange.Resize(i, 1)
Next
End Sub][/CODE]

[BeachRock]

Hi tamzgha,

You might want to take a look at this: http://www.exceltip.com/excel-genera...t-problem.html

I use them all the time and they work fantastic.

[tamzgha]

Thanks a lot!
I used the OFFSET and it worked

[BeachRock]

Glad I could help.

Make sure to mark the thread as solved if your question was answered. Use the menu "Thread Tools" at the top of your first post and select "Mark this thread as solved".