Combobox exit event search error
 

I have a combo box on a form for requestor, when the requestor is
selected I'm auto populating the email address box with the following
code (the list of requestors preloaded into the combo box is in
[data!e10:e21] and the email addresses are one column to the right)

Private Sub RequestorCBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myEmail As String
Dim myRequestor As String
Call TurnOff
myRequestor = RequestorCBox.Value
If RequestorCBox.Value < "" Then
Sheets("Data").Select
Range("e11:e21").Select
Application.FindFormat.NumberFormat = "General"
Selection.Find(what:=myRequestor, after:=ActiveCell,
LookIn:=xlValues, _
lookat:=xlPart, searchorder:=xlByColumns,
searchdirection:=xlNext, _
MatchCase:=False, searchformat:=False).Activate
ActiveCell.Offset(0, 1).Select
myEmail = ActiveCell.Value
Me.EmailBox.Value = myEmail
End If
End Sub

The problem I'm running into is that the list of requestors on the data
sheet isn't (and never will be) comprehensive. So from time to time
someone will have to type in their name, when this happens, the name
isn't found in the list, so this section of code can't continue. How
do I make it move on without auto populating the email box if the name
isn't found in that range?

Combobox exit event search error
 

This is just a quick stab, but it should work for you:

Option Explicit

Private Sub RequestorCBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myRequestor As String

' Cell to store the location of the name
Dim rgName As Range

' Cell to store the email address
Dim rgEMail As Range

Call TurnOff

myRequestor = Me.RequestorCBox.Value

If (myRequestor < "") Then
Application.FindFormat.NumberFormat = "General"

Set rgName = Sheets("Data").Range("E11:E21") _
.Find(What:=myRequestor, _
After:=Sheets("Data").Range("E11:E21"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False)

If (Not rgName Is Nothing) Then
Set rgEMail = rgName.Offset(0, 1)

Me.EmailBox.Value = rgEMail.Value
End If
End If
End Sub

Let me know if you have any troubles with it.

"Craig" wrote:

I have a combo box on a form for requestor, when the requestor is
selected I'm auto populating the email address box with the following
code (the list of requestors preloaded into the combo box is in
[data!e10:e21] and the email addresses are one column to the right)

Private Sub RequestorCBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myEmail As String
Dim myRequestor As String
Call TurnOff
myRequestor = RequestorCBox.Value
If RequestorCBox.Value < "" Then
Sheets("Data").Select
Range("e11:e21").Select
Application.FindFormat.NumberFormat = "General"
Selection.Find(what:=myRequestor, after:=ActiveCell,
LookIn:=xlValues, _
lookat:=xlPart, searchorder:=xlByColumns,
searchdirection:=xlNext, _
MatchCase:=False, searchformat:=False).Activate
ActiveCell.Offset(0, 1).Select
myEmail = ActiveCell.Value
Me.EmailBox.Value = myEmail
End If
End Sub

The problem I'm running into is that the list of requestors on the data
sheet isn't (and never will be) comprehensive. So from time to time
someone will have to type in their name, when this happens, the name
isn't found in the list, so this section of code can't continue. How
do I make it move on without auto populating the email box if the name
isn't found in that range?

Combobox exit event search error
 

I gave that a try, it still gives me the same run time error 13, type
mismatch on the search line. Am I right in thinking that this is
because it's searching and not finding anything withing the range that
it searches, or is something else causing this?

Combobox exit event search error
 

Ah yes. Try this:

Option Explicit

Private Sub RequestorCBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myRequestor As String

' Cell to store the location of the name
Dim rgName As Range

' Cell to store the email address
Dim rgEMail As Range

Call TurnOff

myRequestor = Me.RequestorCBox.Value

If (myRequestor < "") Then
Application.FindFormat.NumberFormat = "General"

On Error Resume Next

Set rgName = Sheets("Data").Range("E11:E21") _
.Find(What:=myRequestor, _
After:=Sheets("Data").Range("E11:E21"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
MatchCase:=False, SearchFormat:=False)

On Error GoTo 0

If (Not rgName Is Nothing) Then
Set rgEMail = rgName.Offset(0, 1)

Me.EmailBox.Value = rgEMail.Value
End If
End If
End Sub

The same as before, but with the two "On Error" lines added. Hopefully this
clears up your problem.

"Craig" wrote:

I gave that a try, it still gives me the same run time error 13, type
mismatch on the search line. Am I right in thinking that this is
because it's searching and not finding anything withing the range that
it searches, or is something else causing this?

Combobox exit event search error
 

thanks for all your help, that got me closer, but still wasn't working
quite right, as i was stepping that through it wasn't setting anything
as the email address (or the range). so i took your suggestions and
incorporated them into my original code, after some further debugging,
it works great, here's the code i ended up with, thanks again

Private Sub RequestorCBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myEmail As String
Dim myRequestor As String
Call TurnOff
myRequestor = RequestorCBox.Value
If RequestorCBox.Value < "" Then
Sheets("Data").Select
Range("e10:e21").Select
On Error Resume Next
Selection.Find(What:=myRequestor, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0
If (Not ActiveCell Is Nothing) Then
ActiveCell.Offset(0, 1).Select
myEmail = ActiveCell.Value
Me.EmailBox.Value = myEmail
End If
End If
End Sub

Combobox exit event search error
 

No problem, glad that I could help.

"Craig" wrote:

thanks for all your help, that got me closer, but still wasn't working
quite right, as i was stepping that through it wasn't setting anything
as the email address (or the range). so i took your suggestions and
incorporated them into my original code, after some further debugging,
it works great, here's the code i ended up with, thanks again

Private Sub RequestorCBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myEmail As String
Dim myRequestor As String
Call TurnOff
myRequestor = RequestorCBox.Value
If RequestorCBox.Value < "" Then
Sheets("Data").Select
Range("e10:e21").Select
On Error Resume Next
Selection.Find(What:=myRequestor, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
searchdirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
On Error GoTo 0
If (Not ActiveCell Is Nothing) Then
ActiveCell.Offset(0, 1).Select
myEmail = ActiveCell.Value
Me.EmailBox.Value = myEmail
End If
End If
End Sub