Akashwani
Well-known Member
- Joined
- Mar 14, 2009
- Messages
- 2,911
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.
I hope someone is able to elevate the frustration for me.
Thanks in advance for all and any contributions.
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.
VBA Code:
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.
VBA Code:
''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