Could you check & advise on my code please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
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

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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 ?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
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
 

Trixterz

Board Regular
Joined
Aug 15, 2019
Messages
59
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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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 ?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
After clicking on OK how cwn we stop the userform opening again,otherwise no point of showing the msgbox advising its empty.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,850
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
@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.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,236
Messages
5,576,890
Members
412,751
Latest member
ausrollo
Top