Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute? I've searched and searched for a proper setting for
this but haven't been able to come up with squat yet. I know that there has
to be a way.....someone help, please before I lose the rest of my
mind.........


Thank you.


Jon

Jon,

Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute?
Actually, no there isn't so you can stop looking.

One workaround is to make all sheets but one visible when saving the workbook
(rendeiring it useless) and on that sheet place a notice that macros need
to be enabled in order to use it. If macros are enabled when it's opened,
that notice sheet will hide itself and the other sheets will be made visible.

Attached below is some code and instructions on how to do this:
(Please make note that Excel is not a secure environment so if you're
using this to protect the workbook, it can be circumvented by an
experienced user)

John

<snip
The only way to do this is to hide all the sheets except one when
the workbook is saved (making the workbook unusable).
On that one unhidden sheet, you'll need to place some text alerting
the user that they need to enable macros and instruction on how
to do so. You'll also need to protect the VBA project so they can't
easily get at the underlying code. Please be aware though that an
experienced user can crack any VBA code that you have or make
the sheets that you've hidden, visible again even without getting
into your VBA code.

Having said that, here's some code that will attempt to do what
you want. Try this in a brandy new workbook first to see how
it works and then adapt the code to fit your needs (based on how
your sheets are laid out)

How it works:
On Close, nothing happens (except the close) because the
stored copy already has all but sheet(1) hidden.
If the user saves the workbook...
His/her last active cell address is saved in a memvar
The sub to hide all but the first sheet is activated.
By default, this activates sheet(1) firing the code there.
There's a one second timer in the sheet activation code
(allowing the actual save to take place with all but the
first sheet hidden). After one second the OpenWorkbook
sub is called taking the user to the sheet he/she was last on.
The last cell on that sheet that was active is now selected.

'***** Workbook Code*****
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
' Remember where the user was
WhereWasI = ActiveCell.Address
' Call sub to hide all but the first sheet
HidealmostAll
End Sub

Private Sub Workbook_Open()
' Prevent Sheet1 Activate code from firing
Application.EnableEvents = False
' Call opening ceremonies sub
OpenWorkbook
Application.EnableEvents = True
End Sub

'***** Sheet(1) Code ******
Option Explicit
Private Sub Worksheet_Activate()
Application.OnTime Now + TimeValue("00:00:01"), "OpenWorkbook"
End Sub

'****Module1 Code *****
Option Explicit
Public WhereWasI As String
Sub HidealmostAll()
' Insure Sheet(1) is Visible
Sheets(1).Visible = True
Dim a As Integer
' Hide all the other sheets
For a = 2 To Sheets.Count
Sheets(a).Visible = xlVeryHidden
Next a
End Sub

Sub OpenWorkbook()
Dim a As Integer
' Display all the other sheets
For a = 2 To Sheets.Count
Sheets(a).Visible = True
Next a
Sheets(1).Visible = xlVeryHidden
If WhereWasI = "" Then Exit Sub
Range(WhereWasI).Activate
End Sub
<snip






Jon wrote:

Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute? I've searched and searched for a proper setting for
this but haven't been able to come up with squat yet. I know that there has
to be a way.....someone help, please before I lose the rest of my
mind.........

Thank you.

Jon

Thanks guys...no wonder it was driving me nuts...looks like this scenario
was not anticipated when MS added security to Office. I'm kind of surprised
though as it seems that security ought to be a 2 way street i.e. the user is
free to decline the running of code in the Excel environment but the
developer should also have a way of stopping the user from operating the
Excel spreadsheet in a way that was not intended (in the absence of macros).
I have several sophisticated Excel apps that I have developed and it bothers
me that many users may inadvertantly get themselves into trouble by
declining macros. Actually I imagine that in some situations this could
cause the applications to generate false output, and there would be no way
to tell. Not good.



Jon

PS You guys are fast too, thanks for the quick answers. I can go to bed now
without feeling like I'm missing something obvious at least!!

Jon,

You probably have formatted or otherwise sued some rows below or
columns to the right of your data. On the bloated workbook, select the
sheets individually and press Ctrl + End. If the last row and column
is well below where you think it should be, you'll need to delete the
empty rows and columns.

Check here for an example of how to do this both manually
and with VBA:

http://www.contextures.com/xlfaqApp.html#Unused

John

Jon wrote:

Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute? I've searched and searched for a proper setting for
this but haven't been able to come up with squat yet. I know that there has
to be a way.....someone help, please before I lose the rest of my
mind.........

Thank you.

Jon

Another idea would be to put all the good stuff in a different workbook, and
then put just enough code in the "first" workbook that if they have macros
turned on, then it will open the second workbook with all of the stuff in
it. At least this way, they wouldn't have a "dead" copy which they might
tinker with. A little better control plus you don't have to do so much
hiding and stuff.
--
HTH
Richard Choate, CPA

"Jon" wrote in message
...
Is there a way to allow a user to open a workbook ONLY if they have allowed
the macros to execute? I've searched and searched for a proper setting for
this but haven't been able to come up with squat yet. I know that there has
to be a way.....someone help, please before I lose the rest of my
mind.........


Thank you.


Jon

Jon,
Its a Catch 22 situation.
What're you gonna use to stop the user opening the workbook... a macro?
But the user has just disabled macros!!!

ATB
Henry

"Jon" wrote in message
...
Is there a way to allow a user to open a workbook ONLY if they have
allowed
the macros to execute? I've searched and searched for a proper setting for
this but haven't been able to come up with squat yet. I know that there
has
to be a way.....someone help, please before I lose the rest of my
mind.........


Thank you.


Jon