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]

Try

Me.Controls("Textbox" & p).Value

 

[Akashwani]

Hello Fluf, thanks for your prompt reply.
Excuse my ignorance, I've added your code and I get the same outcome.

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

 

[Fluff]

How about

Sheet2.Range("A2:C" & q), p + 1, 0)

 

[Akashwani]

I'm sorry Fluff, the same error appears!

 

[Fluff]

In that case what is the value of ComboBox1 when you run the code.

 

[Akashwani]

Aplogises if I've explained this badly.

The ComboBox is used to list 9 digit numbers from a Named Range (in A2:A51), which it does successfully and loads TextBox1 & TextBox2 with the correct relevant data from the range A2:C51. The "Database" covers A2:F51, I'm only interested in returning the data from B2:C51.

I hope this helps in some way.

 

[Fluff]

which it does successfully and loads TextBox1 & TextBox2 with the correct relevant data from the range

In this case what is your problem?

Also you might want to change your signature as HTML code is not allowed on the site any more, so we have a brand new add-in for posting data & can be found here XL2BB

 

[Akashwani]

The problem I have Fluff is that once the data has been loaded to the UserForm and then copied to the worksheet when I click the "
ProdData" button, the screen goes straight to the VBA code and shows the Run-Time Error 13 Type Mismatch error.

I hope that makes sense.

 

[Akashwani]

I've created another Workbook, copied the data from the Worksheets over from my original Workbook and created a new UserForm with the exact same layout and VBA code.

When I run the code, I now get this error on the exact same line of code

Me.Controls("Textbox" & p).Value = Application.WorksheetFunction.VLookup(CLng(Me.ComboBox1.Value), _
Sheet2.Range("A2:C" & q), p + 1, 0)

Run-Time error '1004':
Unable to get the VLookup property of the WorksheetFunction Class

This appears as soon as I select the number in ComboBox1.
The code no longer runs to the end and then errors!