why show error couldn't set the list property when run the userform

[Mussa]

Hi

this error causes me the headache sometimes the userform work without problem and somtimes shows "couldn't set the list property .invalid property array index" when run the userofrm in this line

 ComboBox1.List = Sheets("BRANDS").Range("B2", Sheets("BRANDS").Range("B65536").End(xlUp)).Value

Private Sub UserForm_Activate()
    ComboBox1.RowSource = ""
    ComboBox1.List = Sheets("BRANDS").Range("B2", Sheets("BRANDS").Range("B65536").End(xlUp)).Value

End Sub

can any body fix this error ,please?

 

[davesexcel]

Why are you using 'ComboBox1.RowSource = ""'?
Why is this in the UserForm_Activate event?
Do you have the list filled out in combobox properties?

 

[Mussa]

Why are you using 'ComboBox1.RowSource = ""'?

it gives the same error

Why is this in the UserForm_Activate event?

because UserForm_Initialize() contains others codes I don't want ovarlap each other of them . maybe cause some errors

Do you have the list filled out in combobox properties?

surly no

 

[davesexcel]

What are the steps?
Are you trying to populate the combobox after it has already been populated?

 

[Mussa]

should populate the combobox after run the userform, but I can't run the form because of this error.
seem to find out the problem after B2 is empty if I fill after B2 the error is gone . but how avoid this error if the after B2 is empty ?

 

[Siddharth Rout]

Do you have the list filled out in combobox properties?

surly no

Try this for me? (UNTESTED)

Replace

ComboBox1.RowSource = ""
ComboBox1.List = Sheets("BRANDS").Range("B2", Sheets("BRANDS").Range("B65536").End(xlUp)).Value

with

Dim ws As Worksheet
Dim LRow As Long
Dim rng As Range

Set ws = ThisWorkbook.Sheets("BRANDS")

With ws
    LRow = .Range("B" & .Rows.Count).End(xlUp).Row
 
    If LRow < 2 Then
        MsgBox "No data found"
        Exit Sub
    End If
 
    Set rng = .Range("B2:B" & LRow)
 
    ComboBox1.List = rng.Value2
End With

 

[Mussa]

@Siddharth Rout thanks . your code works in one case if the B2 is empty but if the B2 contains item will show the same error in this line

 ComboBox1.List = rng.Value2

 

[Siddharth Rout]

@Siddharth Rout thanks . your code works in one case if the B2 is empty but if the B2 contains item will show the same error in this line

 ComboBox1.List = rng.Value2

1. Check what is the value of LRow?
2. What exact error do you get?

 

[Mussa]

this is what I have in B2" BTR2300ttRR**/100-1"

 

[Siddharth Rout]

Try this

Dim ws As Worksheet
Dim LRow As Long
Dim rng As Range

Set ws = ThisWorkbook.Sheets("BRANDS")

With ws
    LRow = .Range("B" & .Rows.Count).End(xlUp).Row
 
    If LRow < 2 Then
        MsgBox "No data found"
        Exit Sub
    ElseIf LRow = 2 Then
        ComboBox1.AddItem .Range("B2").Value2
    Else
        Set rng = .Range("B2:B" & LRow)
        
        ComboBox1.List = rng.Value2
    End If
End With