Auto insert new rows in 'list' when sheet is protected (Excel 2003)

[cfoulstone]

Guys

I have a company sheet that is being developed for use by others. I need to lock the sheet in terms of formula editing etc but need to allow users to add data into the list and for the list to automatically expand when the user reaches the end of the defined list. The list function does this itself when the sheet is not protected but the protection function prevents the list from expanding.

If anyone can point me in the right direction I would be very greatful.

Thanks in advance.

 

[Damon Ostrander]

Hi cfoulstone,

Typically this type of problem is solved via a macro which disables the ESC key, then unprotects the sheet, makes the desired changes (i.e., adds to the list), re-protects the sheet, and finally re-enables the ESC key. The ESC key disabling is to prevent the user from aborting the macro after it has unprotected the sheet, leaving the sheet unprotected.

I can't give you any more than this without knowing details of your worksheet layout and how you add to the list.

I hope this helps.

Damon

 

[cfoulstone]

Hi cfoulstone,

Typically this type of problem is solved via a macro which disables the ESC key, then unprotects the sheet, makes the desired changes (i.e., adds to the list), re-protects the sheet, and finally re-enables the ESC key. The ESC key disabling is to prevent the user from aborting the macro after it has unprotected the sheet, leaving the sheet unprotected.

I can't give you any more than this without knowing details of your worksheet layout and how you add to the list.

I hope this helps.

Damon

Damon

Thanks for the information.

My sheet has the following layout....

Rows 1:4 contain information that is outside of the 'list'.
The current list is in the range A5:O37 and each column contains various formulae and data validation rules etc. that are identical down the entrie column. This range has been named 'VO_List', if that helps at all.
Row 5 contains my list headers.
At row 38 I have 3 cells H38, K38, N38 that are sums from columns within the list, based on defined named ranges for each column.
At the moment, if the sheet is unprotected, once the user has entered text in, say, column A at row 37, upon pressing return the list automatically inserts another row. In doing so it moves the sums down to row 39 and expands the defined named ranges to include the newly inserted row 38, thereby updating my sum cell.

I need to protect the sheet to prevent other users from editing the formulae and/or the data validation rules etc. Which I presume wouldn't be a problem if the macro was to unprotect and then re-protect the sheet, I could have any form of protection in place.

If needs be, I don't mind having a button to click in order to insert extra rows to the list, albeit I would rather it simply operate in the same manner as it does without the protection in place.

If you or anyone else could help me out at all I would really appreciate it.

Thanks

 

[Damon Ostrander]

Hi again cfoulstone,

You shouldn't need a button if you are currently adding the new row the way I think you are. My guess is that you are using the Worksheet_Change event to trap a change to the last row of VO_list, and it contains code that adds the new row and re-defines the VO_list range. If this is correct, I suggest you post the code of this Sub procedure and I'll post the necessary modifications to it (using a fictitious protection password, of course) to allow it to run on the protected worksheet.

Damon

 

[cfoulstone]

Hi again cfoulstone,

You shouldn't need a button if you are currently adding the new row the way I think you are. My guess is that you are using the Worksheet_Change event to trap a change to the last row of VO_list, and it contains code that adds the new row and re-defines the VO_list range. If this is correct, I suggest you post the code of this Sub procedure and I'll post the necessary modifications to it (using a fictitious protection password, of course) to allow it to run on the protected worksheet.

Damon

Damon

Apologies for the delay in responding to you.

I don't have any Sub procedure in place. In fact there is no code authored by me, only standard Excel functions available through the GUI menus etc.

Is there a way to still use your Sub procedure idea without there being a Worksheet_Change event as part of a code?

Alternatively, could you please assist me to write a Sub procedure that performs this operation for me, instead of the list function within the GUI?

Many thanks

 

[Damon Ostrander]

Hi again cfoulstone,

Now I'm a bit confused. You mentioned that you already are using a method that adds the row automatically:

"At the moment, if the sheet is unprotected, once the user has entered text in, say, column A at row 37, upon pressing return the list automatically inserts another row. In doing so it moves the sums down to row 39 and expands the defined named ranges to include the newly inserted row 38, thereby updating my sum cell."

I don't know any way of doing this without code. I would be happy to write the code to do this, but if you are already doing it without code I should take advantage of your method. Would you explain to me? Hopefully I can then provide the code to do the unprotect, apply your method, then reprotect the sheet.

Damon