Populating multiple combo-boxes on a UserForm

  • I'm very much a 'Newbie' to Excel VBA and I'm having all sorts of problems, particularly with populating multiple combo-boxes on the same 'UserForm'.


    My 'UserForm' contains 2 comboboxes - cbIdentS and cbCalRun, can anyone tell me why this code doesn't work? It throws up a 'Compile Error: Method or data member not found'.


    I don't know why those "[COLOR]" references have appreared, I suspect I haven't added the code to the message properly - another Newbie problem?




    I have searched through related topics, but can't find an answer that works, any help would be much appreciated.


    [COLOR=#333333]Niknek[/COLOR]

  • Re: Populating multiple combo-boxes on a UserForm


    Rather than a general comment that 'It throws up a 'Compile Error: Method or ...' it usually helps if you mention which line throws the error...


    Failing that it's a guessing game to start with. My guess is one of the referenced worksheets does not exist - perhaps there's an embedded space in the name which is not accounted for in the code. And then again, I could be wrong... :)

  • Re: Populating multiple combo-boxes on a UserForm


    I'm guessing this

    Code
    With cbCalRun 
            ThisWorkbook.Sheet3 ("Calibration") 
            Set Rng = .Range("A2", .Range("A2").End(xlDown)) 
        End With


    should be

    Code
    With ThisWorkbook.Sheets("Calibration") 
            Set Rng = .Range("A2", .Range("A2").End(xlDown)) 
        End With

    ?
    Though quicker to use

    Code
    cbCalRun.list=ThisWorkbook.Sheets("Calibration").Range("A2", ThisWorkbook.Sheets("Calibration").Range("A2").End(xlDown)).value
  • Re: Populating multiple combo-boxes on a UserForm


    Quote from cytop;760296

    Rather than a general comment that 'It throws up a 'Compile Error: Method or ...' it usually helps if you mention which line throws the error...


    Failing that it's a guessing game to start with. My guess is one of the referenced worksheets does not exist - perhaps there's an embedded space in the name which is not accounted for in the code. And then again, I could be wrong... :)


    Here's a snapshot of the offending code:-

  • Re: Populating multiple combo-boxes on a UserForm


    Try this, the List Property is usually the best way


  • Re: Populating multiple combo-boxes on a UserForm


    Hi royUK,


    Thanks for that, however I'm getting a compile error in both lines of code with the second instance of '.Range' highlighted.


    Code
    Private Sub UserForm_Initialize()
         
        Me.cbCalRun.List = ThisWorkbook.Sheets("Calibration").Range("A2", [FONT=arial black].Range[FONT=arial]("[/FONT][/FONT]A2").End(xlDown)).Value
        Me.cbIdentS.List = ThisWorkbook.Sheets("Scope_of_Work").Range("B2", [FONT=arial black].Range[/FONT]("B2").End(xlDown)).Value
         
    End Sub


    Niknek

  • Re: Populating multiple combo-boxes on a UserForm


    Try to remove "." marked in red


    Private Sub UserForm_Initialize()


    Me.cbCalRun.List = ThisWorkbook.Sheets("Calibration").Range("A2", .Range("A2").End(xlDown)).Value
    Me.cbIdentS.List = ThisWorkbook.Sheets("Scope_of_Work").Range("B2", .Range("B2").End(xlDown)).Value

    End Sub


    I mean


    Code
    Private Sub UserForm_Initialize() 
         
         
        Me.cbCalRun.List = ThisWorkbook.Sheets("Calibration").Range("A2", Range("A2").End(xlDown)).Value 
        Me.cbIdentS.List = ThisWorkbook.Sheets("Scope_of_Work").Range("B2", Range("B2").End(xlDown)).Value 
         
    End Sub
  • Re: Populating multiple combo-boxes on a UserForm


    Code
    Private Sub UserForm_Initialize() 
         
         
        Me.cbCalRun.List = ThisWorkbook.Sheets("Calibration").Range("A2", Range("A2").End(xlDown)).Value 
        Me.cbIdentS.List = ThisWorkbook.Sheets("Scope_of_Work").Range("B2", Range("B2").End(xlDown)).Value 
         
    End Sub

    [/QUOTE]


    Thanks, but that now gives me a "Runtime error 1004: Application-defined or object-defined error"

  • Re: Populating multiple combo-boxes on a UserForm


    Try this


  • Re: Populating multiple combo-boxes on a UserForm


    Roy,


    Thank you for that, however, the data required to populate cbIdentS is on a different worksheet i.e. Scope_of_Work.


    Regards


    Niknek

  • Re: Populating multiple combo-boxes on a UserForm


    Roy, Can I use two 'With - End with' loops in the same chunk of code?
    I need to populate 'cbCalRun' from the 'Calibration' worksheet, and 'cbIdentS' from the 'Scope_of_Work' worksheet.

  • Re: Populating multiple combo-boxes on a UserForm


    They are not Loops. The statement refers to an object whose properties are referredtod multiple times. In this case you would need a statement for each sheet

  • Re: Populating multiple combo-boxes on a UserForm


    Roy,
    That works perfectly, however, with 'cbIdentS' I also need to copy the appropriate data from 'Scope_of_Work' columns C, D, E, F & H, into textboxes on the userform, and I can't quite see how to do that.
    Can you help?

  • Re: Populating multiple combo-boxes on a UserForm


    What do you mean? Do you need to populate for textboxes when the combobox is changed. I think an example workbook would help us understand what you are doing.


    This might be what you want


    ComboBox Lookup example

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!