Multiple worksheet for combobox

hedgehog88

New Member
Joined
Mar 25, 2011
Messages
6
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
Sorry, but that code isn't populating a combobox from any worksheet.

Is it definitely all the code?
 

hedgehog88

New Member
Joined
Mar 25, 2011
Messages
6
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
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,192
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top