Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change font color in protected worksheet | Excel Worksheet Functions | |||
Can I change the tab order in a protected worksheet? | Excel Discussion (Misc queries) | |||
On a excel protected worksheet, how do I change the tab order? | Excel Worksheet Functions | |||
How to change the tab order for a protected worksheet | Excel Worksheet Functions | |||
how do you change a row height in a protected worksheet | Excel Programming |