VBA Worksheet Funtion: Error handling for Vlookup

[ReGzMir]

Hi All,

Hope Thursday finds you well.

Still learning the ropes in VBA. Here's what I have:

I have a user form with 2 combo boxes namely: ComboBoxTeamMngr and ComboBoxAgentName.

The ComboBoxTeamMngr is based on a rowsource defined in in Sheet 1. ComboBoxAgentName content is dependent on the values of the ComboBoxTeamMngr. These all works fine.

I then have 2 textboxes namely: TextBoxTeamManagerEmail and TextBoxAgentEmail. Both these textboxes are located on the same form but hidden at the back of the frame (cant be seen by the user). The purpose of these two textboxes is to receive values/results from a vlookup procedure when ComboBoxAgentName has been changed (Change event).

Then I have a sheet named as "Roster" where it contains the names of the agent (column 1), their email address (column 7) and their Managers email address (column 8).

I then have an send email procedure in a module (I got the base code from the links shared on other threads pointing to the very helpful website of RonDeBrun).

Here's a quick brief of what I was trying to do:
>User selects a manager from ComboBoxTeamMngr

>User then selects an agent under the Managers Name from ComboBoxAgentName. List on ComboBoxAgentName is dependent to the values of ComboBoxTeamMngr.

>On ComboxBoxAgentname change, a worksheet vlookup function (please see code below) will be triggered and performs a lookup from the values written in the cells (2,1) of the "Roster" worksheet. This looks for the email address of the agent in column 7 and column 8.

>if all goes well, TextBoxTeamManagerEmail and TextBoxAgentEmail will then be populated with the results of the vlookup results.

>Now, the contents of TextBoxTeamManagerEmail and TextBoxAgentEmail will then be assigned to the ".To" and ".CC" of an email procedure on Module1 (not shown on the code below).

Private Sub ComboBoxAgentName_Change()
Dim ws1 As Worksheet
Dim Result As String
Dim Result2 As String
               
   
    Set ws1 = Worksheets("Roster")
    ws1.Cells(2, 1).Value = FormMain.ComboBoxAgentName.Value
    
    Result = Application.WorksheetFunction.VLookup(Sheets("Roster").Range("A2"), _
    Sheets("Roster").Range("C4:H112"), 5, False)
    TextBoxAgentEmail.Value = Result
    
    Result2 = Application.WorksheetFunction.VLookup(Sheets("Roster").Range("A2"), _
    Sheets("Roster").Range("C4:H112"), 6, False)
    TextBoxTeamManagerEmail.Value = Result2    
   
End Sub

It works fine at some point. But generates an error on the following instance:

>when user has populated ComboBoxTeamMngr and ComboBoxAgentName, then changes his mind and wants to reselect another Manager in the ComboBoxTeamMngr.

>when user has populated ComboBoxTeamMngr and ComboBoxAgentName, then changes his mind and wants to reselect another Agent Name in the CComboBoxAgentName.

>if user presses space bar or backspace to try deleting a selected agent name in the ComboBoxAgentName.
I tried adding error catching after the lookup script but it didn't help. Every time I select a name in ComboBoxAgentName, it now returns an error.

Private Sub ComboBoxAgentName_Change()
Dim ws1 As Worksheet
Dim Result As String
Dim Result2 As String
        
        
    'Lookup the email address of the Audited agent and Team Manager from the Roster sheet
    Set ws1 = Worksheets("Roster")
    ws1.Cells(2, 1).Value = FormMain.ComboBoxAgentName.Value
    
    Result = Application.WorksheetFunction.VLookup(Sheets("Roster").Range("A2"), _
    Sheets("Roster").Range("C4:H112"), 5, False)
    TextBoxAgentEmail.Value = Result
    
    Result2 = Application.WorksheetFunction.VLookup(Sheets("Roster").Range("A2"), _
    Sheets("Roster").Range("C4:H112"), 6, False)
    TextBoxTeamManagerEmail.Value = Result2
    
    On Error GoTo ErrorMessage:
    
ErrorMessage:
    MsgBox ("Incorrect Entry!" & vbLf & "Please select from the list provided")    
    On Error Resume Next
    
End Sub

Is there a way to better do this other than doing a worksheet vlookup function?

I Would greatly appreciate any help or any other suggestions on work-arounds.

Apologies for a very lengthy post.

TIA.

 

[Norie]

Perhaps something like this.

Private Sub ComboBoxAgentName_Change()
Dim ws1 As Worksheet
Dim Result As Variant

    Set ws1 = Worksheets("Roster")
    
    ws1.Cells(2, 1).Value = FormMain.ComboBoxAgentName.Value

    Result = Application.VLookup(Sheets("Roster").Range("A2"), _
                                 Sheets("Roster").Range("C4:H112"), 5, False)
    If Not IsError(Result) Then
        TextBoxAgentEmail.Value = Result
    Else
        MsgBox "An appropriate message for the user."
    End If

    Result = Application.VLookup(Sheets("Roster").Range("A2"), _
                                 Sheets("Roster").Range("C4:H112"), 6, False)
    If Not IsError(Result) Then
        TextBoxTeamManagerEmail.Value = Result
    Else
        MsgBox "An appropriate message for the user."
    End If
    
End Sub

PS If you don't want to use VLookup you might want to look at using the ListIndex property of the comboboxes.

If, as it appears from your post, the comboboxes are popuilated from contiguous ranges then you should be able to use the ListIndex of the combobox to determine which row in the range the value they've selected is in.

 

[ReGzMir]

Hi Norie, thank you for your suggestion.

I tried following the modifications you made and it seems to work fine if user has selected correct entries for the two combo boxes.

However, the error 1004 happens with the following instance:

>when user has populated ComboBoxTeamMngr and ComboBoxAgentName, then changes his mind and wants to reselect another Manager in the ComboBoxTeamMngr. This shows the Debug context screen.

> when user has populated ComboBoxTeamMngr and ComboBoxAgentName, then changes his mind and wants to reselect another Agent Name in the CComboBoxAgentName. This shows the Debug context screen.

> if user presses space bar or backspace to try deleting a selected agent name in the ComboBoxAgentName. This shows the Debug context screen.


One thing I noticed, there's no chance to show the msgbox "An Appropriate Message to the user" on any of the tests I did. I am thinking that this is because, the error is generated AFTER both IF Statements have been completed. Perhaps additional error catching can be done once the process goes out of these if statements loop already. I'm just now sure how to do it.

Also, I am not familiar how listindex works. Let me do a quick research and try playing with it. I'll share my results. I'm hoping it's a better alterative to vlookup.

Thanks again Norie.

 

[Norie]

The code I posted shouldn't generate any runtime errors.

If there is a problem with the VLookups then the variable Result will be set to an error value which is then checked with IsError.

Can you post the code you actually used?

 

[ReGzMir]

The code I posted shouldn't generate any runtime errors.

If there is a problem with the VLookups then the variable Result will be set to an error value which is then checked with IsError.

Can you post the code you actually used?

Hi Norie, I did just what you coded above. But hey, I think I found a way to prevent the error or prevent the debug menu to appear.

Works ok now. Here's how I did it:

Private Sub ComboBoxAgentName_Change()
Dim ws1 As Worksheet
Dim Result As String
Dim Result2 As String
        
        
    Set ws1 = Worksheets("Roster")
    ws1.Cells(2, 1).Value = FormMain.ComboBoxAgentName.Value
    
    On Error Resume Next
    
    Result = Application.WorksheetFunction.VLookup(Sheets("Roster").Range("A2"), _
    Sheets("Roster").Range("C4:H112"), 5, False)
    TextBoxAgentEmail.Value = Result
   
    
    Result2 = Application.WorksheetFunction.VLookup(Sheets("Roster").Range("A2"), _
    Sheets("Roster").Range("C4:H112"), 6, False)
    TextBoxTeamManagerEmail.Value = Result2
    
       
    If Err.Number <> 0 Then
        MsgBox "An appropriate message to the user"
        
    End If
    On Error GoTo 0
    
End Sub

 

[Norie]

If you used the exact code I posted you would not have received any runtime errors.