[Webinar] Streamline your web hosting managementRegister Today

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

Excel Sheet Lock Down

Is there a way to lock down, so to speak, an Excel sheet that has formulas housed inside hidden columns?  The sheet is used for time entry in a business and people keep deleting and cutting and pasting in rows which messes up the formulas.  The sheets are eventually introduced to an importing interface and if the cells do not contain the proper data, the process is toast.  How do I lock down the sheet?

Why can't you insert a row into a sheet containing formulas and have the formulas automatically formulate the data?
0
CynSzcz
Asked:
CynSzcz
  • 3
  • 2
  • 2
  • +4
2 Solutions
 
TracyVBA DeveloperCommented:
Click Tools --> Protection --> Protect Sheet.  Supply a password if desired, check options you want to allow and click OK

Answer 2:  Because that's not how it works unless you code it :-)
You can easily drag the formulas down to the new inserted row though.
0
 
jppintoCommented:
You need to protect your sheet and cells so that user's can't insert or delete rows/columns.

Your formulas should update automatically when you insert rows/columns. Can you post a sample formula that is not updating?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello CynSzcz,

Before you protect the sheet as Broomee describes, you need to select the cells that you want the users to be able to change, then click Format - Protection tab - untick Locked - OK.

Then protect the sheet.

With regards to your second question, if you click into your data table, then Data - Lists - Create List,
Excel will turn your data table into a special kind of list and whenever you add a new row or insert a row, any format or formula applied to the rest of the list will also apply to the new row.

cheers, teylyn
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

 
jppintoCommented:
0
 
kittenwhiskyCommented:
I can think of the following options:

1) The ranges referred to in the hidden formulas should never be changed.
In that case, broomee's suggestion is perfect : select "untouchable" cells, go to Menu Format->Cells, and select Locked in the Protection tab. Deselect Locked option for all other "changeable" cells. Then protect the worksheet. All locked cells will not allow any changes.

2) The user should be allowed to change the values in the ranges referred to in the hidden formulas.
In that case, you have two choices:

 2a) disallow cutting on specific ranges: copying & pasting does not mess up formulas, only cutting them does.
 2b) rewrite the formulas so that they always refer to the same areas, regardless of cuts were made to the area.
In both cases, you'll also need code that writes the formulas into newly inserted rows.

2b) is most efficient to code, so that is the one I used below.

First of all, you need to know how to write your hidden formula "VBA"style. Easiest way is to turn on MacroRecorder (Tools->Macro->Record New Macro). Then go to the instance of your hidden formula in the upper most row, press F2, and then enter. Stop the MacroRecorder. You'll get something looking like this:

Sub CodeFormula1()
'
' CodeFormula1 Macro
' Macro recorded 06/04/2010 by kittenwhisky
'

'
    Range("H3").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]=""STD"",RC[-7],RC[-6])"
    Range("H4").Select
End Sub

Simplify this to:

Formula(1) = "=IF(RC[-5]=""STD"",RC[-7],RC[-6])"

That's the format we need for our VBA code.
Repeat this once for every hidden formula column, changing the result to Formula(2)= "..." , Formula(3)= "..." etc.

Use these formulas in the code below.

Note: to determine the rows that need hidden formulas, the macro goes from cell A2 to the last non-blank cell in column A. All rows in between will have their hidden formulas updated. Pls change as required.
Also, all hidden columns must be "named" individually, ie. do not abbreviate adjacent columns B&C to range("B:C"), it needs to say range("B:B,C:C") for the code to work.

Sub UpdateFormulas()
Dim DataRows As Range, FormulaColumns As Range, Formula() As String
Dim i As Double

'setup rows that contain data
'' goes from cells "A2" to last non-blank cell in column A
'' change as appropriate
Set DataRows = Range("A2")
Set DataRows = Range(DataRows, DataRows.EntireColumn.Cells(Rows.Count).End(xlUp))

'setup hidden columns
'' all hidden formula columns, each column separated by commas
''change as appropriate
Set FormulaColumns = Range("B:B,C:C,F:F")

'setup Formulas
'' set up a formula for each hidden formula column
ReDim Formula(1 To FormulaColumns.Areas.Count)
Formula(1) = "<< insert VBA-style formula found using Macro Recorder >>"
Formula(2) = "<< insert VBA-style formula found using Macro Recorder >>"
Formula(3) = "<< insert VBA-style formula found using Macro Recorder >>"
'etc.

'insert formulas into each row
For i = 1 To UBound(Formula)
    Intersect(DataRows.EntireRow, FormulaColumns.Areas(i)) = Formula(i)
Next i

End Sub

Open in new window

0
 
CynSzczAuthor Commented:
For jppinto, this is the sheet you requested to look at.  It would be great if the formulas would override any new rows inserted into the sheet by individual timekeepers.  Everyone grabs a new sheet each month but some cut, paste, and insert rows which has messed up the importing process at the end of the month.  If I "lock down" the sheet, will the users still be able to use text replacement and their own macros?  This sheet is perfect in theory but not necessarily execution.  The sheet is used by timekeepers to record their time and then their assistant's enter it into a billing software which processes a .dat file for uploading to an electronic auditing house.  Only certain rows are mapped to the import template.

I am new to Excel and really appreciate your assistance.

Thanks,  Cyn-
MRTimeSheet600-Ins.xls
0
 
jppintoCommented:
It's my impression or the file that you uploaded has no formulas?!?
0
 
CynSzczAuthor Commented:
Column D contains a formula, as well as the columns between F and N.  If you put info. into the columns that appear and then unhide the others, you will see the formulas at work.  Perhaps I have added the them incorrectly.  I am new to this.  It is the hidden columns that are necessary for introduction into the import software later in the process.
0
 
calacucciaCommented:
I know it's an old question (the oldest) but I think attached worksheet does what you need.

Formula's are protected and any inserting or deleting of rows is prohibited.

This is achieved by a simple Sheet protection with only needed stuff unchecked.

Password is empty, just unprotect/protect it through Tools/Protect/Unprotect or Protect Sheet

jppinto deserves the points I think

MRTimeSheet600-Ins.xls
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Broomee and I seem to be the only cleanup volunteers in Excel. Since neither of us gets to clean up questions in which we have posted, it makes sense that this Q has not been cleaned yet. I don't have much hope that the OP returns, though. If they have not reacted to all the nudges from EE, they probably never will
0
 
calacucciaCommented:
Unless the question asker returns, after all, he made the last comment and waited for some input, which he has now :-)
0
 
TracyVBA DeveloperCommented:
>>Broomee and I seem to be the only cleanup volunteers in Excel.

I guess we'll have to make sure not to participate in the same questions, so we can make sure to clean each others up! ;-)
0
 
bromy2004Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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