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
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