CODEWITHSUNDEEP
excelvbalistbox
Basics B
Basics B

Reputation: 25

How would I check if a row of a Listbox is empty

I'm trying to write an IF statement in VBA to determine if the first column along the row of a listbox is empty. If the row is empty I want a message box to display. if the row is not empty then the row is saved to memory.

My code so far:

Private Sub RiskLogReviewListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   With RiskLogReviewListBox
      If IsNull(Me.RiskLogReviewListBox, 0, 0) Then
         MsgBox "Item is not a Valid Entry"
      Else
         str1$ = .List(.ListIndex, 0)
         str2$ = .List(.ListIndex, 1)
         str3$ = .List(.ListIndex, 2)
         str4$ = .List(.ListIndex, 3)
         str5$ = .List(.ListIndex, 4)
         str6$ = .List(.ListIndex, 5)
         str7$ = .List(.ListIndex, 6)
         str8$ = .List(.ListIndex, 7)
         str9$ = .List(.ListIndex, 8)
         str10$ = .List(.ListIndex, 9)
   End With
   End If

   Unload Me

   RiskRecordEditForm.Show
End Sub

Upvotes: 2

Views: 1767

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

determine if the first column along the row of a listbox is empty.

You can use Len() and Trim() to check if the first column along the row of a listbox is empty.

Is this what you are trying?

Option Explicit

'~~> Add sample data
Private Sub CommandButton1_Click()
    With RiskLogReviewListBox
        .AddItem
        .List(UBound(.List), 0) = "aa"
        .List(UBound(.List), 1) = "bb"

        .AddItem
        .List(UBound(.List), 0) = "cc"
        .List(UBound(.List), 1) = "" '<~~ Empty
    End With
End Sub

Private Sub RiskLogReviewListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If RiskLogReviewListBox.ListIndex = -1 Then Exit Sub

    If Len(Trim(RiskLogReviewListBox.List(RiskLogReviewListBox.ListIndex, 1))) = 0 Then _
    MsgBox "First Column of selected row is empty"
End Sub

enter image description here

BTW you have your End With before the End If. Maybe a typo?

NOTE: I am considering the second column as the first column. If you meant the first column as in the first column then instead of 1 use 0. For example RiskLogReviewListBox.List(RiskLogReviewListBox.ListIndex, 0)

Option Explicit

'~~> Add sample data
Private Sub CommandButton1_Click()
    With RiskLogReviewListBox
        .AddItem
        .List(UBound(.List), 0) = "aa"
        .List(UBound(.List), 1) = "bb"

        .AddItem
        .List(UBound(.List), 0) = "" '<~~ Empty
        .List(UBound(.List), 1) = "cc" 
    End With
End Sub

Private Sub RiskLogReviewListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    If RiskLogReviewListBox.ListIndex = -1 Then Exit Sub

    If Len(Trim(RiskLogReviewListBox.List(RiskLogReviewListBox.ListIndex, 0))) = 0 Then _
    MsgBox "First Column of selected row is empty"
End Sub

enter image description here

Upvotes: 1

Related Questions