Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Protect an Excel SpreadSheet and allow access to an external data source

Posted on 2014-04-03
5
3,214 Views
Last Modified: 2014-04-03
Custom reports from an Access database tables are a great solution for quickly forming the results in a way that can be sorted, filtered and even creating pivot reports.  We're getting a sweet tooth for this technique and it's going well.

But, the SpreadSheet can inadvertently be changed or otherwise mangled and then saved.

I know the deal with protecting Excel, etc. but this breaks the data refresh-ability.  I'm no VBA jockey and can handle macro's, etc. but not proficient or practiced.

Is there a GUI kind of way to allow the SpreadSheet to open and refresh and read the data and allow users to sort, filter, etc., but otherwise protect the SpreadSheet (especially the formulas)?
0
Comment
Question by:VirtualKansas
  • 3
  • 2
5 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 39976285
Is there a specific trigger that will allow the refresh?  For example, does it happen when the user clicks a button?

If so, you can quickly record a macro to disable protection then turn it back on.
After that, copy the recorded code into the button's procedure and insert your refresh code between those lines.
0
 

Author Comment

by:VirtualKansas
ID: 39976387
The trigger is when the Excel file opens (really don;t need the user to hit the data refresh button.)

Recording a macro is easy enough; been years since I've don it.  Mind a little refresher on how to embed the macro into a button?  Also note; SpreadSheet has four worksheet tabs, so would button have to go in a worksheet?

I think the best outcome would be to have the macro run as part of file open and let user just close & open if they want to refresh.  No button needed, then...
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 500 total points
ID: 39976648
There are a few ways you can handle a "button".

You can drop a button on top of any sheet.  That button is then part of that sheet and is transportable (anyone who uses the spreadsheet or a copy of it will see the button.)

You can create a link in the title bar of Excel.  This is typically not transportable and is specific to the person who added that bar-button.
You can add a shortcut key; that's also typically attached to the user, not the workbook.

You can add a new ribbon bar group and include it in that group. (I'm not sure if that's transportable since I've never tried it.)

You can create an add-in (which is probably more trouble than it's worth for something so simple.)

You can also simply leave the macro as user-selectable.  To do that, press Alt-F8 and find the macro and run it.  Anyone can do that with typical macros, as long as macros are enabled when the user opens the workbook.

---
As you were saying, it seems that you want the macro to run on opening the workbook.  To do that, go to the VBA area (Alt+F11), in the top-left panel (Project window) double-click on "ThisWorkbook" to open the code window for the workbook.
At the top-left of the code window, choose dropdown item "Workbook" and you'll get this code block:

Private Sub Workbook_Open()

End Sub

Open in new window

Inside there you can put the code to unprotect, refresh and protect.
And if the users know about Alt+F8, they can run a refresh if you put it in a different macro area, so something like this:

Private Sub Workbook_Open()
   ' unprotect
   RefreshData
   'protect
End Sub

Sub RefreshData()

End Sub

Open in new window

0
 

Author Closing Comment

by:VirtualKansas
ID: 39976765
Really thorough hand holding response, appreciated.  I'm not really that much of a N00B, but it's been a long day/week and head get's thick after a while.  Small details in response made things much quicker to try.  Thanks & regards...
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 39976943
Glad to help.
I always figure that extra info is also good to people looking in later :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question