Populating multiple combo-boxes on a UserForm

Niknek · Nov 16th 2015

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?

Code

Private Sub UserForm_Initialize()
Dim cell As Range
Dim Rng As Range


With cbCalRun
ThisWorkbook.Sheet3 ("Calibration")
Set Rng = .Range("A2", .Range("A2").End(xlDown))
End With
For Each cell In Rng.Cells
Me.cbCalRun.AddItem cell.Value
Next cell


With cbIdentS
ThisWorkbook.Sheet1 ("Scope_of_Work")
Set Rng = .Range("B2", .Range("B2").End(xlDown))
End With
For Each cell In Rng.Cells
Me.cbIdentS.AddItem cell.Value
Next cell
End Sub

Display More

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

[COLOR=#333333]Niknek[/COLOR]

cytop · Nov 16th 2015

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...

**StephenR** · Nov 16th 2015

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

Niknek · Nov 17th 2015

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

**royUK** · Nov 18th 2015

Re: Populating multiple combo-boxes on a UserForm

Try this, the List Property is usually the best way

Code

Option Explicit




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

Display More

Niknek · Nov 18th 2015

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

Tom.Jones · Nov 18th 2015

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

Niknek · Nov 18th 2015

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"

**StephenR** · Nov 18th 2015

Re: Populating multiple combo-boxes on a UserForm

I guess you missed my earlier post...

**royUK** · Nov 20th 2015

Re: Populating multiple combo-boxes on a UserForm

Try this

Code

Option Explicit


Private Sub UserForm_Initialize()


    With ThisWorkbook.Sheets("Calibration").Range("A2").CurrentRegion
        Me.cbCalRun.List = .Columns(1).Value
        Me.cbIdentS.List = .Columns(2).Value
    End With
End Sub

Display More

Niknek · Nov 20th 2015

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

**royUK** · Nov 22nd 2015

Re: Populating multiple combo-boxes on a UserForm

Change the sheet name in the code

Niknek · Nov 22nd 2015

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.

**royUK** · Nov 23rd 2015

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

Code

Option Explicit




Private Sub UserForm_Initialize()
    With Sheets("Calibration")
        ''///assumes list starts in A1
        Me.cbCalRun.List = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value
        ''///load any other comboboxes whose lists are on this sheet
    End With


    With Sheets("Scope_of_Work")
        ''///assumes list starts in A1
        Me.cbIdentS.List = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)).Value
    End With
End Sub

Display More

Niknek · Nov 23rd 2015

Re: Populating multiple combo-boxes on a UserForm

Tks a million Roy, that's got it working perfectly.
You guys are simply indispensable.

Niknek · Nov 23rd 2015

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?

**royUK** · Nov 24th 2015

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

Niknek · Nov 24th 2015

Re: Populating multiple combo-boxes on a UserForm

Tks Roy,
I'll have a look at it later today.

Populating multiple combo-boxes on a UserForm

Participate now!

Share