If no values are found then show Msgbox

[ipbr21054]

I have this code in use.

If there are no values to populate the Listbox i wish to see a Msgbox but im not sure what code to use or where to place it

    Dim fndRng As Range
    Dim firstAddress As String
    
With Me.ListBox1
    .ColumnCount = 7
    .ColumnWidths = "220;200;110;170;130;60;100"
End With

With Sheets("POSTAGE").Range("G:G")
    Set fndRng = .Find(What:="RECEIVED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do
            ' check the date
            If Date - DateValue(fndRng.Offset(, -6)) < 80 Then
                ' add to listbox
                With Me.ListBox1
                    .AddItem fndRng.Offset(, -5).Value                      'CUSTOMER
                    .List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value    'ITEM
                    .List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value    'DATE
                    .List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value    'TRACKING NUMBER
                    .List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value     'CLAIM
                    .List(.ListCount - 1, 6) = fndRng.Value                 'RECEIVED NO SIG
                    
                End With
            End If
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

 

[sxhall]

This will count as each item is added to the listbox and then an If statement at the end will determine if it will show the message box or not.

Dim fndRng As Range
Dim firstAddress As String
Dim ItemCount as Integer

ItemCount = 0

With Me.ListBox1
    .ColumnCount = 7
    .ColumnWidths = "220;200;110;170;130;60;100"
End With

With Sheets("POSTAGE").Range("G:G")
    Set fndRng = .Find(What:="RECEIVED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do
            ' check the date
            If Date - DateValue(fndRng.Offset(, -6)) < 80 Then
                ' add to listbox
                With Me.ListBox1
                    .AddItem fndRng.Offset(, -5).Value                      'CUSTOMER
                    .List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value    'ITEM
                    .List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value    'DATE
                    .List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value    'TRACKING NUMBER
                    .List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value     'CLAIM
                    .List(.ListCount - 1, 6) = fndRng.Value                 'RECEIVED NO SIG

                    ItemCount = ItemCount + 1 ' Will add 1 to each time an item is added to the listbox

                End With
            End If
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

If Itemcount = 0 Then
MsgBox "Nothing added to listbox"
End If

 

[ipbr21054]

Thanks but that didnt quite work for me.

Where there are no values i see the msgbox.
I close the Msgbox but then the userform wants to open.
I put a line of code to close it but then when i go to the click the button to run it again the form wont open.

I think best maybe i wait for user that helped me & maybe put the text to advise no values actually in the listbox
Thanks

 

[sxhall]

Add me.hide for the userform.

If Itemcount = 0 Then
MsgBox "Nothing added to listbox"
Me.Hide
End If

If the count is more than 1 it will show otherewise it will not.

 

[ipbr21054]

I understand my my button to open the userform is on a worksheet.

This code runs after you try to open it from worksheet

 

[NoSparks]

Perhaps this

Private Sub UserForm_Initialize()

    Dim fndRng As Range
    Dim firstAddress As String
    Dim cnt As Long
    
With Me.ListBox1
    .ColumnCount = 7
    .ColumnWidths = "220;200;110;140;70;70;100"
End With

With Sheets("POSTAGE").Range("G:G")
    Set fndRng = .Find(What:="RECEIVED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not fndRng Is Nothing Then
        firstAddress = fndRng.Address
        Do
            ' check the date
            If Date - DateValue(fndRng.Offset(, -6)) < 90 Then
                cnt = cnt + 1
                ' add to listbox
                With Me.ListBox1
                    .AddItem fndRng.Offset(, -5).Value                      'CUSTOMER
                    .List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value    'ITEM
                    .List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value    'DATE
                    .List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value    'TRACKING NUMBER
                    .List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value     'CLAIM
                    .List(.ListCount - 1, 6) = fndRng.Value                 'RECEIVED NO SIG
                End With
            End If
            Set fndRng = .FindNext(fndRng)
        Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
    End If
End With

If cnt = 0 Then
    MsgBox "There are " & cnt & " records to display in the list box"
    End
End If

End Sub

 

[ipbr21054]

That did the trick .

Thanks