Could you check & advise on my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Evening,

I am using the below code on a userform which consists of ListBox1.
When the code runs it looks in column G for the values shown in RED below
It then places these values in the ListBox1 like photo attached.

As time goes on this list will get larger / smaller BUT what ive noticed is that when the value LOST isnt in the list i then see the msg as shown in BLUE below.
I dont understand what is happening as the other values are present on the worksheet.

Not sure if this points to anything but the first entry has a thin border around it ?

Can you see where i went wrong Thanks

Rich (BB code):
Private Sub ListBox1_Click()
  Range("A" & ListBox1.List(ListBox1.ListIndex, 4)).Select
  Unload PostalIssueForm

End Sub

Private Function add_val(a As String)

      Dim r As Range, f As Range, cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        
        .ColumnCount = 4
        .ColumnWidths = "180;230;250;10"

        Set r = Range("G8", Range("G" & Rows.Count).End(xlUp))
        
        Set f = r.Find(a, LookIn:=xlValues, LookAt:=xlPart)
        If Not f Is Nothing Then
          cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
              .AddItem f.Value, i                 'POSTAL ISSUE COLUMN
              .List(i, 1) = f.Offset(, -5).Value  'NAME
              .List(i, 2) = f.Offset(, -4).Value  'ITEM
              .List(i, 3) = f.Offset(, -6).Value  'DATE
              .List(i, 4) = f.Row                 'ROW
              added = True
              Exit For
          End Select

            Next
            If added = False Then
          .AddItem f.Value                                 'POSTAL ISSUE COLUMN
          .List(.ListCount - 1, 1) = f.Offset(, -5).Value  'NAME
          .List(.ListCount - 1, 2) = f.Offset(, -4).Value  'NAME
          .List(.ListCount - 1, 3) = f.Offset(, -6).Value  'DATE
          .List(.ListCount - 1, 4) = f.Row                 'ROW
        End If

            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          ComboBox1 = UCase(ComboBox1)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
          ComboBox1.Value = ""
          .SetFocus
        End If
      End With
End Function
Private Sub UserForm_Initialize()

Call add_val("LOST")
Call add_val("RECEIVED NO DATE")
Call add_val("RETURNED")
Call add_val("UNKNOWN")
End Sub
 

Attachments

  • 790.jpg
    790.jpg
    166.1 KB · Views: 3

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Just an update.
Below that code shown in BLUE the ComBox1.Value = "" and also .SetFocus ahs been deleted.

It looks like the first word in the list is the issue.
Once that word isnt found i see the Msg but once i click on ok the other words continue to load.

So the Msg is advising me that the word its looking for "inthis case LOST isnt there" so pops up the Msg.
Is this just a matter of moving the Msg as i only need to be told the Msg when NONE are found ?
 
Upvote 0
Can you advise please how i correct add into the code that if the ListBox is empty then show Msg.
I added the line of code in RED but ListBox shows its empty but no Msg was shown


Rich (BB code):
            If added = False Then
          .AddItem f.Value                                 'POSTAL ISSUE COLUMN
          .List(.ListCount - 1, 1) = f.Offset(, -5).Value  'NAME
          .List(.ListCount - 1, 2) = f.Offset(, -4).Value  'NAME
          .List(.ListCount - 1, 3) = f.Offset(, -6).Value  'DATE
          .List(.ListCount - 1, 4) = f.Row                 'ROW
        End If

            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          ComboBox1 = UCase(ComboBox1)
          .TopIndex = 0
        Else
        If ListBox1.Value = "" Then
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
        End If
        End If
      End With
End Function
 
Upvote 0
Can you advise please how i correct add into the code that if the ListBox is empty then show Msg.
I added the line of code in RED but ListBox shows its empty but no Msg was shown


Rich (BB code):
            If added = False Then
          .AddItem f.Value                                 'POSTAL ISSUE COLUMN
          .List(.ListCount - 1, 1) = f.Offset(, -5).Value  'NAME
          .List(.ListCount - 1, 2) = f.Offset(, -4).Value  'NAME
          .List(.ListCount - 1, 3) = f.Offset(, -6).Value  'DATE
          .List(.ListCount - 1, 4) = f.Row                 'ROW
        End If

            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          ComboBox1 = UCase(ComboBox1)
          .TopIndex = 0
        Else
        If ListBox1.Value = "" Then
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
        End If
        End If
      End With
End Function

How about this...

VBA Code:
            If added = False Then
          .AddItem f.Value                                 'POSTAL ISSUE COLUMN
          .List(.ListCount - 1, 1) = f.Offset(, -5).Value  'NAME
          .List(.ListCount - 1, 2) = f.Offset(, -4).Value  'NAME
          .List(.ListCount - 1, 3) = f.Offset(, -6).Value  'DATE
          .List(.ListCount - 1, 4) = f.Row                 'ROW
        End If

            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          ComboBox1 = UCase(ComboBox1)
          .TopIndex = 0
        Else
        If ListBox1.ListCount = 0 Then
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
        End If
        End If
      End With
End Function
 
Upvote 0
Hi,
That then pops up the Msg when ListBox1 is empty.
Upon clicking OK the userform then opens up showing its empty,is it in a loop ?
 
Upvote 0
After clicking on OK how cwn we stop the userform opening again,otherwise no point of showing the msgbox advising its empty.
 
Upvote 0
@ipbr21054

Could you please at least try to give your threads a meaningful title rather than the same one for all of them? Thank you.
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top