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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

ipbr21054

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

richh

Board Regular
Joined
Jun 24, 2007
Messages
109
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
 

ipbr21054

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

richh

Board Regular
Joined
Jun 24, 2007
Messages
109

ADVERTISEMENT

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
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,793
Office Version
  1. 2007
Platform
  1. Windows
Hi,
The code i have in use works great so i just need a msgbox to advise no entries are left in listbox.
 

richh

Board Regular
Joined
Jun 24, 2007
Messages
109

ADVERTISEMENT

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
 

ipbr21054

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

richh

Board Regular
Joined
Jun 24, 2007
Messages
109
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
 

ipbr21054

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

Watch MrExcel Video

Forum statistics

Threads
1,118,887
Messages
5,574,837
Members
412,620
Latest member
sharma7s
Top