Its 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 its
because the open workbook does not contain the worksheet that Im 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 Id
like to see if there is a way of bypassing this problem. Id like to have
the application open without problems even when another Excel instance is
already opened since the end users may not be knowledgeable enough to close
down all Excel prior to opening the application.

Thanks for any advice you can provide.

Try this

Thisworkbook.Activate

before the code

--
HTH

Bob Phillips

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

"Pietersz" wrote in message
...
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.

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

--
Regards,
Tom Ogilvy

"Pietersz" wrote:

Its 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 its
because the open workbook does not contain the worksheet that Im 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 Id
like to see if there is a way of bypassing this problem. Id like to have
the application open without problems even when another Excel instance is
already opened since the end users may not be knowledgeable enough to close
down all Excel prior to opening the application.

Thanks for any advice you can provide.