Display macro enable/disable alert in an emailed file.

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

---

Attachment 1632

Thanks

Hi

Put the code in BeforeSave event rather than BeforeClose event.

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

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.

Thanks admin, it worked. Appreciate your usual support.

Thanks
Raj

Shouldn't:
xlVeryHiddenEnd
be:
xlVeryHidden
?

Quote:

---

Originally Posted by p45cal

Shouldn't:
xlVeryHiddenEnd
be:
xlVeryHidden
?

---

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