Solved: Problem with indexing on a user form

[lanhao]

Hi there,

I'm working on a different project for work, and once piece of code is eluding me as to how the heck to do it. I put together a user form for work, and everything that is presently in there is working except for two things.

The one thing which I am primarily concerned about is the fact that when someone picks an item on in one of the drop downs (specifically the cbRep variable), it has another column next to it on the excel spreadsheet that has the name of their supervisor. How would I tell VBA to pull that information as well?

Specifically I was wondering where I should put the command in, so when it enters the information through the form, it will then do a lookup of the information in that additional column and put it into column #8.

Thanks for taking a look at this in advance. :)


Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lPart As Long
Set ws = Worksheets("QCDump")
Application.ScreenUpdating = True
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

lPart = Me.cbRep.ListIndex

'check for a comment
If Trim(Me.cbRep.Value) = "" Then
Me.cbRep.SetFocus
MsgBox "Please enter a rep name"
Exit Sub
End If
If Trim(Me.cboError.Value) = "" Then
Me.cboError.SetFocus
MsgBox "Please choose a category"
Exit Sub
End If
'copy the data to the database
With ws
.Cells(iRow, 4).Value = Me.cbRep.Value
.Cells(iRow, 2).Value = Me.tbAcct.Value
.Cells(iRow, 3).Value = Me.cboError.Value
.Cells(iRow, 5).Value = Me.tbInstDate.Value
.Cells(iRow, 6).Value = Me.tbReason.Value
.Cells(iRow, 9).Value = Me.cboNC.Value
.Cells(iRow, 7).Value = Me.cboCB.Value
.Cells(iRow, 1).Value = Format(Date)
.Cells(iRow, 11).Value = Environ("UserName")
.Cells(iRow, 8).Value = Me.cbRep.Value
End With
'clear the data
Me.cbRep.Value = ""
Me.tbAcct.Value = ""
Me.cboError.Value = ""
Me.tbInstDate.Value = ""
Me.tbReason.Value = ""
Me.cboNC.Value = ""
Me.cboCB.Value = ""
Application.ScreenUpdating = True
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim cFull As Range
Dim cbError As Range
Dim cbNC As Range
Dim cbCB As Range
Dim ws As Worksheet
Set ws = Worksheets("DataDump")

For Each cFull In ws.Range("FullName")
With Me.cbRep
.AddItem cFull.Value
.List(.ListCount - 1, 1) = cFull.Offset(0, 1).Value
End With
Next cFull
For Each cbError In ws.Range("ErrorType")
With Me.cboError
.AddItem cbError.Value
.List(.ListCount - 1, 1) = cbError.Offset(0, 1).Value
End With
Next cbError
For Each cbNC In ws.Range("Toggle")
With Me.cboNC
.AddItem cbNC.Value
.List(.ListCount - 1, 1) = cbNC.Offset(0, 1).Value
End With
Next cbNC
For Each cbCB In ws.Range("Toggle")
With Me.cboCB
.AddItem cbCB.Value
.List(.ListCount - 1, 1) = cbCB.Offset(0, 1).Value
End With
Next cbCB
Me.tbAcct.Value = ""
Me.tbInstDate.Value = ""
Me.tbReason.Value = ""
Me.cbRep.SetFocus
End Sub