Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing
I have my workbook set so that you can not open it
without enabling macros with; Private Sub Workbook Open () This Workbook.IsAddin = False It works great except when I close the file, using a dialog box. If I select "Save & Close" it saves and closes the file but it sets the workbook so that you can now open without enabling macros. If I use the normal Excel Close, it can not be opened without enabling macros. Any ideas on what is happening? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing
i have once created a Workbook that was trying to do the same thing you're
trying to achieve. What I did was to hide all the worksheets except for the "Warning" sheet before every time it's been saved. when it's open, if the Macro isn't enabled then only the Warning sheet will be display. This works as long as no one change the sheet name from Warning to something else therefore I set up an error trap before it's saved to warn the end-user that if the name isn't change back to Warning then they can't save it. the following are the steps to create this: I created a blank worksheet named "Warning" with nothing but a warning message to let the user know that in order to use this they must Enable macros I then put the follwoing code in the Workbook_Open event Private Sub Workbook_Open() Dim s As Worksheet Application.ScreenUpdating = False For Each s In ThisWorkbook.Worksheets If s.Name < "Warning" Then s.Visible = xlSheetVisible Else s.Visible = xlSheetVeryHidden End If Next s ThisWorkbook.Worksheets(1).Select Application.ScreenUpdating = True End Sub Then, I put the following code in BeforeSaved Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim s As Worksheet On Error GoTo TrapError Application.ScreenUpdating = False ThisWorkbook.Worksheets("Warning").Visible = xlSheetVisible For Each s In ThisWorkbook.Worksheets If s.Name < "Warning" Then s.Visible = xlSheetVeryHidden End If Next s Application.ScreenUpdating = True Exit Sub TrapError: 'this is incase the Warning Sheet name got changed Cancel = True MsgBox "The worksheet that was named 'Warning' was changed." & _ " Please change it back then save again", vbCritical, "Save action was canceled" End Sub Matt Chen Blue Ridge Telecom "Jon M." wrote: I have my workbook set so that you can not open it without enabling macros with; Private Sub Workbook Open () This Workbook.IsAddin = False It works great except when I close the file, using a dialog box. If I select "Save & Close" it saves and closes the file but it sets the workbook so that you can now open without enabling macros. If I use the normal Excel Close, it can not be opened without enabling macros. Any ideas on what is happening? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closing
-----Original Message----- i have once created a Workbook that was trying to do the same thing you're trying to achieve. What I did was to hide all the worksheets except for the "Warning" sheet before every time it's been saved. when it's open, if the Macro isn't enabled then only the Warning sheet will be display. This works as long as no one change the sheet name from Warning to something else therefore I set up an error trap before it's saved to warn the end-user that if the name isn't change back to Warning then they can't save it. the following are the steps to create this: I created a blank worksheet named "Warning" with nothing but a warning message to let the user know that in order to use this they must Enable macros I then put the follwoing code in the Workbook_Open event Private Sub Workbook_Open() Dim s As Worksheet Application.ScreenUpdating = False For Each s In ThisWorkbook.Worksheets If s.Name < "Warning" Then s.Visible = xlSheetVisible Else s.Visible = xlSheetVeryHidden End If Next s ThisWorkbook.Worksheets(1).Select Application.ScreenUpdating = True End Sub Then, I put the following code in BeforeSaved Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim s As Worksheet On Error GoTo TrapError Application.ScreenUpdating = False ThisWorkbook.Worksheets("Warning").Visible = xlSheetVisible For Each s In ThisWorkbook.Worksheets If s.Name < "Warning" Then s.Visible = xlSheetVeryHidden End If Next s Application.ScreenUpdating = True Exit Sub TrapError: 'this is incase the Warning Sheet name got changed Cancel = True MsgBox "The worksheet that was named 'Warning' was changed." & _ " Please change it back then save again", vbCritical, "Save action was canceled" End Sub Matt Chen Blue Ridge Telecom "Jon M." wrote: I have my workbook set so that you can not open it without enabling macros with; Private Sub Workbook Open () This Workbook.IsAddin = False It works great except when I close the file, using a dialog box. If I select "Save & Close" it saves and closes the file but it sets the workbook so that you can now open without enabling macros. If I use the normal Excel Close, it can not be opened without enabling macros. Any ideas on what is happening? Thanks . Thanks a lot for the TIME and info. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing workbooks w/o closing Excel | Excel Discussion (Misc queries) | |||
Closing dates | Excel Worksheet Functions | |||
Closing VB triggers closing Excel | Excel Programming | |||
closing excel after closing a workbook | Excel Programming | |||
VBA closing one file | Excel Programming |