ReGzMir
New Member
- Joined
- Nov 7, 2016
- Messages
- 12
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).
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.
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.
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).
Code:
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.
Code:
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.