Reputation: 263
When using a ComboBox the code worked perfectly, but when using ListBox the C variable is set to null even when a value is selected. I receive an error message "Invalid use of Null".
I need a ListBox because I need to select multiple values from the drop down.
Any help would be much appreciated I have been banging my head about this for most of the day.
Public Sub CommandButton1_Click()
Dim C As String
Dim LastTarget As range
Dim LastTarget2 As range
Set LastTarget = ActiveCell
Set LastTarget2 = ActiveCell.Offset(0, 3)
Set wb1 = Workbooks("Premium Billing Report TemplateListBox.xlsm")
'LastRow = wb1.Sheets("CGIBill").range("A:A").Find("Overall - Total", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
C = ListBox8.value
'For i = 11 To LastRow
'Counts & Coverages based on plan code selection
LastTarget = Application.CountIf(wb1.Sheets("Carrier").range("BG:BG"), C)
LastTarget2 = Application.SumProduct(Application.SumIf(wb1.Sheets("Carrier").range("BG:BG"), C, wb1.Sheets("Carrier").range("BK:BK")))
'Next
Unload Me
End Sub
With Sheets("Carrier").range("BG10:BG10000") v = .value End With With CreateObject("scripting.dictionary") .comparemode = 1 For Each e In v If Not .exists(e) Then .Add e, Nothing Next If .Count Then Me.ListBox8.List = Application.Transpose(.keys) End With
Upvotes: 0
Views: 727
Reputation: 909
This little snippet will get around the error:
If IsNull(ListBox8.Value) Then
C = ""
Else
C = CStr(ListBox8.Value)
End If
Upvotes: 1