Sign in
 
Home20132010Other VersionsLibraryForumsGallery
TechCenter >
> 
> 
Insert/ delete row in protected worksheet

Answered Insert/ delete row in protected worksheet

  • Wednesday, December 08, 2010 5:55 PM
     
     
    Sign In to Vote
    0
    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
     

All Replies

  • Friday, December 10, 2010 6:53 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

     

     

    You can try referring to the article that resolve this issue.

     

    delete/insert row(s) in a protected worksheet?

     

    http://www.mrexcel.com/forum/showthread.php?t=19897

     

    Sincerely,

     

    Harry 

     
  • Monday, December 13, 2010 2:32 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi Harry,

    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).

    HTH! Regards,

    Cornelius