Protect Excel formulae but allow insert/delete? 1

[AlisonH]

I've got a spreadsheet with some complex formulae in it that's kept on the server for my colleagues to update with their data. Unfortunately every now and then someone will overtype the formulae with a value and mess it up.

I've tried protecting just the formulae, but it causes chaos because that stops them inserting and deleting rows. Is there any way round this?

Thanks, Alison.

 

[SkipVought]

Hi, AlisonH,

Yes, but it involves using VBA code.

You remove all formulae and write code that fires on the Worksheet_Change event. Depending on the complexity of your worksheet, it could be a big job, but it solves the problem of formulae getting deleted or corrupted. Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Alison,

Another option... attach the following code to two macro buttons entitled: "INSERT Row - at Cursor Position", and "DELETE Row - at Cursor Position".

This would leave the sheet protected, and still allow the user to insert and delete rows. You naturally would STILL be at "some" degree of risk that a user would delete a row that contains formulas. However, depending on the specifics of your application, you can gauge the degree of risk, and make the determination of whether this is an acceptable approach.

Sub Insert_Row()
Unprotect_Sheet
ActiveCell.EntireRow.Insert
Protect_Sheet
End Sub

Sub Delete_Row()
Unprotect_Sheet
ActiveCell.EntireRow.Delete
Protect_Sheet
End Sub

Sub Unprotect_Sheet()
ActiveSheet.Unprotect "123"
End Sub

Sub Protect_Sheet()
ActiveSheet.Protect "123"
End Sub

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

Alison,

Just thought of yet another option...

Again, whether this option is workable will depend of the specifics of your application.

What about placing your formulas on a separate sheet from the user-input sheet ??? You could then protect the sheet that contains the formulas, and leave the user-input sheet unprotected.

Naturally your formulas will need to reference the user-input data, but this might be possible - using Excel's "database functions" that can "pull in" data from another sheet, based on "criteria" that defines the data you need "pulled in". And, these formulas will work even though users insert or delete rows.

If you don't yet have experience with these "database functions", then you probably can use some help, especially given that there are a couple of "quirks" to using these functions.

If you want to consider this route, and could use help, please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[AlisonH]

Thanks very much for your help. I've decided to leave the formulae on the protected sheet because the users need to see the impact of their changes, so I've added a toolbar with buttons to insert and delete rows, left/center/right align and bold/italic text.

Another quick question - to get the bold and italics buttons to toggle I presume I need to test what they currently are and then make it true or false. How do I get this to work?

Regards, Alison.

 

[DaleWatson123321]

Alison,

The additonal buttons you refer to, ALL "toggle", so the user will ONLY need to click on them - to choose the "opposite" of how the cell is currently formatted.

(THANKS for the STAR !!!)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[AlisonH]

Thanks for your help so far, Dale, and you're welcome to the star Unfortunately my buttons don't toggle so I think I'm doing something wrong. At the moment I've got buttons to turn on bold and italics, but this doesn't turn them back off so I've added a plain text button to my toolbar as a workaround. Is there a better way to do it?

Thanks, Alison.

Sub Italics()
ActiveSheet.Unprotect
Selection.Font.Italic = True
ActiveSheet.Protect
End Sub

Sub Bold()
ActiveSheet.Unprotect
Selection.Font.Bold = True
ActiveSheet.Protect
End Sub

Sub PlainText()
ActiveSheet.Unprotect
Selection.Font.Bold = False
Selection.Font.Italic = False
ActiveSheet.Protect
End Sub