Multiple worksheet for combobox

[hedgehog88]

Another question.

I have this Initialize sub for a userform which I would like to have it set for multiple worksheets (not workbook). And have the combobox populate from another worksheet range.

My code right now for one worksheet is:

Private Sub UserForm_Initialize()

Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupList")

Is there a way to add another worksheet, such as LookupList2?

Thanks in advance

 

[hedgehog88]

bump

 

[Norie]

Sorry, but that code isn't populating a combobox from any worksheet.

Is it definitely all the code?

 

[hedgehog88]

Sorry, but that code isn't populating a combobox from any worksheet.

Is it definitely all the code?

It does work.

Private Sub UserForm_Initialize()

Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupList") <-----

For Each cLoc In ws.Range("AlertType")
With Me.cboAlertType
.AddItem cLoc.Value
End With
Next cLoc

However it only grabs the list range from "Lookuplist" worksheet. I would to know if its possible to add another worksheet so my combobox can list that worksheet range list as well.

I used codes from: http://www.contextures.com/Excel-VBA-ComboBox-Lists.html

 

[Norie]

Well, that code does populate the combobox.

If you just want to add the items from the other worksheet after the ones you are adding already just duplicate the code and change the worksheet/range name.

For Each cLoc In ws.Range("AlertType")

   With Me.cboAlertType
      .AddItem cLoc.Value
   End With

Next cLoc
 
Set ws = Worksheets("OtherWorksheet")
 
For Each cLoc In ws.Range("OtherRange")
 
    With Me.cboAlertType
        .AddItem cLoc.Value
    End With
 
Next cLoc