Change Message on Protected Worksheet
 

In Excel 2000: Using the below code to open a workbook with password
protected worksheets. Opens okay. When the user clicks the €œX€� Close Window
button, a warning message comes up:

Do you want to save the changes you made to €˜ICM A1.xls? Two questions:

1. Why the message? The user cannot make changes on the protected worksheets.
2. Is there a way to bypass this message such that when the user clicks
Close Window, the workbook closes, according to the Auto_Close code?


Option Explicit

Sub Auto_Open()
Dim WS As Worksheet
Application.ScreenUpdating = False
Application.DisplayFullScreen = True
Application.EnableEvents = False

For Each WS In ThisWorkbook.Worksheets
If WS.Visible = xlSheetVisible Then
WS.Select
Application.Goto WS.Range("A1"), True
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.WindowState = xlMaximized
ActiveWindow.View = xlNormalView
End If
Next

Application.Goto Reference:=Range("A100"), Scroll:=False
Application.EnableEvents = True
End Sub


Sub Auto_Close()
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("A100"), Scroll:=False
Application.DisplayFullScreen = False
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayHorizontalScrollBar = True
Application.ScreenUpdating = True
End Sub

Change Message on Protected Worksheet
 

Try creating a new workbook.
then close it. No prompt.

Create another new workbook
toggle the displaygridlines
close it. You get the prompt.

--
That was the first thing I saw (then I stopped).

If you add some:

msgbox Thisworkbook.saved

throughout your code, you can see what you ran to make excel think your workbook
was dirty (needed saving).

In fact, maybe you can just add:

thisworkbook.saved = true

at the end of your auto_open routine. (If the user changes a setting, they're
on their own.)

Phil Hageman wrote:

In Excel 2000: Using the below code to open a workbook with password
protected worksheets. Opens okay. When the user clicks the €œX€� Close Window
button, a warning message comes up:

Do you want to save the changes you made to €˜ICM A1.xls? Two questions:

1. Why the message? The user cannot make changes on the protected worksheets.
2. Is there a way to bypass this message such that when the user clicks
Close Window, the workbook closes, according to the Auto_Close code?

Option Explicit

Sub Auto_Open()
Dim WS As Worksheet
Application.ScreenUpdating = False
Application.DisplayFullScreen = True
Application.EnableEvents = False

For Each WS In ThisWorkbook.Worksheets
If WS.Visible = xlSheetVisible Then
WS.Select
Application.Goto WS.Range("A1"), True
ActiveWindow.DisplayGridlines = False
ActiveWindow.DisplayWorkbookTabs = False
ActiveWindow.DisplayHeadings = False
ActiveWindow.DisplayHorizontalScrollBar = False
ActiveWindow.WindowState = xlMaximized
ActiveWindow.View = xlNormalView
End If
Next

Application.Goto Reference:=Range("A100"), Scroll:=False
Application.EnableEvents = True
End Sub

Sub Auto_Close()
Application.Goto Reference:=Range("A1"), Scroll:=True
Application.Goto Reference:=Range("A100"), Scroll:=False
Application.DisplayFullScreen = False
ActiveWindow.DisplayWorkbookTabs = True
ActiveWindow.DisplayHeadings = True
ActiveWindow.DisplayHorizontalScrollBar = True
Application.ScreenUpdating = True
End Sub

--

Dave Peterson