Wednesday, December 08, 2010 5:55 PMHi 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.
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
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 thisCornelius
Friday, December 10, 2010 6:53 AMModerator
You can try referring to the article that resolve this issue.
delete/insert row(s) in a protected worksheet?
Monday, December 13, 2010 2:32 PM
thank you for your reply. Unfortunately it seems that you didn't read my problem/ questions thoroughly. A spend a lot of time searching for appropriate solutions and also stumbled upon the one you suggested. But I don't think that this solution has good usability, as the user is forced to adapt to a new way a inserting and deleting rows instead of using inbuilt ways (e.g. context-menu, TAB-key in last row).
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 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).
- Marked As Answer by Harry YuanModerator Tuesday, December 14, 2010 2:19 AM