Change Message on Protected Worksheet

P

Phil Hageman

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
 
D

Dave Peterson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top