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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
I thought this might work but i was wrong.
Do you see what im doing wrong here ?

Rich (BB code):
Private Sub ListBox1_Change()
If ListBox1.Text = "" Then
  MsgBox "NO SUCH PART NUMBER"
End If
TextBox1 = ""
TextBox1.SetFocus
End Sub

Basically when the listbox doesnt have a value in it the pop up a MsgBox
 
Upvote 0
Hi ipbr (which I hope means I'm Pabst Blue Ribbon),

I could be wrong, but a textbox may not be the best option for data entry validation. A textbox would work like a charm. You could also use a textbox for data entry and a listbox to immediately display results in your userform.

The issue that you're encountering is that the listbox.text does not = "" any time you enter something. So I could enter in "GODZILLA ATE MY DINGO BABY" and the code would never display the message.

You may consider something such as this pseudo code:

VBA Code:
Private Sub PartLookup_After_Update()

    Dim FoundPart

    If Me.TextBox1 <> "" Then
        For i = 1 to LastRowOfPartsList
            If Thisworkbook.worksheet("Parts List Sheet").Cells(i,[column]) = me.TextBox1 Then
                FoundPart = True
                Exit For
            End If
        next i
    Else
        Goto err
    End If
    
    If FoundPart = True Then
        MsgBox "Part # found on row " & i & " of Parts List"
    Else
        Msgbox "Part not Found"
    End If

err:
End Sub
 
Upvote 0
Hi,
How sure how to apply that & row info not needed.
As i type in textbox the entries in listbox narrows.
If listbox becomes empty or i type say w4w by mistake which would also make listbox empty i needsome kind of msgbox
 
Upvote 0
Here's code if you want to use a textbox to update the contents of a listbox. The listbox I'm referencing here have four columns, the last of which houses the row number the item was found on so if a user selects it, your code doesn't have to loop through your item list again to find it; it'll have the row number right there.

Code:
Private Sub PartNumTextBox_Change()
    Call UpdateList
End SUb

Public Function UpdateList
    Dim ws         As Worksheet
    Dim lRow    As Long
    Dim lBoxRow    As Long
    
    Set ws = Application.Thisworkbook.Worksheets("Parts List Sheet")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lBoxRow = 0
    
    YourUserFormName.PartsList.Clear
    
    For i=2 to lRow 'Assumes your list starts on row 2 to accomodate header row
        If UCase(ws.cells(i, 1)) Like UCase("*" & YourUserFormName.PartNumTextBox & "*") Then
            With YourUserFormName.PartsList
                .AddItem
                .List(lBoxRow, 0) = ws.cells(i, 1) 'Assumed Part Number Column
                .List(lBoxRow, 1) = ws.Cells(i,2) 'Assumed Part Descrition
                .List(lBoxRow, 2) = ws.Cells(i,3) 'Assumed Part uh... price? I dunno
                ,List(lBoxRow, 3) = i 'Store the row number for future use'
                lBoxRow = lBoxRow + 1
            End With
        End If
    Next i
End Function
 
Upvote 0
Hi,
The code i have in use works great so i just need a msgbox to advise no entries are left in listbox.
 
Upvote 0
Hi,
How sure how to apply that & row info not needed.
As i type in textbox the entries in listbox narrows.
If listbox becomes empty or i type say w4w by mistake which would also make listbox empty i needsome kind of msgbox

Ah OK. I see. Try
Code:
If Listbox1.ListCount = 0 Then
    MsgBox "Rawr"
End If
If ListBox1.ListCount = 0 Then
 
Upvote 0
If you mean like this.

Rich (BB code):
Private Sub ListBox1_Change()
If ListBox1.ListCount = 0 Then
    MsgBox "Rawr"
End If
TextBox1 = ""
TextBox1.SetFocus
End Sub

Then if i type say Z in textbox1 and i see no values in listbox1 i see no msg pop up
This is why i need such a thing
 
Upvote 0
The Change should be associated with your textbox, not listbox. textbox should
VBA Code:
Private Sub Textbox1_Change()
    [Run the code here to update you listbox upon the value ented in the textbox]
    
    If me.listbox.ListCount = 0 Then
        MsgBox "NO DEAL"
    End If
End Sub
 
Upvote 0
Do you mean like this ?

Rich (BB code):
Private Sub TextBox1_Change()
    If Len(TextBox1) > 0 Then
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
    Else
        ListBox1.Visible = False
    End If
        If Me.ListBox1.ListCount = 0 Then
        MsgBox "NO DEAL"
    End If
End Sub

If so still no msgbox
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,344
Members
448,570
Latest member
rik81h

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