Announcement

Collapse
No announcement yet.

Combo Box listing a Named Range

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Combo Box listing a Named Range



    Hi,

    I want a named range of values in one sheet to be displayed in a combo box when the form is initiated. But when I am running the following code, either it is giving a run time error 91 'Object required' or it is throwing out error 13. Can you please fix this?

    Here is my existing code:





    Private Sub UserForm_Initialize()

    Dim contracts As Range
    Dim i As Integer

    Set contracts = Worksheets(3).Range("MyList")

    With UserForm1
    .Caption = "Sample Form"
    For i = 2 To Len(contracts)
    .ComboBox1.AddItem contracts(i)
    Next i
    End With
    End Sub




  • #2


    Hi Rekha123,

    The use of the LEN() function is inappropriate. LEN() returns the length of a string variable.

    Use the count of cells to populate you combobox.

    Private Sub UserForm_Initialize()

    Dim contracts As Range
    Dim i As Integer

    Set contracts = Worksheets(3).Range("MyList")

    With UserForm1
    .Caption = "Sample Form"
    For i = 2 To contracts.Cells.Count
    .ComboBox1.AddItem contracts.Cells(i).Value
    Next i
    End With
    End Sub

    Cheers
    Andy

    Comment

    Working...
    X