Hi,
I am getting <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> Run-time error '13' - Type Mismatch - Error 2015 while trying to retrieve data from spreadsheet.
When entering data in the user form, its submitting properly to the data sheet. But when we try to retrieve the same data from the excel sheet to the same userform itself by using the unique id(INB serial NUM ) ,error comes
The code is given below and the error is occurring at line marked between *** ***, AND MARKED IN RED COLOR
I am getting <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> Run-time error '13' - Type Mismatch - Error 2015 while trying to retrieve data from spreadsheet.
When entering data in the user form, its submitting properly to the data sheet. But when we try to retrieve the same data from the excel sheet to the same userform itself by using the unique id(INB serial NUM ) ,error comes
The code is given below and the error is occurring at line marked between *** ***, AND MARKED IN RED COLOR
Code:
Option Explicit
Private Sub cboLocation_Change()
End Sub
Private Sub cboPart_Change()
End Sub
Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Dim ws As Worksheet
Set ws = Worksheets("PartsData")
'find first empty row in database
''lRow = ws.Cells(Rows.Count, 1) _
'' .End(xlUp).Offset(1, 0).Row
'revised code to avoid problems with
'Excel lists and tables in newer versions
lRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lPart = Me.cboPart.ListIndex
'check for a part number
If Trim(Me.cboPart.Value) = "" Then
Me.cboPart.SetFocus
MsgBox "ENTER STORER"
Exit Sub
End If
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.txtDate.Value
.Cells(lRow, 2).Value = Me.ComboBox1.Value
.Cells(lRow, 3).Value = Me.cboPart.List(lPart, 1)
.Cells(lRow, 4).Value = Me.txtQty.Value
.Cells(lRow, 5).Value = Me.TextBox1.Value
.Cells(lRow, 6).Value = Me.ComboBox2.Value
.Cells(lRow, 7).Value = Me.TextBox2.Value
.Cells(lRow, 8).Value = Me.cboLocation.Value
.Cells(lRow, 9).Value = Me.TextBox3.Value
.Cells(lRow, 10).Value = Me.TextBox4.Value
.Cells(lRow, 11).Value = Me.TextBox5.Value
.Cells(lRow, 12).Value = Me.TextBox6.Value
.Cells(lRow, 13).Value = Me.TextBox7.Value
.Cells(lRow, 14).Value = Me.TextBox8.Value
.Cells(lRow, 15).Value = Me.TextBox9.Value
.Cells(lRow, 16).Value = Me.TextBox10.Value
.Cells(lRow, 17).Value = Me.TextBox13.Value
.Cells(lRow, 18).Value = Me.TextBox14.Value
.Cells(lRow, 19).Value = Application.UserName
End With
'clear the data
Me.cboPart.Value = ""
Me.cboLocation.Value = ""
Me.txtDate.Value = Format(Date, "Medium")
Me.txtQty.Value = ""
Me.TextBox1.Value = ""
Me.cboPart.SetFocus
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
MsgBox "Database Updated"
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub ID_Activate()
ID.Value = "AA-" & GetCustomerId("Customers", "CustomerIDList", 8, 1, "-")
End Sub
Private Sub cmdid_Click()
Dim Low As Long
Dim High As Long
Dim r As Long
Low = 1111111
High = 9999999
r = Int((High - Low + 1) * Rnd() + Low)
DoEvents
Me.ComboBox1.Value = r
End Sub
Private Sub ComboBox2_Change()
End Sub
Private Sub Image1_Click()
End Sub
Private Sub Label11_Click()
End Sub
Private Sub Label2_Click()
End Sub
Private Sub Label5_Click()
End Sub
Private Sub Label7_Click()
End Sub
Private Sub Label8_Click()
End Sub
Private Sub Label9_Click()
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub TextBox4_Change()
End Sub
Private Sub txtDate_Change()
End Sub
Private Sub txtQty_Change()
End Sub
Private Sub UserForm_Initialize()
Dim cPart As Range
Dim cLoc As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each cPart In ws.Range("PartIDList")
With Me.cboPart
.AddItem cPart.Value
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
End With
Next cPart
For Each cLoc In ws.Range("LocationList")
With Me.cboLocation
.AddItem cLoc.Value
End With
Next cLoc
Me.txtDate.Value = Format(Date, "dd-mmmm-yyyy")
Me.txtQty.Value = ""
Me.cboPart.SetFocus
End Sub
Private Sub cmdSend_Click()
'Dim the variables
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
'change the number for the number of controls on the userform
cNum = 6
Set nextrow = Sheet3.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
MsgBox "The data has been sent"
'Clear the controls
cNum = 6
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Set nextrow = nextrow.Offset(0, 1)
Next
End Sub
Private Sub ComboBox1_AfterUpdate()
'Check to see if value exists
If WorksheetFunction.CountIf(wksPartsData.Range("B:B"), Me.ComboBox1.Value) = 0 Then
MsgBox "Incorrect ID"
Me.ComboBox1.Value = ""
Exit Sub
End If
'Lookup values based on first control
With Me
************************
[COLOR=#ff0000].TextBox2 = Application.WorksheetFunction.VLookup(CLng("Me.ComboBox1"), wksPartsData.Range("C2:S10"), 7, 0)
[/COLOR]
************************
'.Reg2 = Application.WorksheetFunction.VLookup(CLng(Me.Reg1), Sheet2.Range("Lookup"), 2, 0)
'Sheets("Database!").Range("A2:F62")
End With
MyerrorHandler:
If Err.Number = 1004 Then
MsgBox "Employee name not in the data!"
End If
End Sub
