Run-Time Error 13 Type Mismatch

[Akashwani]

It's been a very long time since I've had the opportunity to do anything in Excel and my knowledge has diminished with the passing years, so I hope you have plenty of patience. I have created a sample database to try and get back into the Excel swing of things and I've spent the last 5 hours scratching my head over this problem.

I have two separate pieces of code that I have put together.
They both work fine together except for one aspect.
Once the data has been entered in to a UserForm it is copied to a Worksheet, no problems.
Other than, I get the "Run-Time Error 13 Type Mismatch" after the code has run on this line.

Me("Textbox" & p).Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), _

Sheet2.Range("A" & 2, "C" & q), p + 1, 0)

I hope someone is able to elevate the frustration for me.
Thanks in advance for all and any contributions.

''Code from here https://www.youtube.com/watch?v=Aye827qjYik''
Private Sub ComboBox1_Change()
Dim q, p As Long

q = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))

For p = 1 To 2
Me("Textbox" & p).Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), _
Sheet2.Range("A" & 2, "C" & q), p + 1, 0)

Next p
End Sub

''Code from here https://www.contextures.com/xlUserForm01.html""
Private Sub ProdData_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ProdData")

'find first empty row in database
''iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    ''SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
iRow = Sheets("ProdData").Cells(Rows.Count, 2).End(xlUp).Row + 1

'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
  '.Unprotect Password:="Password1"
  .Cells(iRow, 1).Value = Me.TxtLine.Value
  .Cells(iRow, 2).Value = Me.TxtOrderNo.Value
  .Cells(iRow, 3).Value = Me.ComboBox1.Value
  .Cells(iRow, 4).Value = Me.TextBox1.Value
  .Cells(iRow, 5).Value = Me.TextBox2.Value
  .Cells(iRow, 6).Value = Me.TxtQty.Value
  .Cells(iRow, 12).Value = Date 'New line added
  .Cells(iRow, 13).Value = Time 'New line added

' .Protect Password:="Password1"
End With

'clear the data
Me.TxtLine.Value = ""
Me.TxtOrderNo = ""
Me.ComboBox1.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TxtQty.Value = ""
Me.TxtLine.SetFocus

End Sub

 

[Fluff]

I got you, easy fix, simply change the Private Sub ComboBox1_Change() event to a Private Sub ComboBox1_Click() event instead

 

[Akashwani]

??
Thank you so much Fluff, that works a treat in my original Workbook, I really appreciate your time, patience and expertise.

Just curious here.
In my second Workbook, After I swap the Change to Click I still get the...
Run-Time error '1004':
Unable to get the VLookup property of the WorksheetFunction Class
Any suggestions, besides scrapping the Workbook!!


If you don't mind, as an extra question to my code.
What do I need to add/change to it so that Sheet2 can be a "named sheet" Eg, ProDatabase.

Thanks again

 

[Fluff]

Was the combo value in the lookup range?
Do avoid errors like that if the value isn't found use

Private Sub ComboBox1_Click()
    Dim Res As Variant
    
    Res = Application.VLookup(CLng(Me.ComboBox1.Value), Sheets("a sheet name").Range("A2:C" & q), p + 1, 0)
    If IsError(Res) Then
        MsgBox "value not found"
        Exit Sub
    End If
    Me.Controls("Textbox" & p).Value = Res

 

[Akashwani]

Thanks for the extra support here Fluff and the code.
Yes the value is in the look up range, everything seems to be the same between the 2 workbooks! I may have missed something though during my double checking!!

I have a feeling that I have failed to amend the code correctly!!
Because now, when I select the value in the ComboBox, the same value is loaded into TextBox1 & TextBox2

Private Sub ComboBox1_Click()
Dim q, p As Long
Dim Res As Variant
q = Application.WorksheetFunction.CountA(Sheets("ProductDatabase").Range("A:A"))
    
    Res = Application.VLookup(CLng(Me.ComboBox1.Value), Sheets("ProductDatabase").Range("A2:C" & q), p + 1, 0)
    If IsError(Res) Then
       MsgBox "value not found"
        Exit Sub
    End If
    For p = 1 To 2
   Me.Controls("Textbox" & p).Value = Res
   Next p
End Sub

Thanks.

 

[Fluff]

You need to do the lookup inside the loop like

Private Sub ComboBox1_Click()
    Dim q As Long, p As Long
    Dim Res As Variant
    q = Application.WorksheetFunction.CountA(Sheets("ProductDatabase").Range("A:A"))
    
    For p = 1 To 2
         Res = Application.VLookup(CLng(Me.ComboBox1.Value), Sheets("ProductDatabase").Range("A2:C" & q), p + 1, 0)
         If IsError(Res) Then
            MsgBox "value not found"
             Exit Sub
         End If
        Me.Controls("Textbox" & p).Value = Res
   Next p
End Sub

 

[Akashwani]

???

Thank you so much Fluff, as I'm sure you know, it works a treat.

I'm slowly building up a sample/example workbook and your help will now form a part of it, thanks again Fluff.
Keep your eyes peeled, there may be more questions coming!!

 

[Fluff]

Glad to help & thanks for the feedback