prevent users from adding sheets / charts

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

All,

Searched for a while, but haven't found an answer to this one yet. I have a
workbook that I do not want the users to add worksheets or charts to. In
particular by a stray keystroke of F11 (like when I forget to hold alt and
hit it myself :-/ )

Is there a way to disable adding sheets / charts?

TIA,
Ian
 
Ian;

Did you Google on "Disable F1 Key".
I know there is some stuff to find about that topic and your question might
probably be answered the same way.

Actually, here it is.

a.. Unhide your personal.xls file
a.. Tools->Macros->Visual Basic Editor
a.. Choose the "ThisWorkbook" file icon in the upper left hand box
a.. Add this VB code:

Private Sub Workbook_Open()
Application.OnKey "{F1}", ""
End Sub
a.. Save the file, and close.

Result: No more accidental MS help windows!

I did not test it, but I assume that it might help with F11 as well.

--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands (the country were they live some 18 feet beyond sea level)

E: (e-mail address removed)
W: www.spreadsheetsolutions.nl
 
Ian;

Did you Google on "Disable F1 Key".
I know there is some stuff to find about that topic and your question might
probably be answered the same way.

Actually, here it is.

a.. Unhide your personal.xls file
a.. Tools->Macros->Visual Basic Editor
a.. Choose the "ThisWorkbook" file icon in the upper left hand box
a.. Add this VB code:

  Private Sub Workbook_Open()
  Application.OnKey "{F1}", ""
  End Sub
a.. Save the file, and close.

Result: No more accidental MS help windows!

I did not test it, but I assume that it might help with F11 as well.

--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands (the country were they live some 18 feet beyond sea level)

E:  (e-mail address removed)
W:www.spreadsheetsolutions.nl








- Show quoted text -
Click to expand...

Wouldn't the simplest solution be to make the spreadsheet read-only so
no-one can make permanent changes?

Rob
 
Protecting the workbook (structure) prevents the addition of sheets.
--
Jim Cone
Portland, Oregon USA



"Ian"
wrote in message
All,
Searched for a while, but haven't found an answer to this one yet. I have a
workbook that I do not want the users to add worksheets or charts to. In
particular by a stray keystroke of F11 (like when I forget to hold alt and
hit it myself :-/ )
Is there a way to disable adding sheets / charts?
TIA,
Ian
 
Hi guys,

Thanks for the replies. Unfortunately, the book I'm making is a score sheet
that the users have to enter data into, however I do not want them to be able
to add worksheets via Insert>Worksheet, nor do I want them to add a chart
sheet via Insert>Chart or simply the F11 key.

I cannot make the workbook read only as that defeats the purpose of the
score sheet.

Mark's solution of the onkey command takes care of part of the problem.
Anyone know how to prevent the rest?

Thanks,
Ian
 
And what is the problem you have when you protect the workbook | structure?
--
Jim Cone
Portland, Oregon USA




"Ian"
wrote in message
Hi guys,
Thanks for the replies. Unfortunately, the book I'm making is a score sheet
that the users have to enter data into, however I do not want them to be able
to add worksheets via Insert>Worksheet, nor do I want them to add a chart
sheet via Insert>Chart or simply the F11 key.
I cannot make the workbook read only as that defeats the purpose of the
score sheet.
Mark's solution of the onkey command takes care of part of the problem.
Anyone know how to prevent the rest?
Thanks,
Ian
 
Nothing... just couldn't get the code to work... everything good now. Thanks
for pointing me in the right direction.

For those searching, code is:

Workbooks(Workbook_Name).Protect Password:=PW, Structure:=True

and to unprotect
Workbooks(Workbook_Name).Unprotect Password:=PW


-Ian

Jim Cone said:
And what is the problem you have when you protect the workbook | structure?
--
Jim Cone
Portland, Oregon USA




"Ian"
wrote in message
Hi guys,
Thanks for the replies. Unfortunately, the book I'm making is a score sheet
that the users have to enter data into, however I do not want them to be able
to add worksheets via Insert>Worksheet, nor do I want them to add a chart
sheet via Insert>Chart or simply the F11 key.
I cannot make the workbook read only as that defeats the purpose of the
score sheet.
Mark's solution of the onkey command takes care of part of the problem.
Anyone know how to prevent the rest?
Thanks,
Ian
Click to expand...
 
Back
Top