Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Notify user macros are disabled.
I want to try and notify the user that macros are disabled. The problem comes
when the user overlooks the Security warning bar at the top of Excel 2007 and doesn't enable the marcros as needed. I can't seem to figure out the last details. Our IT requires that we have security set to disable macros with notification. I'm still investigating getting IT to set the security to trust macros in a defined location for our use. My method of warning the user is to have a cell on the worksheets say macros are disabled and to clear the message when the file is opened and set it before the file is closed. I'm trying to use the Auto_open and BeforeClose events, but there are still holes where the logic breaks down. Especially dealing with the "Do you want to save changes... " dialog. Does anyone have logic to make this work? Thanks, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Notify user macros are disabled.
We've added digital signatures to workbooks with macros and IT has pushed the
digital signature out to everyone's computer. I don't know how it was all done, but it's very handy when we have a new macro. All we need to do is sign it with the corporate signature. -- HTH, Barb Reinhardt "DocBrown" wrote: I want to try and notify the user that macros are disabled. The problem comes when the user overlooks the Security warning bar at the top of Excel 2007 and doesn't enable the marcros as needed. I can't seem to figure out the last details. Our IT requires that we have security set to disable macros with notification. I'm still investigating getting IT to set the security to trust macros in a defined location for our use. My method of warning the user is to have a cell on the worksheets say macros are disabled and to clear the message when the file is opened and set it before the file is closed. I'm trying to use the Auto_open and BeforeClose events, but there are still holes where the logic breaks down. Especially dealing with the "Do you want to save changes... " dialog. Does anyone have logic to make this work? Thanks, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Notify user macros are disabled.
Insert a new worksheet. Name it "Dummy".
In large bold font on this worksheet type "Macros have been disabled, rendering this workbook useless. Please close and re-open with macros enabled" Add this code to Thisworkbook module. Private Sub Workbook_Open() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = xlSheetVisible Sheets("Dummy").Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sht As Worksheet Application.ScreenUpdating = False Sheets("Dummy").Visible = xlSheetVisible For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True ThisWorkbook.Save End Sub Gord Dibben MS Excel MVP On Fri, 12 Mar 2010 10:39:01 -0800, DocBrown wrote: I want to try and notify the user that macros are disabled. The problem comes when the user overlooks the Security warning bar at the top of Excel 2007 and doesn't enable the marcros as needed. I can't seem to figure out the last details. Our IT requires that we have security set to disable macros with notification. I'm still investigating getting IT to set the security to trust macros in a defined location for our use. My method of warning the user is to have a cell on the worksheets say macros are disabled and to clear the message when the file is opened and set it before the file is closed. I'm trying to use the Auto_open and BeforeClose events, but there are still holes where the logic breaks down. Especially dealing with the "Do you want to save changes... " dialog. Does anyone have logic to make this work? Thanks, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Notify user macros are disabled.
I do really appreciate the answers. Unfortunetly, there are several reasons
it won't work in my situation. The first is that the workbook is always saved and I want the users to be able to respond to the 'Do you want to save changes..' dialog with 'No'. It also doesn't seem to allow Excel to handle the 'Cancel' response the way I'd like. But I did come up with a way that seems to work. I include it here so other may use it. First I create a macro: Public Function TestMacros() As Boolean TestMacros = True End Function ....Bear with me... Then I put the message on the WS that I want in Red Bold (your message may very...): The automatic workbook functions are disabled. Make sure that you enable Macros. Then for the cell with the message I create Conditional format that will set the font color to the background: =TestMacros() The message cell is in an area that is protected. If macros are disable, then the formula returns error and the format is not changed. "Gord Dibben" wrote: Insert a new worksheet. Name it "Dummy". In large bold font on this worksheet type "Macros have been disabled, rendering this workbook useless. Please close and re-open with macros enabled" Add this code to Thisworkbook module. Private Sub Workbook_Open() Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = xlSheetVisible Sheets("Dummy").Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim sht As Worksheet Application.ScreenUpdating = False Sheets("Dummy").Visible = xlSheetVisible For Each sht In ActiveWorkbook.Sheets If sht.Name < "Dummy" Then sht.Visible = xlSheetVeryHidden End If Next sht Application.ScreenUpdating = True ThisWorkbook.Save End Sub Gord Dibben MS Excel MVP On Fri, 12 Mar 2010 10:39:01 -0800, DocBrown wrote: I want to try and notify the user that macros are disabled. The problem comes when the user overlooks the Security warning bar at the top of Excel 2007 and doesn't enable the marcros as needed. I can't seem to figure out the last details. Our IT requires that we have security set to disable macros with notification. I'm still investigating getting IT to set the security to trust macros in a defined location for our use. My method of warning the user is to have a cell on the worksheets say macros are disabled and to clear the message when the file is opened and set it before the file is closed. I'm trying to use the Auto_open and BeforeClose events, but there are still holes where the logic breaks down. Especially dealing with the "Do you want to save changes... " dialog. Does anyone have logic to make this work? Thanks, . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros Disabled | Excel Programming | |||
Excel keeps asking for save when user closes system with "Macros Disabled" logic | Excel Programming | |||
Macros disabled | Excel Programming | |||
how do I prevent a file from opening if user has disabled macros? | Excel Programming |