ActiveX ComboBox listfillrange automatic update

**Makafi** · 05-01-2009, 05:21 AM

Hi @ll,

I have a problem with combobox update. I have 2 sheets:

- List - containing list for combobox
- Raporty - containing combobox which should be updated.

Sheets("List").Select

Dim ostatnilista As Long

ostatnilista = Sheets("List").Range("A1").End(xlDown).Row 'finding last row of list

ActiveWorkbook.Names.Add Name:="listaa", RefersToR1C1:="=List!R2C1:R" & ostatnilista & "C1" 'defining range listaa which I want to use in listfillrange

Sheets("Raporty").Select

ActiveSheet.Shapes("ComboBox1").Select
        With Range("listaa")
        ComboBox1.RowSource = .Parent.Name & "!" & .Address
        End With      'trying to change listfillrange with range listaa

In result, I get "runtime error 424: object required"...

Any help would be much appreciated.

**Makafi** · 05-01-2009, 01:54 PM

On Polish Excel forum somebody gave me the solution, macro assigned to ComboBox which works great!

Private Sub ComboBox1_GotFocus() 
Dim List    As Worksheet 
Dim ostA    As Long 

Set List = ThisWorkbook.Worksheets("List") 
ostA = List.Range("A1").End(xlDown).Row 
Me.OLEObjects("ComboBox1").ListFillRange = "List!A1:A" & ostA 
Set List = Nothing 
End Sub

I'm closing this thread.