Macros Disabled

[Steven]

I have a file that dont want the user to to be able to use unless macros are
enabled. I have seen in past postings where people suggest using
xlVeryHidden and the file has one sheet visible stating that this file must
have macros running to work. Then if macros are enabled then the sheet opens
the worksheets to use and hides the message sheet. I do not see how that
works.

If a user enters the file with macros and with the VeryHidden sheets , but
with the macros running the Auto_Open has code to unhide the sheets. Now
when the user saves the file and exits the sheets are now visible so the next
user who may enter with Macros Security very high will be entering the file
with all the sheets visible. How do I fix this? What am I missing here?

Thank you,

Steven

 

[RyanH]

Just add this to the BeforeClose_Event. This will hide all sheets in the
workbook. So if macros are not enabled upon opening the workbook the
worksheets remain hidden.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

For Each wks In Worksheets
wks.Visible = xlSheetVeryHidden
Next wks

End Sub

Hope this helps!

 

[Doug Glancy]

Steven,

What you actually have to do, I think, is in the WorkbookBeforeSave event:

1 note which sheets are currently hidden
2 hide all the sheets except the notification sheet
3 do a save in the code
4 unhide the sheets that were hidden before the save
5 cancel the BeforeSave

Just doing it before the BeforeClose event won't always work because the
user might not actually do a save at closing.

hth,

Doug

 

[Nigel]

You cannot hide every worksheet! Use this modification

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks as worksheet
shMacro.Visible = xlSheetVisible
For Each wks In Worksheets
If wks.codename <> "shMacro" then wks.Visible = xlSheetVeryHidden
Next wks
End Sub

Private Sub Workbook_Open()
Dim wks As Worksheet
shMacro.Visible = xlSheetVeryHidden
For Each wks In Worksheets
If wks.codename <> "shMacro" then wks.Visible = xlSheetVisible
Next wks
End Sub

The worksheet with a codename of "shMacro" will remain visible and contain
your warning messages, if the workbook is opened without macros.

PS To set the codename edit it in the VBA editor, click on relevant workbook
object (sheet) press F4 and change the top entry (name), the other name
without ( ) is the sheet tab name. The reason for using codename is that
changes to the sheet tab name will not affect the code running.

HTH

--

Regards,
Nigel
(e-mail address removed)

 

[Nigel]

If is the close event you need NOT the save event, the Save event might not
fire but the Close event will always occur before closing, whether there is
a Save or no Save.

See my reply to the other thread for a solution

--

Regards,
Nigel
(e-mail address removed)

 

[Christian Treffler]

Hi,

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wks as worksheet
shMacro.Visible = xlSheetVisible
For Each wks In Worksheets
If wks.codename <> "shMacro" then wks.Visible = xlSheetVeryHidden
Next wks
End Sub

Private Sub Workbook_Open()
Dim wks As Worksheet
shMacro.Visible = xlSheetVeryHidden
For Each wks In Worksheets
If wks.codename <> "shMacro" then wks.Visible = xlSheetVisible
Next wks
End Sub

The worksheet with a codename of "shMacro" will remain visible and contain
your warning messages, if the workbook is opened without macros.

I'm using a similar construction to show or hide the first line of an
excel sheet which contains a warning about enabling macros.

The only thing that bothers me is that the line is visible after loading
the file until the Workbook_Open sub is executed. Thus a customer who
opens the file gets a short glimpse of something in red before it
disappears.

Is there any way of calling a macro and hide sheets or cells before the
contents of the activated sheet are displayed?

CU,
Christian

 

[john]

Steven,
Dougs response is to use the BeforeSave event is correct as you need to
allow for users who may save the file & then close it without further save.
In this case, your sheet tabs would still be visible if next user opens the
workbook without enabling macros.

never used but i found this approach published by unknown author which may
do what you want. Paste all code in ThisWorkbook.

Private Const MacroWarning As String = "sheet1" 'Enter name of the Warning
Page

Private Sub Workbook_Beforesave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For Each ws In ActiveWorkbook.Sheets
If LCase(MacroWarning) = LCase(ws.Name) Then
ws.Visible = True
Else: ws.Visible = xlVeryHidden
End If
Next
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal ws As Object, ByVal Target
As Excel.Range)
If LCase(ws.Name) = LCase(MacroWarning) Then
For Each ws In ActiveWorkbook.Sheets
ws.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End If
End Sub

Private Sub workbook_open()
Sheets(MacroWarning).Select
For Each ws In ActiveWorkbook.Sheets
ws.Visible = True
Next
ActiveSheet.Visible = xlVeryHidden
End Sub

 

[Nigel]

I get the gist of what you are doing but the active sheet now has a missing
row which probably draws more attention than a passing red message? There
is no earlier event than the workbook open event, you could make another
sheet the first activated sheet by activating this before the file is
previously saved. Then in your open event code activate your main sheet
after the message line is hidden?

The method I use with a separate sheet avoids this completely, you can also
add more lines or instructions on this sheet to help the user get over the
problem of not running macros. The overhead is minimal.

--

Regards,
Nigel
(e-mail address removed)

 

[Steven]

Thank you for all your responses. After review and testing I will say Doug
has the solution. If you only do this on the before_close event then the
system will ask the user if they want to save again, because in fact there
has been a change, and at that time the file will be only showing the one
sheet that has the message on it, which is something I dont want the user to
really clue in on anyway. Therefore the before_close will not work. And you
cannot do the hide alone on the Before_Save because the user may be staying
in the file to continue working. Therefore you have to do in in the
Before_Save ... Save the file ... Switch it back to the users normal view ...
then Cancel the Save, and all is well. This keeps things working in the
users normal course of operation of the file which means doing what Doug
recommends is the best way I believe.

Thank you very much. This issue has really bothered me for years. Like
about 20 years.

 

[Steven]

Open all the responses. I made a repsonse that is not showing but does show
if all opened.

 

[Doug Glancy]

Steven,

I'm glad I could help.

Doug