Display macro enable/disable alert in an emailed file.

**Rajesh Kr Joshi** · 07-07-2014, 06:41 PM

Hi,

I have the below macro code to display a message if macros are enable/disable. In the attached file there are 2 sheets 'Summary' and 'Info'. Info sheet is visible when macros are disabled and meanwhile summary is hidden. Summary file is only visible when macros are enabled. I am facing issue when I am emailing this file, the summary sheet doesn't hide even if the macros are disable. On strange thing I also noticed is that If I email this file while macros are enable in my excel, it doesn't work. But if I send this with macro disabled in my excel it works fine. Seeking experts help here.

HTML Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("info").Visible = True
Sheets("summary").Visible = xlVeryHiddenEnd
End Sub

HTML Code:

Private Sub Workbook_Open()
Sheets("Summary").Visible = True
Sheets("info").Visible = xlVeryHidden

End Sub

Test WB.xlsm

Thanks

**Admin** · 07-08-2014, 09:31 AM

Hi

Put the code in BeforeSave event rather than BeforeClose event.

**Rajesh Kr Joshi** · 07-08-2014, 01:13 PM

Hi Admin, thanks for your prompt reply. I tried the BeforeSave event, still not working.

HTML Code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("info").Visible = True
Sheets("summary").Visible = xlVeryHiddenEnd
End Sub

Thanks

**Admin** · 07-09-2014, 07:54 AM

OK

Try this.

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("info").Visible = True
    Sheets("summary").Visible = xlVeryHiddenEnd
    ThisWorkbook.Save
End Sub

Please note that this code will forcefully save the workbook each time when it closes.