Range.Select error and screen maximizing error in Workbook_Open()

[Punsterr]

Hi,

I have a routine in ThisWorkbook so that when the workbook is opened,
it first protects the worksheets and workbook so that the user cannot
make changes except where allowed. I also want the workbook to open to
a certain worksheet and cell each time the file is opened. I'm running
into two problems:

First, I've had problems with this workbook such that it opens
minimized and does not allow the option to maximize the screen.
Running this macro while having other Excel workbooks open also tends
to cause the other workbook to have the same problem after that other
file is saved and re-opened separately. Perhaps my macro isn't being
limited to the proper workbook?

Second, when the workbook opens, I get a runtime error 91 object
variable or with block variable not set. This occurs after the
range.select and before the splash.

Any suggestions would be appreciated!


Sub Workbook_Open()

' Maximize all windows

ActiveWorkbook.Unprotect Password:="Password"
Application.WindowState = xlMaximized
ActiveWindow.WindowState = xlMaximized
ActiveWorkbook.Protect Password:="Password", Structure:=True,
Windows:=True

' Opens workbook to Table of Contents cell A1 upon opening the
workbook.

ThisWorkbook.Sheets("Contents").Range("a1").Select


' Show splash screen upon opening the workbook

MySplashForm.Show

' Password-protect all worksheets but lets macros hide/delete

Application.ScreenUpdating = False
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="Password",
userinterfaceonly:=True, AllowFormattingCells:=True,
AllowFormattingColumns:=True, AllowFormattingRows:=True
ws.EnableSelection = xlNoRestrictions
Next ws
Application.ScreenUpdating = True

End Sub

 

[Jim Thomlinson]

In order to select a range first you have to select a sheet then select a
range. You can not do it all in one line... Something like this.

Sheets("Contents").select
Range("a1").Select

 

[Jim Rech]

ThisWorkbook.Sheets("Contents").Range("a1").Select

This will work only if Contents is the active sheet. Otherwise you should
activate the sheet first and then select the cell. To do it in one step you
have to use Application.Goto.

--
Jim
| Hi,
|
| I have a routine in ThisWorkbook so that when the workbook is opened,
| it first protects the worksheets and workbook so that the user cannot
| make changes except where allowed. I also want the workbook to open to
| a certain worksheet and cell each time the file is opened. I'm running
| into two problems:
|
| First, I've had problems with this workbook such that it opens
| minimized and does not allow the option to maximize the screen.
| Running this macro while having other Excel workbooks open also tends
| to cause the other workbook to have the same problem after that other
| file is saved and re-opened separately. Perhaps my macro isn't being
| limited to the proper workbook?
|
| Second, when the workbook opens, I get a runtime error 91 object
| variable or with block variable not set. This occurs after the
| range.select and before the splash.
|
| Any suggestions would be appreciated!
|
|
| Sub Workbook_Open()
|
| ' Maximize all windows
|
| ActiveWorkbook.Unprotect Password:="Password"
| Application.WindowState = xlMaximized
| ActiveWindow.WindowState = xlMaximized
| ActiveWorkbook.Protect Password:="Password", Structure:=True,
| Windows:=True
|
| ' Opens workbook to Table of Contents cell A1 upon opening the
| workbook.
|
| ThisWorkbook.Sheets("Contents").Range("a1").Select
|
|
| ' Show splash screen upon opening the workbook
|
| MySplashForm.Show
|
| ' Password-protect all worksheets but lets macros hide/delete
|
| Application.ScreenUpdating = False
| Dim ws As Worksheet
| For Each ws In ThisWorkbook.Worksheets
| ws.Protect Password:="Password",
| userinterfaceonly:=True, AllowFormattingCells:=True,
| AllowFormattingColumns:=True, AllowFormattingRows:=True
| ws.EnableSelection = xlNoRestrictions
| Next ws
| Application.ScreenUpdating = True
|
| End Sub
|

 

[Punsterr]

Thanks, Jim. That fixed one part of the problem. I am still, however,
having the runtime error. This is where things get interesting. If I
save the Excel file to my hard drive or to a network, the Workbook Open
function works fine. However, if I attach this file to a Lotus Notes
database and open the file from within the database, the runtime error
91 appears on the very first line of code - where I'm trying to
unprotect the workbook. Any thoughts as to why that might be?