Range on Userform only if on worksheet

K

KIM W

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
 
D

Dave Peterson

Check your earlier thread.

KIM said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top