How to catch Run-time error '91'

[[email protected]]

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

[Jake Marx]

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]

[email protected] 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

[Chip Pearson]

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



<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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
>
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >