CODEWITHSUNDEEP
vbaexcel
Andrea Aller
Andrea Aller

Reputation: 5

Problems with Null in VBA

I want the code to display the selection in ListBox1 in a MsgBox and "Select a Capacity" if ListBox1 is empty/not selected.
If I try to use IsEmpty(), then ListBox1.Value is Null.
If I use IsNull(), then ListBox1.Value is "".

Private Sub CommandButton3_Click()
    Dim Cap As Integer

    If IsNull(ListBox1) = True Then
        MsgBox "Select a Capacity"
        Exit Sub
    End If

    Cap = Left(ListBox1.Value, 2)
    MsgBox Cap    
End Sub

Any suggestions would be appreciated.

Upvotes: 0

Views: 203

Answers (2)

barrowc
barrowc

Reputation: 10689

The IsEmpty function is used to check is a variable of type Variant has been initialised. It cannot be used to check if a ListBox contains any entries.

The IsNull function checks if a variable has been set to Null. This doesn't help with checking a ListBox for entries.

Instead, use If ListBox1.ListCount = 0 Then to check if the ListBox is empty and use If ListBox1.ListIndex = -1 Then to check if any entries have been selected.

If the ListBox allows multiple selections at once then, as mentioned by @shoegazer100, use something like:

Dim rowNumber As Long
For rowNumber = 0 To (ListBox1.ListCount - 1)
    If ListBox1.Selected(rowNumber) Then
        ' do something
    End If
Next rowNumber

to determine which rows are currently selected (if Selected returns True for a particular row then the corresponding row in the ListBox is selected)

Upvotes: 0

shoegazer100
shoegazer100

Reputation: 13

You could try:

If ListBox1.ItemsSelected.Count = 0 Then
    MsgBox "Select a capacity"
    Exit Sub
End If

Cap = Left(ListBox1.Value, 2)

Upvotes: 1

Related Questions