VBA to unlock worksheet to allow table row to be added and then protected again

  • Good evening all,

    Sorry for the lengthy subject but not easy to explain.

    I have a table in an excel worksheet. I want to lock a number of columns in the table (but not all) and then protect the worksheet to ensure that formulas in those columns are not accidentally deleted/changed. Unfortunately, once I protect the worksheet I am no longer able to add rows to the table via entering a new invoice number in the first column.

    In the attached example a new invoice number in column A will, when unprotected/unlocked, will automatically add a new line across the table column range. The locked columns are H and J.

    Any help would be appreciated - there is no password.

    Many thanks.


  • Try this

  • Hi Roy,

    Thanks for that. It works... but I didn't explain that I wanted the vba to run when a new invoice number is entered into first empty cell in column A (i.e. the first cell in column A below the table).

    I have put in a change event in the worksheet code that will run your code but there are 2 issues:

    1. The change event looks for any change to any cell in column A. I just want it to run when the first empty celling that column is changed.I cannot find a way to include the vba to find the first empty cell within the change event code.

    2. When I run the change event the code adds approx. 150 rows instead of just one. This does not happen when I run your code separately.

    I have attached the revised worksheet.



    PS. I have removed the password.

  • How can you add a new number if the Sheet is protected. You need an empty row to work with.

    This code needs an empty row in the Table for the user to type in. When the code runs it adds a new row for the next time it is used.

  • Hi Roy,

    Thanks. Getting there. The code does what it says on the tin :)

    Just a couple of questions:

    1. Not all columns in the worksheet. Column A is one of these. Hence the original ask.

    2. Is it possible to limit the change event to the last empty cell in column A? (I have found that any further change to already entered entries in column A results in another row being added when it is not necessary.



    • Your protection is not really much use, because it is set wrong. You need the cells in the table to be Locked or else they can be changed even if the sheet is protected. Read this.
    • I'm not sure what you mean about the columns.
    • When you talk about the last cell, you are using a Table so the last cell needs to be in the Table not the sheet.

    Try this

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!