Retain dynamic Table feature in Password-Protected Sheet

[newqueen]

Hi All,

When I password-protect a worksheet that has a dynamic Table, I lose the dynamic effect of the Table. I need help with this, please.

I populate an Excel 2007 dynamic table with data from a Userform. But when I try to password-protect the sheet containing the Table, the dynamic feature/effect stops working. In order to protect the sheet, I apply a vba code to ensure that when data is being written onto the Table (from the Userform), the table is unprotected, and when the writing is complete, the table is protected.

Below is the code that I use:

Please Login or Register  to view this content.

With the above code applied to the Userform, the next step I take is to go back to Excel, Protect Sheet, and add a password - hoping to click 'Save'. But as soon as I click OK after confirming the password, the dynamic feature of the Table disappears, and the Table no longer works as a Dynamic Table. If I remove the password just added, the feature comes back.

I need to protect the Sheet, and I need help with this, please.

Thank you all for your support.

Newqueen

[TMS]

Hi Newqueen,

I would suggest that the first thing you try is to unlock all cells below the Table Header and then make sure that you allow locked and unlocked cells to be selected. You need to be able to select locked cells so you can use Sort and Filter from the header cells.

The other option is to NOT protect the sheet but use the Selection and/or Change events to control access to the Table and surrounding cells. So, for example, if you have any rows/cells above the table, you control access to them using the Selection event and move the selection point to a "safe/home" cell. You can allow access to the Table Header but prevent changes using Application.Undo in the Change event. Access to the table body would be unlimited as normal.

I'm not sure if the first option will work but I've used the second approach.

Good luck.


Regards, TMS

[newqueen]

Dear TMS,

Thanks for your response. I'll explain what I did originally, so you'd determine what I might have done wrong.

First of all, there are no rows/cells above the Table. I didn't want to complicate issues for myself by having rows/cells above the table. The Table has headings and rows below the headings where data is transferred to from the Userform. My intention was to protect other parts of the sheet and unprotect the cells where data goes into. Here is what I did:-

1. Highlight whole sheet
2. Right click, Format Cells, Protection
3. Checked the two boxes 'Locked' & 'Hidden', OK
4. Then highlight the columns where data will be input (the DYNAMIC feature/effect operates in these columns)
5. Right click, Format Cells, Protection
6. Uncheck the two boxes 'Locked' & 'Hidden' - for these columns.
7. Then go to Excel Protection (password protect), SAVE

It is on the 7th step - after password protecting, that the dynamic effect of the columns disappears. But if I leave out the 7th step above, the dynamic effect remains. If it's possible I'd like to password-protect the sheet and retain the dynamic effect of the table.

Your second option sounds better and I'm willing to try that. However, my vba knowledge and skills are very poor. I don't know the vba code and steps to take to achieve that option. I'd be grateful for your further help with this.

Thank you.

Newqueen

[TMS]

I would suggest trying not locking anything and seeing if that makes a difference. It may be that adding a new entry to a Table wants access to all the columns. That is just a guess though.

The following code uses the Selection Change event to manage access on a sheet where a Table was necessary ... pretty much the same situation that you have.

It is monitoring a Log sheet and uses a number of Named Ranges that determine specific areas above and within the Table. As it is the Log sheet, I chose to prefix all the names with "l".

Please Login or Register  to view this content.

This checks an are above the Table where the user can select a cell, lViews. Then it checks if the Target row is less than lAnchor row ... which is actually a cell in the Table Header row. If the cell selected is within the Table that's OK regardless of whether it is the Header or Data area.

And finally, if the cell is below the Table area, it generates a new entry ... in my case, I needed to generate a unique reference


So, four monitored areas:

1. above the Table but OK - do nothing
2. above the Table but not in the OK area, select a "home" cell, which was the cell at the intersection of the Freeze panes.
3. within the Table area - OK, do nothing
4. below the Table area - OK, create entry

I don't recall selecting cells to the right of the Table area giving me an issue, hence, I don't think I trapped it. You might need to add a check for a cell being selected after the last column of the Table and move the selection somewhere "safe".

So, that's the basic idea but clearly it's not all applicable to your case.


Regards, TMS

[newqueen]

Thanks TMS for the valuable time that you've invested to help. I am grateful.

I shall study your code, try it out and also try out your other suggestions.

Meanwhile, where do I place the code into? Is it in 'TheWorkbook' or create a Module for it, or in any particular sheet?

Again, thanks for your help.

Newqueen

[TMS]

The code needs to go in the sheet module for the worksheet where the Table resides.

Thanks for the rep

[newqueen]

Thanks TMS. I'd certainly go back and try out your suggestions.

Newqueen