I have a userform that is initialized with the city names, but its value is not transferred to the spreadsheet.

When I put a MsgBox in the OkButton_Click sub for CityListBox.Value, it returns "invalid use of null".

Everything else works perfectly, only the third column stays empty. I also see the first city name (and can select the others) on the user form.

Any ideas what the problem might be?

Private Sub UserForm_Initialize()    
    NameTextBox.Value = ""    
    PhoneTextBox.Value = ""
    CityListBox.Clear
    
    With CityListBox
        .AddItem "San Francisco"
        .AddItem "Oakland"
        .AddItem "Richmond"
    End With
    
    DinnerComboBox.Clear
        
    With DinnerComboBox
        .AddItem "Italian"
        .AddItem "Chinese"
        .AddItem "Frites and Meat"
    End With
    
    DateCheckbox1.Value = False
    DateCheckbox2.Value = False
    DateCheckbox3.Value = False
    
    MoneyTextBox.Value = ""
    
    NameTextBox.SetFocus
End Sub

Private Sub OKButton_Click()
    Dim emptyRow As Long
    
    Sheets("DinnerPlannerData").Activate
    
    MsgBox CityListBox.Value
    
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    
    Cells(emptyRow, 1).Value = NameTextBox.Value
    Cells(emptyRow, 2).Value = PhoneTextBox.Value
    Cells(emptyRow, 3).Value = CityListBox.Value
    Cells(emptyRow, 4).Value = DinnerComboBox.Value
    
    If DateCheckbox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckbox1.Caption
    If DateCheckbox2.Value = True Then Cells(emptyRow, 5).Value = DateCheckbox2.Caption
    If DateCheckbox3.Value = True Then Cells(emptyRow, 5).Value = DateCheckbox3.Caption
    
    If CarOptionButton1.Value = True Then
        Cells(emptyRow, 6).Value = "Yes"
    Else
        Cells(emptyRow, 6).Value = "No"
    End If
    
    Cells(emptyRow, 7).Value = MoneyTextBox.Value
End Sub

I have a userform that is initialized with the city names, but its value is not transferred to the spreadsheet.

When I put a MsgBox in the OkButton_Click sub for CityListBox.Value, it returns "invalid use of null".

Everything else works perfectly, only the third column stays empty. I also see the first city name (and can select the others) on the user form.

Any ideas what the problem might be?

Private Sub UserForm_Initialize()    
    NameTextBox.Value = ""    
    PhoneTextBox.Value = ""
    CityListBox.Clear
    
    With CityListBox
        .AddItem "San Francisco"
        .AddItem "Oakland"
        .AddItem "Richmond"
    End With
    
    DinnerComboBox.Clear
        
    With DinnerComboBox
        .AddItem "Italian"
        .AddItem "Chinese"
        .AddItem "Frites and Meat"
    End With
    
    DateCheckbox1.Value = False
    DateCheckbox2.Value = False
    DateCheckbox3.Value = False
    
    MoneyTextBox.Value = ""
    
    NameTextBox.SetFocus
End Sub

Private Sub OKButton_Click()
    Dim emptyRow As Long
    
    Sheets("DinnerPlannerData").Activate
    
    MsgBox CityListBox.Value
    
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
    
    Cells(emptyRow, 1).Value = NameTextBox.Value
    Cells(emptyRow, 2).Value = PhoneTextBox.Value
    Cells(emptyRow, 3).Value = CityListBox.Value
    Cells(emptyRow, 4).Value = DinnerComboBox.Value
    
    If DateCheckbox1.Value = True Then Cells(emptyRow, 5).Value = DateCheckbox1.Caption
    If DateCheckbox2.Value = True Then Cells(emptyRow, 5).Value = DateCheckbox2.Caption
    If DateCheckbox3.Value = True Then Cells(emptyRow, 5).Value = DateCheckbox3.Caption
    
    If CarOptionButton1.Value = True Then
        Cells(emptyRow, 6).Value = "Yes"
    Else
        Cells(emptyRow, 6).Value = "No"
    End If
    
    Cells(emptyRow, 7).Value = MoneyTextBox.Value
End Sub

• excel
• vba

• Have you select an item in the ListBox? – Black cat Commented Nov 18, 2024 at 10:43

1 Answer 1

2 scenarios:

• Your Listbox has property MultiSelect to frmMultiSelectSingle:

If you fill a Listbox, but a user doesn't select anything, the value-property is null (which is not the string "null", not the string "" and not the value Empty).

If you write this to a cell, Excel translates this as an empty value. If the user selects a value from the list, the value-property is set to the selected item.

With ThisWorkbook.Sheets("DinnerPlannerData")
    emptyRow = WorksheetFunction.CountA(.Range("A:A")) + 1
    ' (all the other fields...)
    If IsNull(CityListBox.Value) Then
        .Cells(emptyRow, 3).Value = "(no City selected)"
    Else
        .Cells(emptyRow, 3).Value = CityListBox.Value
    End If
End With

• Your Listbox has property MultiSelect to frmMultiSelectMulti or fmMultiSelectExtended:

The user can select more than one item from the list. The value-property will always be null. To get a list of all selected items, you need to loop over the Selected-property of the listbox (note that this list is 0-based).

With ThisWorkbook.Sheets("DinnerPlannerData")
    Dim CityList As String, i As Long
    For i = 0 To CityListBox.ListCount - 1
        If CityListBox.Selected(i) Then
            CityList = CityList & IIf(CityList = "", "", ", ") & CityListBox.List(i)
        End If
    Next
    .Cells(emptyRow, 3).Value = CityList
End With