Hi,

I'm trying to create a worksheet in Excel 2000 to post on an intranet site,
but I don't want users to be able to save copies to their local drives
because we will update reference data periodically.

I tried an auto run macro to disable the menu bar, but I also need it to run
automatically when the worksheet is closed to restore the users defaults (I
don't want any permanent changes to their Excel).

I also tried creating a web site, but I lost too much of the functionality
of the worksheet, so this won't work.

I'd appreciate any help.

Thanks,

Brian

Hi Brian

I have info on this page
http://www.rondebruin.com/menuid.htm

Use this two events in the Thisworkbook module.
Read more about events on Chip Pearson's site.

http://www.cpearson.com/excel/events.htm

Private Sub Workbook_Activate()
'your code to change Enabled to False
'Or the name of your macro
End Sub

Private Sub Workbook_Deactivate()
'your code to change Enabled to True
'Or the name of your macro
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" <Brian wrote in message ...
Hi,

I'm trying to create a worksheet in Excel 2000 to post on an intranet site,
but I don't want users to be able to save copies to their local drives
because we will update reference data periodically.

I tried an auto run macro to disable the menu bar, but I also need it to run
automatically when the worksheet is closed to restore the users defaults (I
don't want any permanent changes to their Excel).

I also tried creating a web site, but I lost too much of the functionality
of the worksheet, so this won't work.

I'd appreciate any help.

Thanks,

Brian

Hi Ron,

This was great. I was able to disable the Save and Save As when I open
workbook, and re-enable when I close.

Now I have another challenge. If the user has their security setting at
Medium, they could open the workbook without enabling the macros. Is there a
way around this?

Thank you very much!

Brian

"Ron de Bruin" wrote:

Hi Brian

I have info on this page
http://www.rondebruin.com/menuid.htm

Use this two events in the Thisworkbook module.
Read more about events on Chip Pearson's site.

http://www.cpearson.com/excel/events.htm

Private Sub Workbook_Activate()
'your code to change Enabled to False
'Or the name of your macro
End Sub

Private Sub Workbook_Deactivate()
'your code to change Enabled to True
'Or the name of your macro
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" <Brian wrote in message ...
Hi,

I'm trying to create a worksheet in Excel 2000 to post on an intranet site,
but I don't want users to be able to save copies to their local drives
because we will update reference data periodically.

I tried an auto run macro to disable the menu bar, but I also need it to run
automatically when the worksheet is closed to restore the users defaults (I
don't want any permanent changes to their Excel).

I also tried creating a web site, but I lost too much of the functionality
of the worksheet, so this won't work.

I'd appreciate any help.

Thanks,

Brian

Hi Brian

There is no safe way

One way is to hide all sheets except one and unhide them in
the workbook open event.
Place a message on that sheet with "you must enabled macro's to work with this file"
And hide the sheets in the beforeclose event.
So the user can't use the workbook if he disable macro's.
If he do the workbook open event don't run so there are no sheets
to work with
You must protect your project also in the VBA editor because a user can't unhide
the sheets there also if you don't do that.

Some example code to do this
Sheet 1 stay always visible

Sub HidealmostAll()
Dim a As Integer
For a = 2 To Sheets.Count
Sheets(a).Visible = xlVeryHidden
Next a
End Sub

Sub ShowAll()
Dim a As Integer
For a = 2 To Sheets.Count
Sheets(a).Visible = True
Next a
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" wrote in message ...
Hi Ron,

This was great. I was able to disable the Save and Save As when I open
workbook, and re-enable when I close.

Now I have another challenge. If the user has their security setting at
Medium, they could open the workbook without enabling the macros. Is there a
way around this?

Thank you very much!

Brian

"Ron de Bruin" wrote:

Hi Brian

I have info on this page
http://www.rondebruin.com/menuid.htm

Use this two events in the Thisworkbook module.
Read more about events on Chip Pearson's site.

http://www.cpearson.com/excel/events.htm

Private Sub Workbook_Activate()
'your code to change Enabled to False
'Or the name of your macro
End Sub

Private Sub Workbook_Deactivate()
'your code to change Enabled to True
'Or the name of your macro
End Sub




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Brian C" <Brian wrote in message ...
Hi,

I'm trying to create a worksheet in Excel 2000 to post on an intranet site,
but I don't want users to be able to save copies to their local drives
because we will update reference data periodically.

I tried an auto run macro to disable the menu bar, but I also need it to run
automatically when the worksheet is closed to restore the users defaults (I
don't want any permanent changes to their Excel).

I also tried creating a web site, but I lost too much of the functionality
of the worksheet, so this won't work.

I'd appreciate any help.

Thanks,

Brian