Insert/ delete row in protected worksheet

[Cornelius]

Hi guys,

i know there are a lot of threads about this topic on the net. Most of
them provide solutions using macros in order to manually insert rows
on executing. Desipite the efforts that people put into their
solutions (and there are nice once), none of them really do convince
me, because they all lack usability.

A short introduction to the problem:
Michael Weinhardt posted a thread that is spread over many forums with
the title "Can't insert row in data table in protected worksheet" that
describes best, what I am (and I guess many others) searching for.
Unfortunately there are no solutions available.
I have a data table, where I have columns that include formulas that
need to be protected in order to prevent accidental changes. The
worksheet is protected in a way that rows can be inserted and deleted.
Of course this does not actually work well, as there are cells
included in every row that are protected.

The problems:

1. If I insert a new row (somewhere in the middle of the table) a
message-box pops up telling me that "the cell or chart you are trying
to change is protected and therefore read-only". I don't really
understand, why this has to be, but I can't have it for productive
use.

2. If I want to insert a new row at the end of the table (e.g. going
to the last cell of the table and press TAB), a new cell isn't entered
automatically (but it is, if I unprotect the sheet).

3. If I want to delete a row it is not possible, as there a protected
cells within the row (although the function was activated when
protecting the worksheet)

As I am concerned about the usability of the table a macro that needs
to be invoked manually is not a solution that I can accept. Users
should be able to work as they usually do. Actually I personally
believe that what I want is not that unusual or caused by exceptional
perceptions on what should be possible.
If you for example unprotect a cell that includes a formula, you will
notice a exclamation mark next to the cell, telling you that this cell
contains a formula, but is not protected. But as long as you don't
protect the worksheet, the cell-protection itself is useless. But if
you protect the worksheet, you are not able to insert/ delete rows
that way you would expect it to. I more and more believe that this is
simply caused by bad software-design and not because I want to do
something very unusual.

The only solution that I came up with, is to unprotect the sheet and
rather create a macro that checks the important formulas when invoking
the Workbook_BeforeClose-Event. If these have been altered the user
gets a notice, whether the change was on purpose or if the old
forumula should be restored.

I am thankfull for any advice you share on how you handled this
problem.
Kind regards,

Cornelius

 

[DCG-jaeson]

Hi cornelus,

Why the Worksheet is being protected? Might be for some security
reason..Right? But if for the first place you are the one who create
the Template, why don't you Unprotect first then Protect it again once
you are done? The best simply solutions I can suggest! Haha!

 

[Cornelius]

Hi Jaeson,

well, as I am not the one working with the sheets, but people who have
just basic knowledge in Excel and won't understand huge formulas that
we need to calculate internal values, it is better to protect parts of
the worksheet in order to avoid changes by accident. But as people
need to work with the sheets, i.e. need to be able to insert and
sometimes delete rows standard protection functions cannot be applied,
unless there is a solution that I don't know of....

Cheers, Cornelius

 

[Gord Dibben]

Excel's worksheet protection features can be confusing.

You can supposedly "allow" many features when protecting a sheet, but most of
these will not function as you think they should.

e.g. you can allow "sorting" but turns out you can only sort within a
contiguous range of unlocked cells.

Same for "Use Autofilter"..............only if autofilter has been enabled
before protection is set.

Easy enough to insert new row(s) in a protected sheet..............you are not
attempting to alter any locked cell(s).

Tools>Protection>Protect sheet with allow insert rows function.

Deleting row(s) is not so easy if any cell in that row has been locked.

You must unprotect, delete then re-protect while allowing users no access to any
cells.

Sub delete_row()
ActiveSheet.Unprotect Password:="justme"
ActiveCell.EntireRow.Delete
ActiveSheet.Protect Password:="justme"
End Sub

Returning to row insertion..........................

When inserting a row do you need users to do anything with the cells in that
row?

Do they have to enter data or change the format(including cell-locking or
un-locking)?


Gord Dibben MS Excel MVP

 

[Cornelius]

Hi Gord,

thank you for your reply. Well, I spend a lot of time searching for
appropriate solutions and also stumbled upon the once you mentioned.
The problem is, that besides the configured protection I still get the
message "the cell or chart you are trying to change is protected and
therefore read-only" when inserting a new row. Also I don't think that
this solution for deleting rows has good usability, as the user is
forced to adapt to a new way of working instead of using inbuilt ways.

I would claim that 50% of the success of optimizing report creating is
determined not by smart technical solutions, but by user-centeredness
and the fact, whether you can manage to convince people that your
solution is better than what they had before. Otherwise, they will
themself come up with own solutions that fit their needs and way of
working more than what the "IT-guy" came up with. (might be slightly
different in other companies and countries, but I am currently working
for an NGO in south-america - here that is definitely important).

Here is the solution that I came up with: In "ThisWorkbook" insert to
the following method, that will be invoked by the beforeClose-Event.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'check if formulas that shouldn't be changed, have changed
Dim formula_1 As String
Dim formula_2 As String

formula_1 = "the formula 1"
formula_2 = "the formula 2"
checkList = Array( _
Array("AD8", formula_1, "Accounting I"), _
Array("AE8", formula_2, "Accounting II"), _
)

Dim sheet As Worksheet
For Each sheet In ActiveWorkbook.Worksheets
Dim item As Variant
For Each item In checkList
If Not Range(item(0)).Formula = item(1) Then
note = "La fórmula para " & item(2) & " de hoja " & sheet.name
& " ha cambiado. ¿Restaurar el valor de edad?"
Answer = MsgBox(note, vbQuestion + vbYesNo, "Nota:")
If Answer = vbYes Then
Range(item(0)).Formula = item(1)
End If
End If
Next item
Next sheet
End Sub

The Macro checks certain fields, whether for formula has changed and
can restore the old one. When you want to change the formula, you just
have to change the formula in the marco, as when invoking the macro on
beforeClose the formula is updated within each sheet. This macro works
regardless the language that had been installed (we work with german,
english, spanish versions). This way the user can work the way he is
used to work, but you are still able to protect, what you want to have
protected.

With field-validation and pre-set formatting I am convinced that it's
enough to ensure correctness and completeness of data

HTH! Regards,

Cornelius

 

[Gord Dibben]

Your method is one way of looking for alterations and should be sufficient for
your purposes.

But I don't know why you would be getting the "cell or chart etc." message
when inserting a row on a protected sheet.

I cannot replicate that.

Select a row header and CTRL + SHIFT + + and row is inserted quietly.


Gord

 

[Cornelius]

Hi Gord,

well could be like this, because I use a table (listObject) instead of
a simple range.
I tested and had the same behaviour with Excel 2007 (PC) aswell as
with Excel 2011 (Mac).

Regards,

Cornelius