Solved

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

Posted on 2014-04-03
5
2,834 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now