Before Close is probably not what you want.
You want the sheet statuses to be present in the saved version of the
workbook. For this, you probably want Workbook_BeforeSave instead.
Regards, Dave S
________________________________
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of Saad
Sent: Wednesday, 13 July 2011 20:32
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] force users to enable macros
Dear experts:
I have a workbook with 3 worksheets
1. warning
2. A
3. B
In the sheet 'warning', there is a simple message that "Please enable
macros to work with this workbook."
In sheets A and B, i have some data and analayses to display.
I would like that when a user chooses to disable macros, he should be
directed to the warning sheet only. The other sheets should be
'veryhidden'.
When the user enables macros, the warning sheet should then be
'veryhidden' and other two sheets be displayed.
I wrote following code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("warning").Visible = True
Sheets("A").Visible = xlSheetVeryHidden
Sheets("B").Visible = xlSheetVeryHidden
Application.ThisWorkbook.Saved = True
End Sub
Private Sub Workbook_Open()
Sheets("A").Visible = True
Sheets("B").Visible = True
Sheets("warning").Visible = xlSheetVeryHidden
End Sub
The problem is that first time, the code works well. However, next time,
it always hides the warning sheet and shows the other sheets, whether or
not the macros are enabled.
Need help on this one! I am using Excel 2010.
Best regards, Saad
----------
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar