Range on Userform only if on worksheet
 

The following code wrongly only displays the list of values if the range is
on the worksheet from which I launched the form. If the range is not on the
worksheet, then I get no display-- I get the message from code below, "Select
a name from the combobox"./
Assistance, please?

This userform has one combobox and one listbox. The listbox is filled by
values in the range selected in the combobox. It works fine, but I have been
trying to remove blanks in the range before displaying in the listbox.

How can I get it so that it doesn't matter where in the workbook the range
originates?

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

Range on Userform only if on worksheet
 

Check your earlier thread.

KIM W wrote:

The following code wrongly only displays the list of values if the range is
on the worksheet from which I launched the form. If the range is not on the
worksheet, then I get no display-- I get the message from code below, "Select
a name from the combobox"./
Assistance, please?

This userform has one combobox and one listbox. The listbox is filled by
values in the range selected in the combobox. It works fine, but I have been
trying to remove blanks in the range before displaying in the listbox.

How can I get it so that it doesn't matter where in the workbook the range
originates?

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

--

Dave Peterson