Excel VBA Vlookup after combobox

[markuk33]

Hi All,

Im fairly new to excel VBA but needed to learn for work.

I have a created a userform to monitor performance and really needing help with one section of it.
The premise of the userform is to select a job, who is on the job, time stamp when starting and then recalling a given job number to put in finish quantity.
It works and I am happy with it besides one section. Instead of a massive process combo box list (100+ items) I wanted to split it down by section so that the process listy changes depending on the area selected in another.

I have that part working however my vlookup's dont work to pull through information regarding the process selected whereas they did before when it was just one big list.

I am happy to send the excel file or here is parts of the code i think is the issue



Private Sub cboprocess_afterupdate()
'check to see if value exists
If WorksheetFunction.CountIf(Sheet8.Range("B:B"), Me.cboprocess.Value) = 0 Then
MsgBox "This is an incorrect Job Number"
Me.cboprocess.Value = ""
Exit Sub
End If
'lookup values based on first control
With Me
On Error Resume Next


.txtdesc = Application.WorksheetFunction.VLookup((Me.cboprocess), Sheet8.Range("B:F"), 2, 0)
.txtuom2 = Application.WorksheetFunction.VLookup((Me.cboprocess), Sheet8.Range("B:F"), 5, 0)
.txttarget = Application.WorksheetFunction.VLookup((Me.cboprocess), Sheet8.Range("B:F"), 3, 0)

On Error Resume Next

.txtdesc = Application.WorksheetFunction.VLookup(CLng(Me.cboprocess), Sheet8.Range("B:F"), 2, 0)
.txtuom2 = Application.WorksheetFunction.VLookup(CLng(Me.cboprocess), Sheet8.Range("B:F"), 5, 0)
.txttarget = Application.WorksheetFunction.VLookup(CLng(Me.cboprocess), Sheet8.Range("B:F"), 3, 0)

End With

End Sub


Private Sub UserForm_Initialize()
Me.txtdate.Text = Now

With cboarea
.AddItem "Spheres - Milling Room"
.AddItem "Cones - Milling Room"
.AddItem "Wedding - Milling Room"
.AddItem "Cylinders - Milling Room"
.AddItem "Florettes & Jumbos - Brick Room"
.AddItem "Final Assembly/Lady Packs"
.AddItem "Auto Line"
.AddItem "Brick Line"

End With

End Sub

Private Sub cboarea_Change()

Dim index As Integer
index = cboarea.ListIndex

Select Case index
Case Is = 0
With Me.cboprocess
.RowSource = "Spheres"
End With
Case Is = 1
With Me.cboprocess
.RowSource = "Cones"
End With
Case Is = 2
With Me.cboprocess
.RowSource = "Wedding"
End With
Case Is = 3
With Me.cboprocess
.RowSource = "Cylinders"
End With
Case Is = 4
With Me.cboprocess
.RowSource = "Florettes"
End With
Case Is = 5
With Me.cboprocess
.RowSource = "Fassembly"
End With
Case Is = 6
With Me.cboprocess
.RowSource = "Auto"
End With
Case Is = 7
With Me.cboprocess
.RowSource = "Brickline"
End With
End Select

End Sub