Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3861
  • Last Modified:

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

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
VirtualKansas
Asked:
VirtualKansas
  • 3
  • 2
1 Solution
 
rspahitzCommented:
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
 
VirtualKansasAuthor Commented:
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
 
rspahitzCommented:
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
 
VirtualKansasAuthor Commented:
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
 
rspahitzCommented:
Glad to help.
I always figure that extra info is also good to people looking in later :)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now