Edit for MsgBox to existing working code advice

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
This is the code in use.
My userform is populated with values in a listbox.
I can either just select a value from the listbox or i can start to type in the textbox which then narrows down the listbox entries.

So lets assume these are the values in the listbox
72147-123
72158-269
72147-333
etc etc

So if i type 72147-124 the listbox will be now empty because no match was found.
It is at this point i would like to see a MsgBox " NO SUCH NUMBER FOUND"
My attempt below does nothing Ha Ha

Rich (BB code):
Private Sub ListBox1_Click()
         
       HondaParts.MyPartNumber.Text = ListBox1.Text
              If ListBox1.Text = "" Then
       MsgBox "no such number found"
End If
       Unload Me
       HondaParts.Show

End Sub
 
L

Legacy 456155

Guest
If you are using the example I posted earlier, richh is spot on, but unaware of the listbox not having a listcount of 0, but it actually has a list assigned to a variant array with "Empty" in it. The function needed to return an array, so I used the Array function as such.
Array(Empty, Empty)
Array(Empty) would have worked as well.

I updated the example if you need to view it. Filter listbox as you type.xlsm in this folder.

VBA Code:
Private Sub TextBox1_Change()
    If Len(TextBox1) > 1 Then
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
        If IsEmpty(ListBox1.List(0)) Then MsgBox "NO SUCH NUMBER FOUND"
    Else
        ListBox1.Visible = False
    End If
End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

richh

Board Regular
Joined
Jun 24, 2007
Messages
109
Lets test something out. Open up your Immediate Window and put a debug stop on the Me.ListBox1.ListCount line. Then, type in an invalid code into the textbox. Copy "Me.ListBox1.ListCount". In the Immediate Window, type in a question mark, then paste what you copied. Press enter and let's see what that count says it's at.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Perfect worked great @dataluver
Now we have passed that it needs another edit "didnt know this as didnt get that far"
The MsgBox is shown
I click ok
Now i see a partial typed number in TextBox1
I need to basically remove this so textbox1 is empty & reload the listbox as if i just opened it for this first time
 
L

Legacy 456155

Guest
VBA Code:
Private Sub TextBox1_Change()
    If Len(TextBox1) > 1 Then
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
        If IsEmpty(ListBox1.List(0)) Then
            MsgBox "NO SUCH NUMBER FOUND"
            ListBox1.List = myList
            TextBox1 = vbNullString
            TextBox1.SetFocus
        End If
    Else
        ListBox1.Visible = False
    End If
End Sub
 

ipbr21054

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

ADVERTISEMENT

Thanks but that doesnt show the listbox entries,its empty actually not shown
 
L

Legacy 456155

Guest
See if this is acceptable. It does not clear the text, but selects it so the user can type over it.

VBA Code:
Private Sub TextBox1_Change()
 
    If Len(TextBox1) > 1 Then
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
        If IsEmpty(ListBox1.List(0)) Then
            MsgBox "NO SUCH NUMBER FOUND"
            ListBox1.List = myList
            TextBox1.SelStart = 0
            TextBox1.SelLength = Len(TextBox1)
            TextBox1.SetFocus
        End If
    Else
        ListBox1.Visible = False
    End If
End Sub

Here is another option:
VBA Code:
Private Sub TextBox1_Change()
    Static NoMatch As Boolean
    
    If Len(TextBox1) > 1 Or NoMatch Then
        NoMatch = False
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
        If IsEmpty(ListBox1.List(0)) Then
            MsgBox "NO SUCH NUMBER FOUND"
            ListBox1.List = myList
            NoMatch = True
            TextBox1 = vbNullString
            TextBox1.SetFocus
        End If
    Else
        ListBox1.Visible = False
    End If
End Sub
 

ipbr21054

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

ADVERTISEMENT

That does what you advised.

Or

This but rude

Rich (BB code):
Private Sub TextBox1_Change()

If Len(TextBox1) > 0 Then

ListBox1.Visible = True

ListBox1.List = GetCutList()

If IsEmpty(ListBox1.List(0)) Then

MsgBox "NO SUCH NUMBER FOUND", vbCritical, "HONDA EPC NUMBER CHECK"

Unload Me

HondaEpcNumbers.Show

End If

Else

ListBox1.Visible = False

End If

End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
@dataluver this worked spot on.

thanks for your time.

Rich (BB code):
Private Sub TextBox1_Change()
    Static NoMatch As Boolean
    
    If Len(TextBox1) > 1 Or NoMatch Then
        NoMatch = False
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
        If IsEmpty(ListBox1.List(0)) Then
            MsgBox "NO SUCH NUMBER FOUND"
            ListBox1.List = myList
            NoMatch = True
            TextBox1 = vbNullString
            TextBox1.SetFocus
        End If
    Else
        ListBox1.Visible = False
    End If
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,178
Messages
5,576,552
Members
412,731
Latest member
yaseen381
Top