If you know the name of the sheet, you can change this line:

With ActiveSheet
to
With Worksheets("sheetnamehere")

======
If I wanted to use a range that contained blanks as my list in data|validation,
I'd copy it to a new range. Sort it and use the non-empty range.

Or maybe just sort the original range (all the columns!) and use a dynamic range
name that grew or contracted with the amount of data.

Debra Dalgleish explains dynamic range names he
http://contextures.com/xlNames01.html#Dynamic

(Maybe that's what you could use for populating the listbox, too???)

KIM W wrote:

Dear Dave,
Thanks so much for the code solution for creating range excluding blanks.
It works when the range is on the same worksheet that I launch the Userform
from. Could you assist further by showing me how the rage can be from
another worksheet?
A related question, please: How can I make this new blank-less range also
the range I use in a Validation List?
Here's what I ended up with:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
With ActiveSheet
Set myRng = .Range(Me.ComboBox1.Value)
End With
On Error GoTo 0

Me.Label1.Caption = ""

If myRng Is Nothing Then
Beep
Me.Label1.Caption = "Select a name from the combobox"
Else
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
Me.ListBox1.AddItem myCell.Value
End If
Next myCell
End If
End Sub

Private Sub UserForm_Initialize()

Dim nName As Name
Dim myNames() As String
Dim iCtr As Long

iCtr = 0
For Each nName In ThisWorkbook.Names
If LCase(nName.Name) Like LCase("List*") Then
iCtr = iCtr + 1
ReDim Preserve myNames(1 To iCtr)
myNames(iCtr) = nName.Name
End If
Next nName

If iCtr = 0 Then
UserFormListToCell.ComboBox1.Enabled = False
Else
With UserFormListToCell.ComboBox1
.Clear
.List = myNames
.Enabled = True

"Dave Peterson" wrote:

Loop through the cells in that range and check to see if they're empty. Use
..additem to add them to the listbox.

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range

Set myRng = Nothing
On Error Resume Next
With ActiveSheet
Set myRng = .Range(Me.ComboBox1.Value)
End With
On Error GoTo 0

Me.Label1.Caption = ""

If myRng Is Nothing Then
Beep
Me.Label1.Caption = "Select a name from the combobox"
Else
For Each myCell In myRng.Cells
If Trim(myCell.Value) = "" Then
'skip it
Else
Me.ListBox1.AddItem myCell.Value
End If
Next myCell
End If

End Sub
Private Sub UserForm_Initialize()
With Me.ComboBox1
.Clear
.AddItem "rngname1"
.AddItem "rngname2"
End With
End Sub


KIM W wrote:

I successfully populate a listbox with a list of values based on the value
selected in a combox. The value in the combobox is a named range. However,
that list may have blanks in it that I don't want.
How can I display the listing in the listbox excluding any blanks so it is
one solid list? THe total items is less than 200, and often less than 20.

Private Sub ComboBox1_Change()
Dim rng As Variant
'rng = UserForm1.ComboBox1.Selected(x)
With ActiveSheet
rng = UserFormListToCell.ComboBox1.Value
UserFormListToCell.ListBox1.RowSource = rng
End With

End Sub

--

Dave Peterson


--

Dave Peterson