Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Ranges in various sheets
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.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... | Excel Programming | |||
copying named ranges to other sheets | Excel Programming | |||
Validation OK to named ranges in Veryhidden sheets? | Excel Programming | |||
Named ranges on other sheets | Excel Programming | |||
named ranges and copying sheets to another workbook | Excel Programming |