Runtimem Error 9 Subscript Out of Range

[Guest]

It’s my first time posting a question so bear with me.

My application has an auto-open macro which displays an advanced user form
(i.e. frm___.Show), which then activates a work sheet to populate the form
with data from the worksheet. I get “Runtime error 9 subscript out of rangeâ€
on the “Worksheets(“WorkSheetNameâ€).Activate if another Excel work book is
already open prior to my opening the application. My guess is that it’s
because the open workbook does not contain the worksheet that I’m trying to
activate.

I can avoid this by shutting down all open instances of Excel prior to
opening my application but in order to make my application end user proof I’d
like to see if there is a way of bypassing this problem. I’d like to have
the application open without problems even when another Excel instance is
already opened since the end user’s may not be knowledgeable enough to close
down all Excel prior to opening the application.

Thanks for any advice you can provide.

 

[Bob Phillips]

Try this

Thisworkbook.Activate

before the code

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

dim sh as Worksheet
Thisworkbook.Activate
On Error Resume Next
set sh = Thisworkbook.Worksheets(“WorkSheetNameâ€)
On Error goto 0
if sh is nothing then
msgbox "WorksheetName does not exit in workbook " & _
Thisworkbook.name
ThisWorkbook.Close SaveChanges:=False
end if