INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

View Only excel?

View Only excel?

(OP)
Is there a way of protecting an excel (2010) spreadsheet so that it becomes view only to anybody else? A quick google search doesn't turn up anything. I have data to share, but it becomes stale very quickly and others in the organization have a tendency to print something out once and refer to it for the next 10 years. I want to publish it on SharePoint and force everybody back to the SharePoint source anytime they want to reference the data.

RE: View Only excel?

Does that lock down 'save as' and 'print'?

Personally I think the cause is hopeless, since a sufficiently determined user will just print screenshots. perhaps you could watermark the spreadsheet with "Latest data for this sheet is at www.idontknow.com. This data is current as of xyz"

Incidentally the concept of transient records in a serious information retention system gets around this, my personal paper copies of a spreadsheet would be destroyed after 3 years, maximum, more likely two years (I quite simply empty the 2014 drawer into the shredder).

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies http://eng-tips.com/market.cfm?

RE: View Only excel?

Greg - I guess the best you can do is save the spreadsheet as a pdf, then save that to not allow printing or save-as.

But even then, pdf passwords are easily circumvented, and as you say, there is nothing to stop people doing a screenshot.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: View Only excel?

(OP)
The whole thing would take 2180 rows (so far, more to come) by 126 columns (also probably more to come) so that I think screen shotting it would be far more difficult than going back to SharePoint every time, but if somebody wants to go to that length, they can knock them self out. The k2e.com link appears to be about read only, not view only; lots of information available about read only. Using SharePoint as the distribution medium I'm not worried about my data getting corrupted; and as long as the code that reads a text file from another application isn't corrupted the data in the spreadsheet is a dead end anyway; the next version of the spreadsheet will come from fresh external data. The SharePoint file may not even include the VBA code.

I'd like to leave it as excel so that people can grab small bits of data for immediate use; that's what it's for but I don't want anybody using this month's data 3, 5, or 10 years from now. I'm working on an extract of the relay settings for our entire system so that other groups that may need to know what the settings are today can access them. But settings change, often, and I want (as does my management) everybody to go back to the well every time they want any setting information. Saving a copy is far too easy; making screen shots of a huge pile of data is, in my estimation, too much work.

If view only isn't possible, I'm probably looking at a .pdf solution with watermarks across the whole thing that the data is void after xx/xx/xxxx. But that's more work for me and somewhat less useful for others. I'd love to just trust people to go back for fresh data every time, but there's well established precedent that they won't. And since they won't, we'll get the "but you said..." complaints when reality doesn't match their year's old spreadsheet.

RE: View Only excel?

If this is a serious issue you need to get management involved to change the culture. If management does not care, then why should you?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: View Only excel?

Nothing's fool proof, particularly when the fool is determined.  I'd try using VBA as an extra layer of protection to anything else you do.  Put the data on a "very hidden" sheet.  Have the workbook_open event handler make the sheet visible, but only if the system date satisfies some sort of recency test.  Obviously you also need to use the workbook_save even handler to make your sheet invisible again.

RE: View Only excel?

davidbeach,

How about a PDF with a watermark that includes the date, and dire warnings about the document going out of date?

--
JHG

RE: View Only excel?

Just like you can create additional ribbon tabs and buttons, you can also use vba to hide tabs in the ribbon on excel 2007 & above. Might at least give the appearance of removing functionalities in combination with other measures.

I like Denial's suggestion.
You can also use vba to alter the visibility of a piece of word art which is used as a watermark. Use this in combination with some code for how current the data is.

Capturing a before print event can enable you to hide the data so only a blank page is printed. A before save event can be used to delete all of the data. Set print area to a blank cell, remove ribbon, remove ability to right click cells, etc... Basically cripple excel when in the spreadsheet.

Some form of punishment (naming and shaming) should follow for those who consistently circumvent the way the data should be used, as skip said management needs to support the correct way of using the data... If not you'll never get there.

RE: View Only excel?

As Denial suggested, make the sheet in question VeryHidden (can only be done via VBA or the VB Editor). Add a slash sheet that tells the user to Enable Macros. When the user enables macros, the VeryHidden sheet become visible and the splash sheet is hidden.

The Before_Print event will Cancel any Excel print command.

The Before_Save event will cancel any Save/SaveAs command.

Opening workbook with your UID will bypass these event results.

This will not prevent someone doing a screen print.

I would further suggest that YOUR "official" print copy, that comes from you as paper or PDF, contain a header/footer that explicitly states, "Valid until date."

Of course all this needs VBA to "lock down" and require the user to Enable Macros or else they don't get a look, if the users will have access to the workbook.

If you want help doing this, you'll get better help at our sister site: http://www.tek-tips.com/threadminder.cfm?pid=707

You may want the users to grab data from this sheet for their use. Hmmmmmmm. Does it matter that when you publish the next version, they still have old data that they copied/pasted from the former version? One other possibility is to give users a user defined spreadsheet function that returns certain data based on some argument value(s). Not knowing the nature of your data it would be impossible to venture a guess at how that might be accomplished. Over a period of 20 years I've supplied a user community with dozens of functions that returned near current data from staged production data tables including part requirements, planned orders, production orders, inventory level, production order operation and much more.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: View Only excel?

(OP)
Thanks for the suggestions. At this point I think I'm leaning toward posting a watermarked PDF. The VBA stuff looks interesting but seems to be getting into the realm of playing games that I don't want to play.

RE: View Only excel?

I would agree!

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: View Only excel?

Google Excel to EXE

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close