Named Ranges in various sheets
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.
Corey wrote:
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
|