VBA Error on userform (Run-time error '91')

[cerberus1845]

Hi,

Hoping someone can help me - I have a userform that has 2 combo boxes on it. One is for Recruiter Name and the other is for Recruiter ID - these are listed in a table on a separate sheet. I need these to be linked so that when you select an Employee ID in the combobox then it automatically populates the Recruiter Name and vice versa. The code I have below *works as in the values populate correctly - when I attempt to update the record on the userform then I get an error (the data still updates but receive the following error:

Run-time error '91':

Object variable or With block variable not set:

This is the code I currently have- can someone explain where I've went wrong - I'm relatively new to VBA - any help would be amazing!!

Private Sub cmbRecruiterEmplID_Change()

    cmbRecruiterName.Value = [Table1].Find(cmbRecruiterEmplID, , xlValues).Offset(0, 1)

End Sub


Private Sub cmbRecruiterName_Change()

    cmbRecruiterEmplID.Value = [Table1].Find(cmbRecruiterName, , xlValues).Offset(0, -1)

End Sub

 

[cerberus1845]

ok so i had a play around with it and i *think* i fixed it - but happy to be corrected!:

Private Sub cmbRecruiterEmplID_Change()
    
    If cmbRecruiterName.Value <> "" Then
    cmbRecruiterName.Value = [Table1].Find(cmbRecruiterEmplID, , xlValues).Offset(0, 1)
    End If

End Sub

Private Sub cmbRecruiterName_Change()

    If cmbRecruiterEmplID.Value <> "" Then
    cmbRecruiterEmplID.Value = [Table1].Find(cmbRecruiterName, , xlValues).Offset(0, -1)
    End If

End Sub

 

[cerberus1845]

aagggh so fix one problem and another arises...

so the above code seems to work for updating data - and it can submit and update changes.. I'm having problems trying to get it to initiate correctly now when the user form loads!!?? - does anyone have any ideas what I've done wrong!? - highlighted in RED below... any help would be really gratefully appreciated!

' CODE WHEN USERFORM LOADS
Private Sub UserForm_Activate()

        Call Refresh_data
        
        
' CODE TO CHECK AUTONUMBERING ID
Dim LRow As Long 'Figure out Last Value in Range
Dim LRow2 As Long ' added this for the recruiter allocated to cost center lookup from other table - CHECK!!!
Dim OVal As String 'Old Value
Dim NVal As String 'New Value

'CODE TO AUTO INCREASE ID NUMBER
With Sheets("RecruiterAllocation") 'Sheet1
    If .Range("A2") = "" Then
        .Range("A2") = "1"
    Else
        LRow = .Cells(Rows.Count, "A").End(xlUp).Row
        OVal = .Range("A" & LRow)
        NVal = .Range("A" & LRow) + 1
        Me.txtID.Value = NVal
    End If
End With

    'Populate search combobox so it can be run from dashboard
    cmbSearch.List = Sheets("RecruiterAllocation").Range("G2:G" & LRow).Value
    
    
With Sheets("RecruiterDetails") 'Sheet1
    If .Range("A2") = "" Then
        .Range("A2") = "1"
    Else
        LRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
    End If
End With

[COLOR=rgb(209, 72, 65)][B]    'Populated recruiter Employee ID and Recruiter drop down boxes
    
    cmbRecruiterEmplID.List = Sheets("RecruiterDetails").Range("B2:B" & LRow2).Value
    
    cmbRecruiterName.List = Sheets("RecruiterDetails").Range("C2:C" & LRow2).Value[/B][/COLOR]
 

End Sub

 

[cerberus1845]

can anyone offer me any guidance on populating the two combo boxes 'cmbRecruiterEmplID' and 'cmbRecruiterName' when the userform initiates - the source data is a table called Table1 on a sheet called 'RecruiterDetails' - thanks!!

 

[Jon Peltier]

Here's how I would populate the combo boxes and keep them in sync.

Private Sub cmbRecruiterEmplID_Change()
  Me.cmbRecruiterEmplName.ListIndex = Me.cmbRecruiterEmplID.ListIndex
End Sub

Private Sub cmbRecruiterEmplName_Change()
  Me.cmbRecruiterEmplID.ListIndex = Me.cmbRecruiterEmplName.ListIndex
End Sub

Private Sub UserForm_Initialize()
  Dim r As Range
  Set r = Worksheets("Recruiter Details").Range("Table1")
  Me.cmbRecruiterEmplID.List = r.Columns(1).Value
  Me.cmbRecruiterEmplName.List = r.Columns(2).Value
  Me.cmbRecruiterEmplID.ListIndex = 0
End Sub

 

[cerberus1845]

Here's how I would populate the combo boxes and keep them in sync.

Private Sub cmbRecruiterEmplID_Change()
  Me.cmbRecruiterEmplName.ListIndex = Me.cmbRecruiterEmplID.ListIndex
End Sub

Private Sub cmbRecruiterEmplName_Change()
  Me.cmbRecruiterEmplID.ListIndex = Me.cmbRecruiterEmplName.ListIndex
End Sub

Private Sub UserForm_Initialize()
  Dim r As Range
  Set r = Worksheets("Recruiter Details").Range("Table1")
  Me.cmbRecruiterEmplID.List = r.Columns(1).Value
  Me.cmbRecruiterEmplName.List = r.Columns(2).Value
  Me.cmbRecruiterEmplID.ListIndex = 0
End Sub

Jon - AMAZING!!!! - thank you so much for this.. had to make a few tweaks on the naming - but seems to work as i'd hope!!! - appreciate your reply!!!!

Code for Combo Box Changes:

'CODE FOR CHANGES TO RECRUITER EMPLOYEE ID TO UPDATE RECRUITER NAME AUTOMATICALLY
Private Sub cmbRecruiterEmplID_Change()

  Me.cmbRecruiterName.ListIndex = Me.cmbRecruiterEmplID.ListIndex
  
End Sub


'CODE FOR CHANGES TO RECRUITER NAME TO UPDATE RECRUITER EMPLOYEE ID AUTOMATICALLY
Private Sub cmbRecruiterName_Change()

  Me.cmbRecruiterEmplID.ListIndex = Me.cmbRecruiterName.ListIndex

End Sub

Code when User form launches:

' CODE WHEN USERFORM LOADS
Private Sub UserForm_Activate()

        Call Refresh_data
        
        
' CODE TO CHECK AUTONUMBERING ID
Dim LRow As Long 'Figure out Last Value in Range
Dim OVal As String 'Old Value
Dim NVal As String 'New Value
Dim r As Range

'CODE TO AUTO INCREASE ID NUMBER
With Sheets("RecruiterAllocation") 'Sheet1
    If .Range("A2") = "" Then
        .Range("A2") = "1"
    Else
        LRow = .Cells(Rows.Count, "A").End(xlUp).Row
        OVal = .Range("A" & LRow)
        NVal = .Range("A" & LRow) + 1
        Me.txtID.Value = NVal
    End If
End With

    'Populate search combobox so it can be run from dashboard
    Me.cmbSearch.List = Sheets("RecruiterAllocation").Range("G2:G" & LRow).Value
    
    
With Sheets("RecruiterDetails")
    If .Range("A2") = "" Then
        .Range("A2") = "1"
    Else
        LRow = .Cells(Rows.Count, "A").End(xlUp).Row
    End If
    
End With
  
  Set r = Worksheets("RecruiterDetails").Range("Table1")
  Me.cmbRecruiterEmplID.List = r.Columns(2).Value
  Me.cmbRecruiterName.List = r.Columns(3).Value
  'Me.cmbRecruiterEmplID.ListIndex = 2

End Sub