I am running Excel 2010/Windows 7 on a large network. I've recently updated an application that was originally written 4+ years ago and has been enhanced throughout the years. The latest version works fine on my machine, but some users are getting the following error upon opening the macro:

Run-time error '32809'
Application-defined or object-defined error

The trigger is the following code in sheet 'ThisWorkbook'

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("Instructions").Activate
Application.ScreenUpdating = True
End Sub

The error is pointing to the "Instructions" sheet (in this case it is also Sheet33

Any suggestions as to why this is happening?

Art

Hi,

Am Tue, 3 Mar 2015 07:11:13 -0800 (PST) schrieb :

The error is pointing to the "Instructions" sheet (in this case it is also Sheet33

check the sheet name for leading or trailing spaces. Is the sheet
hidden?

try:
Private Sub Workbook_Open()
Application.Goto Sheets("Instructions").Range("A1")
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Claus,

No leading or trailing spaces, sheet is visible.

I did change the first line to

Private Sub Auto_Open

and the issue apparently went away. However, I'd really like to understand why this happened.

Art

On Tuesday, March 3, 2015 at 10:11:17 AM UTC-5, wrote:
I am running Excel 2010/Windows 7 on a large network. I've recently updated an application that was originally written 4+ years ago and has been enhanced throughout the years. The latest version works fine on my machine, but some users are getting the following error upon opening the macro:

Run-time error '32809'
Application-defined or object-defined error

The trigger is the following code in sheet 'ThisWorkbook'

Private Sub Workbook_Open()
Application.ScreenUpdating = False
Sheets("Instructions").Activate
Application.ScreenUpdating = True
End Sub

The error is pointing to the "Instructions" sheet (in this case it is also Sheet33

Any suggestions as to why this is happening?

Art

Hi Art,

Am Tue, 3 Mar 2015 08:18:31 -0800 (PST) schrieb :

and the issue apparently went away. However, I'd really like to understand why this happened.

for me both codes (yours amd mine) worked fine in Workbook_Open event
without error


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I'm going to suggest you *do not use* the Workbook_Open event at all,
*ever*. Instead, in a standard module...

Option Explicit
Option Private Module

Sub Auto_Open()
Application.GoTo ThisWorkbook.Sheets("Instructions").Range("A1")
End Sub

...where this is a 'public' scope procedure exclusive to your project.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok, I'll bite. Why not use the auto_open or workbook_open events?

Art


On Tuesday, March 3, 2015 at 2:50:29 PM UTC-5, GS wrote:
I'm going to suggest you *do not use* the Workbook_Open event at all,
*ever*. Instead, in a standard module...

Option Explicit
Option Private Module

Sub Auto_Open()
Application.GoTo ThisWorkbook.Sheets("Instructions").Range("A1")
End Sub

..where this is a 'public' scope procedure exclusive to your project.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok, I'll bite. Why not use the auto_open or workbook_open events?

Use Auto_Open procedure *instead* of Workbook_Open event is what I
suggested. Auto_Open (and Auto_Close) are AutoMacros that will run
independant of events. So if for any reason events are disabled when
your file is opened (or closed), your AutoMacros will run.

Also, if your project becomes corrupt the events for ThisWorkbook may
not even work!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion