I am creating a quick procedure which allows users to select a range of cells in a worksheet. When they are done selecting the range of cells the values are displayed in a listbox located in a userform. (This macro will help update some metrics reports and the criteria are constantly changing, so I am allowing the user to select the criteria, confirm their selection in the listbox, and then move on from there.) This works great when multiple cells are selected, but if only one cell is selected it throws a mismatch error. Below is my code:
Now I can determine when the user has only selected one cell, but I do not know how to pass this information into the listbox. Any help would be appreciated.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myArray() As Variant
Dim Lrow As Long
Dim Lcol As Long
Dim x As Long
' Public variable that populates textbox showing selected range
TargetRng = Target.Address
UserForm1.TextBox1.Value = TargetRng
' Determine number of rows and columns
Lcol = Target.Columns.Count
Lrow = Target.Rows.Count
' Determine if only one cell was selected
If Lcol + Lrow = 2 Then
Exit Sub
Else
' Fill array with selected cells
myArray = Target
For x = LBound(myArray) To UBound(myArray)
Next
' Adjust field columns accordingly
UserForm1.ListBox1.ColumnCount = Lcol
' Fill listbox with selected cells
UserForm1.ListBox1.List = myArray
End If
End Sub
Now I can determine when the user has only selected one cell, but I do not know how to pass this information into the listbox. Any help would be appreciated.