Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-04-03
5
Medium Priority
?
3,706 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
[X]
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
  • 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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 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