disable excel except to "view"

  • Thread starter Thread starter Lj
  • Start date Start date
L

Lj

Hello folks!

I have a protected excel file that I make available to several
computers from our server, just to read. But I need even more
protection: I want patrons to have access to read this file but NOT
to be able to then click the "new" icon to go on and create any new
excel files. Can I disable this? i.e. like an "excel viewer" only,
not the ability to create further excel sheets, but on a computer
that does have the excel program.
(the computer also has Fortres, but if I include excel in it's list
of prohibited programs, they can't view the file anymore).
thanks,
lj.
 
Hi Lj

I'm abit confused why you not want anyone to create a new Workbook while
your Workbook is open? It can be done with VBA, just wan to ensure that
is really what you want. I take it the Workbook is passworded for Read
Only?Do you have internal Workbook Protection applied? E.g
Tools>Protection>Protect Workbook. This will stop users from inserting
new sheets.

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Hi ya:
yes, it may seem odd, but here's the situation: these are restricted
computers in a high school library just to let students view the
catalogue and this particular excel file (a special list of books
not on the catalogue).
I have the sheet protected, and read only. But once they are looking
at it, they can just click the little "new page" icon in excel and
start a whole other excel sheet going. Not a huge deal, I just hate
frustrating kids with "sorry, you can't use this computer for that"
if they've already started. Rather just make the feature unavailable
so they can't start in the first place.
thanks for any thoughts. Sorry: what is VBA?
LJ.
 
VBA is a programming language that sits behind excel. It's a way of giving
excel users the power to really screw up things really fast <vbg>.

You can lots of things in macros that you can't do via the user interface. You
can do lots of repetitive things quicker, too. And you can almost prevent users
from opening a second workbook (or creating a new workbook) when there is
another workbook loaded.

If you want to give it a go, here's one way that seemed to work ok for me:

Start a new workbook.
RightClick on the icon to the left of File option on the worksheet menu bar.
select View Code. You'll be taken to the VBE where VBA code lives.


Paste this in:

Option Explicit
Dim myOpenClass As Class1
Private Sub Workbook_Open()
Set myOpenClass = New Class1
Set myOpenClass.xlApp = Application
End Sub
Private Sub Workbook_Close()
Set myOpenClass.xlApp = Nothing
End Sub

Now hit Ctrl-R (to see the project explorer--much like windows explorer)

Right click on your project.
It should look like:
VBAProject (book1)
Select Insert|Class Module
It should be called Class1.
Paste this in:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
If xlApp.Workbooks.Count > 1 Then
MsgBox "Cannot create a new workbook"
Wb.Close savechanges:=False
End If
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
If xlApp.Workbooks.Count > 1 Then
MsgBox "Cannot create a new workbook"
Wb.Close savechanges:=False
End If
End Sub

Now back to excel.

File|SaveAs
Change Save As Type to: Microsoft Excel Addin (*.xla)
Give it a nice name and store it in your favorite folder.

I'm guessing that it should open automatically when you start excel. (You could
have others using the pc.)

Each time you need to use it, just File|open and open this addin.

It won't affect any existing workbooks you already have open, but'll try to stop
any new.

==
Why the "try" stuff?

If you have an excel user who's smart enough to disable events or just close the
addin, then it won't work. But I bet it would stop lots.

If you really want it to load each time you start excel, you could store the
addin in your XLStart folder.

(You could toggle it on/off by keeping it hidden away, but installing it via
Tools|Addins. But your users could disable it there, too.)

If you want to learn about application events, then Chip Pearson has some nice
notes at:
http://www.cpearson.com/excel/AppEvent.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
 
I think I'd load this via tools|addins. Then if the user closed excel, it'll be
open the next time--but it's still easy to toggle on/off if you need to.

(But after reading Gord's reply, I'd dump all of this and use his suggestion!)
 
Thanks guys, I got some good chuckles- I think Gord's suggestion
sounds nice and simple too.
ta, LJ.

(but I might have to try yours Dave just to have fun tinkering ;-)
 
Back
Top