Dear All,
I am trying to run a userform where a TextBox value is based on the selection of a ComboBox, i.e. the user would select a service and theform would bring up a predefined price from a table with vlookup. The codes currently look like this:
Private Sub CboService_Change()
With Me
.TextRev = Application.VLookup(Me.CboService.Value, Worksheets("Param").Range("Services"), 2, 0)
End With
End Sub
and
Private Sub CommandButton1_Click()
Dim lRow As Long
Dim lEmployee As String
Dim lCustomer As String
Dim lTime As String
Dim lService As Long
Dim lServ2 As String
Dim lProduct As String
Dim lQuant As Integer
Dim lRev As Integer
Dim lPay As String
Dim ws As Worksheet
Set ws = Worksheets(2)
'find first empty row in database
lRow = ws.Cells.Find(WHAT:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lEmployee = Me.CboUser.ListIndex
m1 = Application.VLookup("m1", Worksheets("Param").Range("SysM"), 2, 0)
'check for a valid Employee entry
If Me.CboUser.Value = "" Then
MsgBox m1
Exit Sub
End If
lCustomer = Me.TextGuest.Value
lTime = Me.TextTime.Value
lService = Me.CboService.ListIndex
lProduct = Me.CboProduct.ListIndex
'check for separate product line
m8 = Application.VLookup("m8", Worksheets("Param").Range("SysM"), 2, 0)
If Me.CboService.Value <> "" And Me.CboProduct.Value <> "" Then
MsgBox m8
Exit Sub
End If
lQuant = Me.TextQuant.Value
lRev = Me.TextRev.Value
'check for Revenue entry
m9 = Application.VLookup("m9", Worksheets("Param").Range("SysM"), 2, 0)
If lRev = 0 Then
MsgBox m9
Exit Sub
End If
lPay = Me.CboPymnt.Value
'check for a valid Payment mode entry
m3 = Application.VLookup("m3", Worksheets("Param").Range("SysM"), 2, 0)
If lPay = "" Then
MsgBox m3
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(lRow, 2).Value = Me.CboUser.Value
.Cells(lRow, 3).Value = lCustomer
.Cells(lRow, 4).Value = Format(Date, "yyyy.mm.dd")
.Cells(lRow, 5).Value = lTime
.Cells(lRow, 6).Value = lServ2
.Cells(lRow, 7).Value = Me.CboProduct.Value
.Cells(lRow, 8).Value = lQuant
.Cells(lRow, 9).Value = lRev
.Cells(lRow, 10).Value = lPay
' .Protect Password:="password"
End With
'clear the data
Me.CboUser.Value = ""
Me.CboService.Value = ""
Me.CboProduct.Value = ""
Me.CboPymnt.Value = ""
Me.CboUser.SetFocus
End Sub
The system currently gives the infamous error message of: Run-time error '-2147352571 (80020005)": Could not set the Value property. Type mismatch.
Any advice/help/hint would be greatly appreciated
I am trying to run a userform where a TextBox value is based on the selection of a ComboBox, i.e. the user would select a service and theform would bring up a predefined price from a table with vlookup. The codes currently look like this:
Private Sub CboService_Change()
With Me
.TextRev = Application.VLookup(Me.CboService.Value, Worksheets("Param").Range("Services"), 2, 0)
End With
End Sub
and
Private Sub CommandButton1_Click()
Dim lRow As Long
Dim lEmployee As String
Dim lCustomer As String
Dim lTime As String
Dim lService As Long
Dim lServ2 As String
Dim lProduct As String
Dim lQuant As Integer
Dim lRev As Integer
Dim lPay As String
Dim ws As Worksheet
Set ws = Worksheets(2)
'find first empty row in database
lRow = ws.Cells.Find(WHAT:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
lEmployee = Me.CboUser.ListIndex
m1 = Application.VLookup("m1", Worksheets("Param").Range("SysM"), 2, 0)
'check for a valid Employee entry
If Me.CboUser.Value = "" Then
MsgBox m1
Exit Sub
End If
lCustomer = Me.TextGuest.Value
lTime = Me.TextTime.Value
lService = Me.CboService.ListIndex
lProduct = Me.CboProduct.ListIndex
'check for separate product line
m8 = Application.VLookup("m8", Worksheets("Param").Range("SysM"), 2, 0)
If Me.CboService.Value <> "" And Me.CboProduct.Value <> "" Then
MsgBox m8
Exit Sub
End If
lQuant = Me.TextQuant.Value
lRev = Me.TextRev.Value
'check for Revenue entry
m9 = Application.VLookup("m9", Worksheets("Param").Range("SysM"), 2, 0)
If lRev = 0 Then
MsgBox m9
Exit Sub
End If
lPay = Me.CboPymnt.Value
'check for a valid Payment mode entry
m3 = Application.VLookup("m3", Worksheets("Param").Range("SysM"), 2, 0)
If lPay = "" Then
MsgBox m3
Exit Sub
End If
'copy the data to the database
'use protect and unprotect lines,
' with your password
' if worksheet is protected
With ws
' .Unprotect Password:="password"
.Cells(lRow, 2).Value = Me.CboUser.Value
.Cells(lRow, 3).Value = lCustomer
.Cells(lRow, 4).Value = Format(Date, "yyyy.mm.dd")
.Cells(lRow, 5).Value = lTime
.Cells(lRow, 6).Value = lServ2
.Cells(lRow, 7).Value = Me.CboProduct.Value
.Cells(lRow, 8).Value = lQuant
.Cells(lRow, 9).Value = lRev
.Cells(lRow, 10).Value = lPay
' .Protect Password:="password"
End With
'clear the data
Me.CboUser.Value = ""
Me.CboService.Value = ""
Me.CboProduct.Value = ""
Me.CboPymnt.Value = ""
Me.CboUser.SetFocus
End Sub
The system currently gives the infamous error message of: Run-time error '-2147352571 (80020005)": Could not set the Value property. Type mismatch.
Any advice/help/hint would be greatly appreciated
