CODEWITHSUNDEEP
excellistboxuserformvba
t.mo
t.mo

Reputation: 263

Why is my ListBox8.value listed as Null?

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

Answers (1)

scb998
scb998

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

Related Questions