Run code when value has been pasted

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
Morning.
I have a userform of which it consists of 1 TextBox & 1 ListBox.

As i type in the TextBox the options in the ListBox narrow down depending on a match etc.
A character of - is automatically entered after the 5th & 8th character.
Like so 04721-TGG-G11 or 35113-SED-307
This works fine BUT can the code be edited to allow me to paste the value.
Currently if i paste a known value i see a MsgBox " NO SUCH NUMBER FOUND"

This is the value when i paste which gives me the error 35113TL4E01 BUT if i type it then all is good.

This is the code in use,

Rich (BB code):
Option Explicit

Private myList() As Variant
Private Sub ListBox1_Click()
    
       HondaParts.MyPartNumber.Text = ListBox1.Text
          Unload Me
       HondaParts.Show
End Sub

Private Sub TextBox1_Change()
       TextBox1 = UCase(TextBox1)
    Static NoMatch As Boolean
    
    If Len(TextBox1) > 0 Or NoMatch Then
        NoMatch = False
        ListBox1.Visible = True
        ListBox1.List = GetCutList()
        If IsEmpty(ListBox1.List(0)) Then
            MsgBox "NO SUCH NUMBER FOUND", vbCritical, "HONDA EPC NUMBER CHECK"
            ListBox1.List = myList
            NoMatch = True
            TextBox1 = vbNullString
            TextBox1.SetFocus
        End If
    Else
        ListBox1.Visible = False
    End If
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case Len(TextBox1)
        Case 5, 9
            With TextBox1
                .Text = .Text & "-"
                .SelStart = Len(.Text)
            End With
    End Select
End Sub
Private Sub UserForm_Initialize()
    myList = Range("Table24")
    ListBox1.List = myList
End Sub

Private Function GetCutList() As Variant()
    Dim i As Long
    Dim ret() As Variant
    Dim ret2() As Variant
    Dim x As Long
    
    ReDim ret(UBound(myList, 1), 0)
    For i = 1 To UBound(myList, 1)
        If myList(i, 1) Like "*" & TextBox1 & "*" Then
            ret(x, 0) = myList(i, 1)
            x = x + 1
        End If
    Next
    
    If x > 0 Then
        ReDim ret2(x - 1, 0)
        For i = 0 To x - 1
            ret2(i, 0) = ret(i, 0)
        Next
        GetCutList = ret2
    Else
        GetCutList = Array(Empty, Empty)
    End If
End Function
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You are using the KeyDown event for your text completion. Would recommend to use the Change event for this as well.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,831
Members
416,984
Latest member
dee10

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
Top