VBA Userform TextBox value dependant on ComboBox selection

[anteus]

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

 

[sanj_edu]

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

Department	A	B	C_	D	E	F	G	H	I	J	K
A	1	4	8	14	20	29	31	36	49	54	58
B	2	5	9	15	21	30	32	37	50	55	59
C_	3	6	10	16	22		33	38	51	56	60
D		7	11	17	23		34	39	52	57	61
E			12	18	24		35	40	53
F			13	19	25			41
G					26			42
H					27			43
I					28			44
J								45
K								46
								47
								48

<tbody>
</tbody><colgroup><col><col span="11"></colgroup>

Private Sub ComboBox1_Change()
Me.ComboBox2 = ""

Select Case Me.ComboBox1
Case "Department"
Me.ComboBox2.RowSource = "Department"
Case "A"
Me.ComboBox2.RowSource = "A"
Case "B"
Me.ComboBox2.RowSource = "B"
Case "C_"
Me.ComboBox2.RowSource = "C_"
Case "D"
Me.ComboBox2.RowSource = "D"
Case "E"
Me.ComboBox2.RowSource = "E"
Case "F"
Me.ComboBox2.RowSource = "F"
Case "G"
Me.ComboBox2.RowSource = "G"
Case "H"
Me.ComboBox2.RowSource = "H"
Case "I"
Me.ComboBox2.RowSource = "I"
Case "J"
Me.ComboBox2.RowSource = "J"
Case "K"
Me.ComboBox2.RowSource = "K"
End Select



End Sub
Private Sub UserForm_Initialize()

Dim lastrow As Long
Dim lastcolumn As Long
Dim i As Integer

lastcolumn = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
With Worksheets("Sheet1")

For i = 1 To lastcolumn
With .Columns(i)
lastrow = Sheet1.Cells(Rows.Count, i).End(xlUp).Row
With Range(Cells(1, i), Cells(lastrow, i))
Range(Cells(1, i), Cells(lastrow, i)).Select
Selection.CreateNames Top:=True
End With
End With
Next i
End With

Me.ComboBox1.RowSource = "Department"




End Sub