Digital Spy

Search Digital Spy
 

DS Forums

 
 

More Excel help please


Reply
Thread Tools Search this Thread
Old 08-02-2013, 21:51
fat controller
Forum Member
 
Join Date: Oct 2005
Location: Slightly round the bend
Posts: 12,685

I have a fairly complex spreadsheet, which is used to collate date across a 28 day period; at the end of each 7 day period, there is a summary page which totals up all the data for the week at the top half of the page, and then has a number of cells at the bottom half of the page (comments text, time, quantity, cost 1, cost 2) x 10 lines for each day.

I need to lock all the cells on the page to prevent users from knackering the formulae in there, however in doing so I am also preventing a few people in head office from being able to select the cells to copy the information in them and then pasting that information into a word document or a blank workbook.

At the moment, I have given the select few the password to remove the protection, however this is less than ideal - so, is there a way that I can allow them to select a set of cells to copy the information (not the formulas) in them to then be pasted into another workbook?

Or, is there a way to have a macro output the information from those cells directly into a new/blank workbook that the user can then copy and paste what they want?

Thanks in advance.
fat controller is offline   Reply With Quote
Please sign in or register to remove this advertisement.
Old 08-02-2013, 22:47
scrabble123
Forum Member
 
Join Date: Nov 2010
Posts: 53
why not put a password to modify on it? that way people can open it and use it but they can't save the changes as it would be read only.
scrabble123 is offline   Reply With Quote
Old 08-02-2013, 22:59
fat controller
Forum Member
 
Join Date: Oct 2005
Location: Slightly round the bend
Posts: 12,685
Think I have cracked it with a macro:

Sub Output_Macro()
'
' Output_Macro Macro
'
' Keyboard Shortcut: Ctrl+o
'
ActiveSheet.Unprotect Password:="XXXXXXX"
ActiveWindow.SmallScroll Down:=60
Range("A64:M154").Select
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.WindowState = xlMinimized
Application.CutCopyMode = False
ActiveSheet.Protect Password:="XXXXXXX", DrawingObjects:=True, Contents:=True, Scenarios:=True




End Sub

This outputs the values only to a new workbook which I hope will be sufficient for the onward copy/paste operation.

Thanks
fat controller is offline   Reply With Quote
Old 08-02-2013, 22:59
Smiley433
Forum Member
 
Join Date: Apr 2006
Location: Location: Location
Posts: 3,619
so, is there a way that I can allow them to select a set of cells to copy the information (not the formulas) in them to then be pasted into another workbook?
Not sure if this is what you want to achieve, and also not sure of which version of Excel you're using, but you can copy what you want from the spreadsheet and instead of doing "Paste", in the Excel 2000 that I use you can do a "Paste Special" and from there select "Values" which pastes the result of any formula but not the formula itself. However this will not copy any formating to the target cell which may affect things.
Smiley433 is offline   Reply With Quote
Old 08-02-2013, 23:05
fat controller
Forum Member
 
Join Date: Oct 2005
Location: Slightly round the bend
Posts: 12,685
Not sure if this is what you want to achieve, and also not sure of which version of Excel you're using, but you can copy what you want from the spreadsheet and instead of doing "Paste", in the Excel 2000 that I use you can do a "Paste Special" and from there select "Values" which pastes the result of any formula but not the formula itself. However this will not copy any formating to the target cell which may affect things.
Thanks - I am currently using Office 2010, but it has to be an Office 2003 file as many of the PC's at work are somewhat antiquated.

I will have to ask on Monday to see if this solves the problem, and if not it will be back to the drawing board.

The best of it is, this spreadsheet is used by a considerable number of people, and does exactly what we need of it, however there is a small group who need to be able to copy and paste some of the values onward to other docs/sheets. As mentioned above, they currently have the password to open and relock the spreadsheet, but as this is getting rolled out to a wider and wider audience, I am getting all the more jittery that someone will bend it out of shape and then I will be spending all my time fixing the various copies of the sheet being used - hence the desire to lock it completely.
fat controller is offline   Reply With Quote
Old 08-02-2013, 23:20
Smiley433
Forum Member
 
Join Date: Apr 2006
Location: Location: Location
Posts: 3,619
Could your users who just want the summary information use another workbook which has a permanent link to the cells in the "master" spreadsheet? That way they don't need to open the source spreadsheet (although Excel will open it to get the values) and there's no danger of incorrect editing. Might be an issue if you are using differently named spreadsheets at the end of each 7 day or 28 day period, but provided they are issued with the name of the file, you could write a macro in their workbook which asks which file they want to access.

Would everyone have access to that macro you've written above? If it contains the actual password then it doesn't look to be very secure - is this an issue?
Smiley433 is offline   Reply With Quote
Old 09-02-2013, 00:37
SnrDev
Forum Member
 
Join Date: May 2011
Posts: 2,787
Hands up I've been on the wine, but in essence you want to make data available but without allowing edits or updates. Am I close?

In database terms you need a view of the source data. There are probably better ways of achieving this but why not create a new workbook and make each cell a reference to the correspondin cell in the original workbook. I've done that before, extending the reference to include another workbook name along with the worksheet name and cell reference. It'll take a bit of effort to create it but you can then allow access to the vew without affecting source data. Probably.

I'll pop back tomorrow to see how barmy this idea probably is.

Eit - I see Mr Smiley above has suggested something similar. Neat.
SnrDev is offline   Reply With Quote
 
Reply



Thread Tools Search this Thread
Search this Thread:

Advanced Search

 
Forum Jump


All times are GMT +1. The time now is 18:35.