Toolbars and options on OPEN

[Sean Evanovich]

I have a workbook that allows the users to select a few
values and then use those values to open the appropriate
PDF file on the network (statement files that contain
year, quarter and ssn in their name).

It all seems to be working fine - but now I would like
to change the look of the workbook so that the user is
limited in what they see and can do.

The workbook will be stored as "read only" and I have
created an EXIT button which will close the workbook
without asking if they wish to save it. But I'd also like
to be able to do the following (if possbile):

Turn off the "Close" X button in the top right hand
corner.

Open the workbook with the column, row, formula, format,
etc. bars and options OFF.

I have done some of these things via code in Access, I'm
just not sure how you do it in Excel.

Thanks for reading...
Sean

 

[onedaywhen]

This is not recommended. MS Excel is a designed to allow users to
freely modify workbook's contents. Excel users don't like developers
changing their user settings and disabling functionality i.e. you may
find they stop using your software.

 

[Guest]

I appreciate your concern for the intended uses of Excel
but my request was in an effort to "help" a situation by
making it less confusing, if possible.

The idea is to limit functionality "during" the use of
this specific workbook, and then to return the settings
upon exit.

It's really not that big of a deal...I'm simply trying to
eliminate a "Do you want to save?" message that could
possibly confuse these particular users.

If they use my exit button, they will not see the
message, but if the use the Excel close, they will.

 

[Dave Peterson]

Tom Ogilvy posted these links:

http://support.microsoft.com/support/kb/articles/Q107/6/89.asp
XL: Disabling Microsoft Excel Control Menu Commands

http://support.microsoft.com/support/kb/articles/q117/8/55.asp
XL: Removing the Control Menu and Application Window Controls


http://support.microsoft.com/support/kb/articles/q147/6/59.asp
HOWTO: Get a Window Handle Without Specifying an Exact Title

http://support.microsoft.com/support/kb/articles/Q112/6/49.asp
HOWTO: Get a Window's Class Name and Other Window Attributes

===
David Hager posted this from Andrew Baker:
http://groups.google.com/groups?threadm=uJJXRxp2#GA.259@cppssbbsa05

====
And if you record a macro when turning off the options, you can name it
Auto_open. Then each time your workbook opens, it'll do what you want.

Putting the "undo" code into auto_Close is one way to turn it back on when
you're done.

But I agree with OneDayWhen. I wouldn't want something that does this to my
environment.

And if the user has more than your workbook open, you probably won't want to
disable the menus for that workbook.

You'll have to take care of putting them back when they deactivate your
workbook's windows and hide them when they activate a window in your workbook.

You may want to look at work at:
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
and
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)

workbook events found under ThisWorkbook in the VBE.

And a couple of links to describe events and macros:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm
and
David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm