Hi All,

I am working in excel VBA. I have a userform with a combo box. I have a
list of names in a worksheet.

I need a textbox to populate the employee code with respect to the name
in the combo box.

To achieve this I have used 'find' function to find the name on
combobox_change event which finds the name and shows up the
corresponding employee code.

This works fine till the user selects from the dropdown. However, if i
am trying to type in a name which does not exist on the sheet, it gives
me Run-time error " 91 - Object Variable or with block variable not
set" which is correct.

I want to capture this event and inform the user that this name does
not exist in the spreadsheet.

Can someone please help me to do this. Thanks in advance.

Yash

Hi Yash,

The easiest way to do this is to set a results of the Find method to a Range
object, which you're probably already doing. Then you can just see if the
reference Is Nothing - if so, the Find didn't return a reference.

Here's an example:

Sub demo()
Dim sSearch As String
Dim rngSearch As Range

sSearch = "test"

Set rngSearch = Sheet1.UsedRange.Find(sSearch)
If Not rngSearch Is Nothing Then
'/ do your stuff here
Else
MsgBox "Unable to find '" & sSearch & _
"'.", vbExclamation, "Not Found"
End If

Set rngSearch = Nothing
End Sub

Alternatively, you could trap for error 91:

On Error Resume Next
'/ your code that may cause err 91
If Err.Number = 91 Then
MsgBox "Error"
End If

--
Regards,

Jake Marx
www.longhead.com


[please keep replies in the newsgroup - email address unmonitored]

wrote:
Hi All,

I am working in excel VBA. I have a userform with a combo box. I have
a list of names in a worksheet.

I need a textbox to populate the employee code with respect to the
name in the combo box.

To achieve this I have used 'find' function to find the name on
combobox_change event which finds the name and shows up the
corresponding employee code.

This works fine till the user selects from the dropdown. However, if i
am trying to type in a name which does not exist on the sheet, it
gives me Run-time error " 91 - Object Variable or with block variable
not set" which is correct.

I want to capture this event and inform the user that this name does
not exist in the spreadsheet.

Can someone please help me to do this. Thanks in advance.

Yash

Try something like

On Error Resume Next
' your code that may cause an error
If Err.Number = 91 Then
MsgBox "Invalid Name"
Exit Sub
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



wrote in message
oups.com...
Hi All,

I am working in excel VBA. I have a userform with a combo box.
I have a
list of names in a worksheet.

I need a textbox to populate the employee code with respect to
the name
in the combo box.

To achieve this I have used 'find' function to find the name on
combobox_change event which finds the name and shows up the
corresponding employee code.

This works fine till the user selects from the dropdown.
However, if i
am trying to type in a name which does not exist on the sheet,
it gives
me Run-time error " 91 - Object Variable or with block variable
not
set" which is correct.

I want to capture this event and inform the user that this name
does
not exist in the spreadsheet.

Can someone please help me to do this. Thanks in advance.

Yash

Hi Jake and Chip,

I tried option two. It works. Thanks a ton...

Chip Pearson wrote:
Try something like

On Error Resume Next
' your code that may cause an error
If Err.Number = 91 Then
MsgBox "Invalid Name"
Exit Sub
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



wrote in message
oups.com...
Hi All,

I am working in excel VBA. I have a userform with a combo box.
I have a
list of names in a worksheet.

I need a textbox to populate the employee code with respect to
the name
in the combo box.

To achieve this I have used 'find' function to find the name on
combobox_change event which finds the name and shows up the
corresponding employee code.

This works fine till the user selects from the dropdown.
However, if i
am trying to type in a name which does not exist on the sheet,
it gives
me Run-time error " 91 - Object Variable or with block variable
not
set" which is correct.

I want to capture this event and inform the user that this name
does
not exist in the spreadsheet.

Can someone please help me to do this. Thanks in advance.

Yash