Display macro enable/disable alert in an emailed file.

[Rajesh Kr Joshi]

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.


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


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

End Sub

1632

Thanks

[Admin]

Hi

Put the code in BeforeSave event rather than BeforeClose event.

[Rajesh Kr Joshi]

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


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

Thanks

[Admin]

OK

Try this.


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.

[Rajesh Kr Joshi]

Thanks admin, it worked. Appreciate your usual support.

Thanks
Raj

[p45cal]

Shouldn't:
xlVeryHiddenEnd
be:
xlVeryHidden
?

[Admin]

Shouldn't:
xlVeryHiddenEnd
be:
xlVeryHidden
?

I just copied the OP's code, haven't noticed that :sleepy: