enforcing macros

[icestationzbra]

hello,

wishing everyone a very prosperous and happy new year.

i have created a workbook that has username validation and that is to
be used over a network.

all the background macros run and thus validate the username only when
macros are enabled. if someone disables macros, they could easily
tamper with the workbook.

hence, i hid sheets in the workbook and protected the structure of the
workbook with a password. in the workbook_open event i would provide
the password and unhide the sheets (xlsheetvisible). again, in the
workbook_beforeclose event i would use "xlveryhidden" to hide the
structure with a password.

this way if macros are disabled, sheets would not be visible. only when
macros are enabled sheets would be visible and thus liable to be
validated for usage.

this method works very erratically. it sometimes works and at
othertimes it keeps all the sheets visible.

please help me in this regard.

thanks,

ali mac.

 

[John Wilson]

ali mac,

this method works very erratically. it sometimes works and at
othertimes it keeps all the sheets visible.

Without seeing your code, I can't be 100% sure, but I'll take a guess.
Your code to hide the sheets is in the Before_Close Event???
This does absolutely nothing.
Remember that when you open up a workbook, you're only copying
that worksheet into memory and working with a "view" of it.
Regardless of what you do with it, the copy on the hard drive will
remain unchanged until you save over it.
Now, keeping the above in mind......
A user opens a workbook with macros enabled.
All your sheets are unhidden.
He/she saves the workbook.
The workbook saves with the sheets unhidden so that the next person
that opens it (with or without macros enabled) will be able to view
those sheets.
The user then closes the workbook.......
Well, that doesn't do anything to the saved copy so the sheets
on it are still unhidden.

Suggest that you place your sheet hiding code in the Before_Save
event so that any time the workbook is saved, those sheets will be
hidden on the saved copy.

John

 

[Dave Peterson]

And if you do put your code to hide the sheets in the before_save event, you'll
want to give the user a nice way of unhiding them--closing and reopening would
work, but it sounds inconvenient to me.

 

[icestationzbra]

hello dave,

thank you for your reply.

i should have posted the code in order to hasten the solution, just did
not strike me.

these are the steps that i followed to do the intended.

1. i hid sheets in the workbook and protected the structure of the
workbook through the menu.
*****
2. i call a certain subproc to validate usernames, wherein i planted
this piece.

ThisWorkbook.Unprotect "pswd"
Sheet2.Visible = xlSheetVisible
*****
3. following is the snippet that i used to try to hide the sheets with
a pswd.

Sheet2.Visible = xlSheetVeryHidden
thisworkbook.protect "pswd"

i placed this snippet in the beforeclose event, as i can not enforce on
the user to save the file before close. if the user has committed a
mistake in data entry, and hence wants to close the file without
saving, this code will not get executed in the beforesave event.
*****
this piece, however, works once in a while. but, sometimes it flips. i
could attach the file, but it is HUGE.

thanks,

mac.

 

[John Wilson]

Mac,

Go back and read what I wrote carefully.......
The Before_Close event will do you absoultely no good.
If they open the workbook with macros enabled and save it,
the sheets will be unhidden (macros enabled or not).
Period........

this piece, however, works once in a while

Read the above. Read my previous post.
It either works or it doesn't.
The reason that you "think" it works "once in a while" is because
they're saving it with the sheets unhidden.

John

 

[icestationzbra]

hi dave/john,

i used the following statement in beforeclose as per your
instructions:

*****

Sheet2.Visible = xlSheetHidden

or

Sheet2.Visible = xlSheetVeryHidden

or

Sheet2.Visible = False

and

ActiveWorkbook.Protect "pswd", Structure:=True, Windows:=False

*****

i am getting an error:

run time error '1004':

method 'visible' of object 'sheets' failed.

can you help me?

thanks,

mac.

 

[John Wilson]

mac,

In all probability, you're getting the error because you're trying to
hide all the sheets. At least one sheet has to visible at all times.
Check the sequesnce of your hide/unhide and insure that one
is visible *before* trying to hide the others.

Now, getting back to your other problems.
I don't seem to be making my point that using the Before_Close
doesn't work. Before_Close does nothing to the "saved" copy
on your hard drive.

as i can not enforce on the user to save the file before close.

But remember that if the user does save the file, it's saved with
all of the sheets that your macro unhid, still unhidden. User opened
the file with macros enabled, the sheets unhid and the user saved
the file. That file, if now opened with macros disabled will
display all those sheets.

And now as Dave said......

And if you do put your code to hide the sheets in the before_save event, you'll
want to give the user a nice way of unhiding them--closing and reopening would
work, but it sounds inconvenient to me.

There is a way around that.........
I assume that there is only one sheet that you want displayed when the user
opens the file with macros disabled (the same one made visible in your
Before_Close event code). For the sake of this example, I'll assume
it's Sheet1.

Step #1
Take all of your "unhide" code and place it in it's own sub (not the
Workbook_Open event or an Auto_Open sub).
For this example, let's name the sub "OnOpen()"
Step #2
Now call this sub from the Workbook_Open event or your
Auto_Open() sub.
Okay, at this point, nothing should have changed except that you
re-arranged the code. Test it to make sure.
Step #3
Right Click on the tab for Sheet1 and select "View Code"
Copy and paste the following onto that sheet code pane:
Private Sub Worksheet_Activate()
Application.OnTime Now + TimeValue("00:00:01"), "OnOpen"
End Sub
What the above does......
When your sub to hide the sheets runs, it'll activate Sheet1 as
soon as you unhide it. After a 1 second delay, it'll then run
your OnOpen() sub to unhide the sheets again.
Okay, you might say...."what good does that do??"
Step #4
Take your code to hide the sheets and *move* it from the Before_Close
event to the Before_Save event.
Step #5
Hide the sheets that you want hidden manually leaving Sheet1
visible and save the file.

Here's how it works.......
User opens the file with macros disabled.
User sees Sheet1 only (we already knew that).
User opens the file with macros enabled.
Your Workbook_Open or Auto_Open calls the sub named
OnOpen.
That sub unhides the sheets you want to unhide and hides Sheet1.
Scenario #1....user closes the file without saving.
Nothing happens. Nothing has to happen as the saved copy on
the hard drive already has all but Sheet1 hidden.
Scenario #2...user saves the file
The Before_Save event fires.
This hides all of the sheets except Sheet1 and saves your file
to the hard drive with these sheets hidden.
Sheet1 (which you unhid during this procedure) becomes visible
on the screen momentarily. After a one second delay, your OnOpen
sub runs to unhide the sheets again, hide Sheet1 and allow the user
to continue with whatever they were doing.

John

 

[Harlan Grove]

And if you do put your code to hide the sheets in the before_save event, you'll
want to give the user a nice way of unhiding them--closing and reopening would
work, but it sounds inconvenient to me.

...

Or leave the sheet hiding code in the BeforeClose event handler and add logic to
save the workbook again in the BeforeClose event handler as the very last thing
done before the workbook closes.

Additional Logic
If all worksheets whose names are in the array Array("foo", "bar", "xyz"), then
iterate through Sheets(Array("foo", "bar", "xyz")) to check that they're all
closed. If they are, just quit. If not, save first then quit.

IMHO, the sheet hiding code belongs in both the BeforeClose and BeforeSave event
handlers, but the BeforeSave event handler also needs to save the file, unhide
the worksheets and set its Cancel argument to False. That way, if user A has
just saved the file but still has it open and user B opens it read-only, user B
has all the worksheets hidden initially, as intended, while user A has them
visible.

 

[Harlan Grove]

In all probability, you're getting the error because you're trying to
hide all the sheets. At least one sheet has to visible at all times.
Check the sequesnce of your hide/unhide and insure that one
is visible *before* trying to hide the others.

Quibble: the *workbook* could be hidden (Window > Hide) before it's saved, and
the unhide macro could unhide the workbook rather than manipulating the
worksheets' .Visible properties. While one or more worksheet could remain
visible, if the workbook is hidden it just wouldn't matter.

Now, getting back to your other problems.
I don't seem to be making my point that using the Before_Close
doesn't work. Before_Close does nothing to the "saved" copy
on your hard drive.

...

It does if you save the workbook in the BeforeClose event handler. Last time I
tried, Excel allowed me to do so.

 

[John Wilson]

Harlan,

Quibble: the *workbook* could be hidden (Window > Hide) before it's saved

True, but I didn't think that's what the OP wanted. I assumed that he
wanted one sheet visible hopefully with some instructions on how to
enable macros if they weren't already enabled.

It does if you save the workbook in the BeforeClose event handler.

Agreed, but judging by what the OP was saying, he wasn't doing
that and if he wasn't, any manipulation of the workbook that he did
via code (or manually), would not be saved.

Last time I tried, Excel allowed me to do so.

Worked the last time I tried too. I'm not arguing your point.

Initially, the OP said that his code was erratic. Worked sometimes
but not others? I tried to make the point that if his code was
correct and in proper event, it should work all the time (or fail
all the time).

His latest post was in regard to an error he was getting whilst
trying to hide the sheets. In all likelihood, he was trying to
hide the only visible sheet that he had and that would generate
an error. Of course, had he included his sub along with his
post, it would have been easier to answer with more certainty
as to what the problem really was.

From your other post......

IMHO, the sheet hiding code belongs in both the BeforeClose
and BeforeSave event

I haven't tried this with multiple users on a file yet, but your logic
makes sense. It wouldn't hurt to have the code in both events
(or called from both events), but if it's only in the Close and a Save
isn't included in that code, then using the Close alone to manipulate
the workbook is useless.

Sublimely yours,
John

 

[Harlan Grove]

...
...

(or called from both events), but if it's only in the Close and a Save
isn't included in that code, then using the Close alone to manipulate
the workbook is useless.

Agreed, but the whole point is to save in the BeforeClose handler as needed,
i.e., whenever any of worksheets that should initially be hidden were still
visible. I was reacting to you saying there was no reason to use BeforeClose.

Of course, if users have other workbooks open that include macros that run

Application.EnableEvents = False

then the whole house of cards implodes.

The best way of ensuring that users run workbook models under the configuration
intended for those workbook models hasn't changed from 123 Release 2 - have a
cell (or a defined name in Excel) with a formula that checks configuration
status and returns something innocuous (0 or "") if configuration is OK, and an
error value otherwise, then add N(cell) or append T(cell) to all formulas. If
configuration is OK, all formulas evaluate as intended. If not, all formulas
evaluate to error values. Since one configuration check for the OP would be
testing worksheet visibility, the defined name would need to call a UDF.

*NOTHING* stops a user cold as fast as absolutely nothing but errors appearing
in most cells. If you've got a cruel streak, you could also add a conditional
message informing the user that their manager has been e-mailed that the user
has violated company policies.

 

[Dave Peterson]

I think that this'll work.

I created a workbook with multiple worksheets. I named one Instructions.

I chose to hide every other sheet and leave "instructions" visible or do the
exact reverse--hide "instructions" and unhide every other sheet.

I also chose to put all the code in the workbook_beforesave. I don't like stuff
in workbook_beforeclose--just in case I make a mistake that I don't want to
save.

This is all the code I had under ThisWorkbook:

Option Explicit
Dim blkProc As Boolean
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wks As Worksheet
Dim resp As Long
Dim myActRange As Range

Cancel = True 'we'll save it ourselves!

Set myActRange = Selection

Worksheets("Instructions").Visible = xlSheetVisible

For Each wks In Me.Worksheets
If LCase(wks.Name) = LCase("Instructions") Then
'do nothing
Else
wks.Visible = xlSheetVeryHidden
End If
Next wks

Application.EnableEvents = False
Me.Save
Application.EnableEvents = True

resp = MsgBox(Prompt:="Would you like to keep the workbook open?", _
Buttons:=vbYesNo)

If resp = vbYes Then
For Each wks In Me.Worksheets
wks.Visible = xlSheetVisible
Next wks
With Worksheets("Instructions")
.Visible = xlSheetVeryHidden
If myActRange.Parent.Name = .Name Then
'do nothing
Else
Application.Goto myActRange
End If
End With
Me.Saved = True
Else
blkProc = True
Me.Close
End If

End Sub
Private Sub Workbook_Open()

Dim wks As Worksheet

For Each wks In Me.Worksheets
If LCase(wks.Name) = LCase("Instructions") Then
'do nothing
Else
wks.Visible = xlSheetVisible
End If
Next wks

Worksheets("Instructions").Visible = xlSheetVeryHidden

End Sub


You may want to stress test it a little. It seemed to work ok for me.

 

[Dave Peterson]

I missed catching the File|SaveAs.

I would imagine that you don't want that to happen either.

I added a little check to the first procedure that stopped it:

Option Explicit
Dim blkProc As Boolean
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim wks As Worksheet
Dim resp As Long
Dim myActRange As Range

If SaveAsUI = True Then
MsgBox "Sorry, You can't save this somewhere else"
Cancel = True
Exit Sub
End If

Cancel = True 'we'll save it ourselves!

Set myActRange = Selection

Worksheets("Instructions").Visible = xlSheetVisible

For Each wks In Me.Worksheets
If LCase(wks.Name) = LCase("Instructions") Then
'do nothing
Else
wks.Visible = xlSheetVeryHidden
End If
Next wks

Application.EnableEvents = False
Me.Save
Application.EnableEvents = True

resp = MsgBox(Prompt:="Would you like to keep the workbook open?", _
Buttons:=vbYesNo)

If resp = vbYes Then
For Each wks In Me.Worksheets
wks.Visible = xlSheetVisible
Next wks
With Worksheets("Instructions")
.Visible = xlSheetVeryHidden
If myActRange.Parent.Name = .Name Then
'do nothing
Else
Application.Goto myActRange
End If
End With
Me.Saved = True
Else
blkProc = True
Me.Close
End If

End Sub

Private Sub Workbook_Open()

Dim wks As Worksheet

For Each wks In Me.Worksheets
If LCase(wks.Name) = LCase("Instructions") Then
'do nothing
Else
wks.Visible = xlSheetVisible
End If
Next wks

Worksheets("Instructions").Visible = xlSheetVeryHidden

End Sub


Now, if you (as the developer or very smart user) want to save it without this
just firing, just open the VBE (alt-F11), hit ctrl-G (to see the immediate
window) and type:

application.enablevents = false
(and hit enter)

Now you can save (and anyone else who does it can, too!)

After you save the workbook, turn this setting back on:
application.enablevents = true

 

[Harlan Grove]

...
...

I also chose to put all the code in the workbook_beforesave. I don't like
stuff in workbook_beforeclose--just in case I make a mistake that I don't want
to save.

...

IF there's no way to save the file without triggering BeforeSave if events are
enabled, then the worksheet hiding code isn't needed in BeforeClose. However,
the BeforeClose is a much better place to put a dialog. Your BeforeSave code

resp = MsgBox(Prompt:="Would you like to keep the workbook open?", _
Buttons:=vbYesNo)

could be viewed as user-hostile. Better to put a dialog into BeforeClose asking
whether the user wants to save changes. If user chooses to do so, hiding
worksheets, saving, closing. If not, just closing. The BeforeSave handler would
then always unhide worksheets. Something crudely like


Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim resp As Long

If Me.Saved Then Exit Sub 'nothing to do, so don't do anything

resp = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Close")

Select Case resp
Case vbYes
Call Workbook_BeforeSave(False, False)
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select
End Sub


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Me.Worksheets("Sheet3").Visible = xlSheetVeryHidden

On Error Resume Next
Application.EnableEvents = False
Me.Save
Application.EnableEvents = True
On Error GoTo 0

Me.Worksheets("Sheet3").Visible = xlSheetVisible
Me.Saved = True
End Sub


Private Sub Workbook_Open()
Me.Worksheets("Sheet3").Visible = xlSheetVisible
End Sub

 

[John Wilson]

Dave,

Nice way to accomplish this.
Cleaner than the way I've been doing it.
This is a much asked question, keep it handy.

Thanks,
John

 

[John Wilson]

Harlan,

If you've got a cruel streak

I do and you gave me some good ideas. Thanks!!

Dave seems to have come up with a nice way to
accomplish what the OP wanted. I like his approach
better than what I've been using.

Thanks,
John

 

[Dave Peterson]

I like this better. It is more user friendly.

...
..
..

IF there's no way to save the file without triggering BeforeSave if events are
enabled, then the worksheet hiding code isn't needed in BeforeClose. However,
the BeforeClose is a much better place to put a dialog. Your BeforeSave code


could be viewed as user-hostile. Better to put a dialog into BeforeClose asking
whether the user wants to save changes. If user chooses to do so, hiding
worksheets, saving, closing. If not, just closing. The BeforeSave handler would
then always unhide worksheets. Something crudely like

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim resp As Long

If Me.Saved Then Exit Sub 'nothing to do, so don't do anything

resp = MsgBox("Do you want to save your changes?", vbYesNoCancel, "Close")

Select Case resp
Case vbYes
Call Workbook_BeforeSave(False, False)
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
End Select
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
Me.Worksheets("Sheet3").Visible = xlSheetVeryHidden

On Error Resume Next
Application.EnableEvents = False
Me.Save
Application.EnableEvents = True
On Error GoTo 0

Me.Worksheets("Sheet3").Visible = xlSheetVisible
Me.Saved = True
End Sub

Private Sub Workbook_Open()
Me.Worksheets("Sheet3").Visible = xlSheetVisible
End Sub

 

[icestationzbra]

hi dave/john,

in my immediately previous post, where i had mentioned "beforeclose", i
had actually meant "beforesave". i have used that event but forgot to
mention it correctly. sorry if i seemed obstinate, i was not.

i will try the codes posted out here.

i had a little query, how do i prevent application from displaying save
dialog when trying to close? i tried using application.displayalerts =
false, but that gave me an error about using the wrong method.

thanks,

mac.

 

[John Wilson]

mac,

While the code scenario that I gave you would work, Dave's is better.
Personally, I'd comment out the MsgBox code and "just do it".
If you're going to have more than one user opening the file at the
same time, you might want to copy the Save event code into the
Close event too as Harlan suggested (wouldn't hurt anyway).

As to your query.....
Application.DisplayAlerts = False
shouldn't give you an error
Post the code immediately before and after that line
of code and I or someone else will take a look at it for
you.
One other way to stop that save alert, by the way, is to
fool the workbook into thinking it's saved (whether it is
or not).
Use ThisWorkbook.Saved = True

John

 

[icestationzbra]

hi dave/john/harlan,

i tried all the codes, spent nearly 36 hours performing regressiv
test.

dave - the code that i got from you, there is a small glitch.

i open the file, enter some information. close the file. it asks m
whether i want to save the file or not. i say yes. it saves the file
and asks me if i want to keep the file open or not. i say yes. it ask
me whether i want to save the file or not. i say yes. it saves th
file, and asks me if i want to keep the file open or not. i say yes
and this goes on until i say cancel, and the file remains open with n
more keep-open question. if i say no to filesave at this point in time
the file closes.

i do this again. after a couple of yes's, i choose no for keep-open
excel crashes asking me whether i want to send the error to microsoft.

the code has 2-3 very good features, which i incorporated in the fina
code, with due acknowledgement .

harlan - the code works fine, but i could not understand certai
things. what happens when i close the file, say no to save changes an
open the file with macros disabled? how is it hiding the sheets
beforesave event is not being triggered, there is no sheet-hid
procedure in beforeclose.

most of the final code is built around harlan's algorithm. i am stil
trying to find a glitch or two in his code .

which event occurs first, beforeclose or beforesave?

i have combined the two along with what i already had and arrived a
the following.

****************************************************

Option Explicit

Public WS As Worksheet

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim intResp As Integer

If Me.Saved = True Then Exit Sub

intResp = MsgBox("Do you want to save the changes you made to - '"
ActiveWorkbook.Name & "'?", vbYesNoCancel + vbExclamation, "Microsof
Excel")

Select Case intResp

Case vbYes
Call Workbook_BeforeSave(False, False) 'hide, save, exit

Case vbNo
Me.Saved = True 'exit

Case vbCancel
Cancel = True 'stay

End Select

On Error Resume Next

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel A
Boolean)

Cancel = True

If SaveAsUI = True Then 'prevent file from being saved elsewhere

MsgBox "Restriction on FileSaveAs location!", vbCritical
Cancel = True
Exit Sub

End If

For Each WS In Me.Worksheets 'hide every sheet before save, whethe
workbook is being closed or not

If LCase(WS.Name) = LCase("TitleSheet") Then
'do not hide the titlesheet
Else
WS.Visible = xlSheetVeryHidden
End If

Next WS

Application.EnableEvents = False 'prevent beforesave event
ActiveWorkbook.Save 'save workbook
Application.EnableEvents = True 'enable beforesave

For Each WS In Me.Worksheets 'if workbook is not being closed, unhid
every sheet

WS.Visible = xlSheetVisible

Next WS

Me.Saved = True 'else save message comes up twice, once fo
beforeclose, once for beforesave

On Error Resume Next

End Sub

Private Sub Workbook_Open()

For Each WS In Me.Worksheets

WS.Visible = xlSheetVisible 'make every sheet visible upon workboo
open

Next WS

On Error Resume Next

End Sub

****************************************************

the msgbox that you see upon closing a file, is custom, no
application's.

one thing that i am not able to do here is, if i do not want to clos
the file, how do i go back to the range where i was working? dav
handled that in his code.

i have another question, if you could help me.

LCase(wks.Name) = LCase("Instructions")

in this statement, i would like to be able to use the sheet number
such as, Sheet1 or Sheet2. it is more robust, as even if someon
changes the name, it would still be valid.

thanks,

mac