Edit for MsgBox to existing working code advice

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
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
 
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
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks but that doesnt show the listbox entries,its empty actually not shown
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
@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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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