Named Ranges in various sheets

[Corey]

I have items listed in various ranges through multiple sheets.

I have Named Each range such as "PartNoBlue", "PartNoYellow" etc

I want to populate a Listbox on a form with ALL the Named Range Values
across all sheets, but have been unable to get the correct syntax.

I also tried a Named Range that included ALL Values of items across Multiple
sheets, but it returned an error.

How can i populate the Listbox with Multiple named Ranges correctly, and
also remove any Spaces?

Corey....

 

[Dave Peterson]

One name per listbox?

Option Explicit
Private Sub UserForm_Initialize()

Dim testRng as range
dim myCell as range
dim wks as worksheet

for each wks in activeworkbook.worksheets
set testrng = nothing
on error resume next
set testrng = wks.range("partnoblue")
on error goto 0

if testrng is nothing then
'not on this sheet
else
for each mycell in testrng.cells
if mycell.value = "" then
'skip it
else
me.listbox1.additem mycell.value 'mycell.text???
end if
next mycell
end if
next wks

End Sub

Or all the names in a single listbox?

Option Explicit
Private Sub UserForm_Initialize()

Dim testRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myNames As Variant
Dim nCtr As Long

myNames = Array("partnoblue", "PartNoYellow")

For nCtr = LBound(myNames) To UBound(myNames)
For Each wks In ActiveWorkbook.Worksheets
Set testRng = Nothing
On Error Resume Next
Set testRng = wks.Range(myNames(nCtr))
On Error GoTo 0

If testRng Is Nothing Then
'not on this sheet
Else
For Each myCell In testRng.Cells
If myCell.Value = "" Then
'skip it
Else
Me.ListBox1.AddItem myCell.Value 'mycell.text???
End If
Next myCell
End If
Next wks
Next nCtr

End Sub

Both untested, but compiled.