Dim BadgeNumber as String
Dim lBadge as Long

BadgeNumber = InputBox(prompt:="Please enter Collar Number" _
& Chr(13) & "(with leading zeros as appropriate)" _
& Chr(13) & "or just Surname for Inspecting ranks or Police Staff.")


if isnumeric(BadgeNumber) then
lBadge = clng(BadgeNumber)
Select Case lBadge
Case 1 To 1000

Case 1001 to 2000 etc...

Case Else

End Select
Else
msgbox badgenumber
End if

Would be the simple solution. The reason your code is failing is that you
get there because an error is raised. You then try to error trap in your
error handler. This isn't allowed and Excel quits since you have an error
in your err handler. (you may not need the case else or use it to signal
an invalid numeric badge number. Also, you still have BadgeNumber with
leading zeros - you only use lBadge for the decision in the case statement.

You should use ampersands (&) for contenation rather than overloading the
plus sign. Not a problem here, but can be in cases where numbers are
involved.

--
Regards,
Tom Ogilvy


"Mark" wrote in message
...
I am using Excel 97.

I am trying to goes to write some code which goes to the appropriate
workbook.
I have a control workbook and 7 workbooks which are opened in the
background.

The the first six of these workbooks are contain numerically named sheets

0000 -0999
1000 -1999
etc

but my last workbook contains names on the worksheets.

Others

I am using some code which I was sent by Tom Ogilvy which I have altered
to
suit.

Dim BadgeNumber as Variant

BadgeNumber = InputBox(prompt:="Please enter Collar Number" _
+ Chr(13) + "(with leading zeros as appropriate)" _
+ Chr(13) + "or just Surname for Inspecting ranks or Police Staff.")

On Error GoTo CheckSheet

Select Case BadgeNumber
Case 1 To 1000

Case 1001 to 2000 etc...

Case Else

I thought that if I entered a name it would go to the Case Else field but
unfortunately it doesn't! So when I type a name as the BadgeNumber it
debugs
so I added this -

CheckSheet:

On Error Resume Next

Workbooks("Lieu Leave - (Others)").Activate

Set wSheet = Sheets(BadgeNumber)
If wSheet Is Nothing Then 'Doesn't exist

MsgBox "Worksheet does not exist", _
vbCritical, "Validater"

Else 'Does exist
MsgBox "Sheet does exist", _
vbInformation, "Validater"

Set wSheet = Nothing

On Error GoTo 0

When I run this - if a sheet does exist then it works fine, but if it
doesn't again it debugs.

Can anyone assist me a solution which looks up both types of worksheets,
please?

Thanks

Mark