Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Combo Box listing a Named Range

  1. #1
    Join Date
    7th July 2004
    Posts
    1

    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



    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,310
    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.

    VB:
    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


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Validation List To Combo Box Without A Named Range
    By RickWHaas in forum EXCEL HELP
    Replies: 2
    Last Post: December 5th, 2007, 06:44
  2. Go To Named Range Chosen From Combo Box
    By IGWright in forum EXCEL HELP
    Replies: 2
    Last Post: September 16th, 2007, 13:20
  3. Transpose Named Range for Combo Box
    By Jaclyn in forum EXCEL HELP
    Replies: 3
    Last Post: May 29th, 2006, 15:34
  4. Combo Box Named Range refers to another Named Range
    By bridges_22 in forum EXCEL HELP
    Replies: 9
    Last Post: February 27th, 2006, 18:48
  5. using combo box to define named range in VLOOKUP
    By Robert B in forum EXCEL HELP
    Replies: 2
    Last Post: January 24th, 2006, 21:48

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno