Hi Dorianne

With Dave's code it Enable 522 when you close or go to another workbook.
Insert the code in a new workbook and test it again


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Dorianne" wrote in message ...
Dave, This code works great, but now I've disabled my Options and cannot get
it enabled again. Simply deleting the code from VBA This Workbook did not
work. Please advise.

Thanks

"Dave Peterson" wrote:

You can stop the users from getting to Tools|options with code like:

Option Explicit
Private Sub Workbook_Activate()
Dim myID As Long
myID = 522 '&Options...
Call EnableDisableByID(myID, False)
End Sub
Private Sub Workbook_Deactivate()
Dim myID As Long
myID = 522 '&Options...
Call EnableDisableByID(myID, True)
End Sub

Sub EnableDisableByID(myID As Long, TurnOn As Boolean)
Dim myCommandBar As CommandBar
Dim myCtrl As CommandBarControl

For Each myCommandBar In Application.CommandBars
Set myCtrl = myCommandBar.FindControl(ID:=myID, recursive:=True)
If myCtrl Is Nothing Then
'do nothing
Else
myCtrl.Enabled = TurnOn
End If
Next myCommandBar
End Sub

You can plop all 3 subs right into the ThisWorkbook module. (The last one could
go into a general module, instead.)

But personally, I think won't stop any user who can find the newsgroups.



Mark wrote:

Thanks Dave work a treat. Any ideas on how I can stop people accessing the
options in a protected worksheet or workbook?

Kind Regards...Mark

"Dave Peterson" wrote:

With a macro, kind of like this...

Option Explicit
sub auto_open()
worksheets("myfavoritesheet").select
end sub

Change the name to match your worksheet.

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

Mark_J wrote:

In Microsoft Excel how do you open a workbook to ensure it only opens one a
specified worksheet, no matter what spreadsheet you last saved the workbook
in.

--

Dave Peterson


--

Dave Peterson