none
Insert/ delete row in protected worksheet

    Question

  • 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
    Wednesday, December 08, 2010 5:55 PM

Answers

  • 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

     

    Monday, December 13, 2010 2:32 PM

All replies

  • 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 

    Friday, December 10, 2010 6:53 AM
    Moderator
  • 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

     

    Monday, December 13, 2010 2:32 PM