Delete dynamic table rows without removing password protection

Repeat protection with [tt]UserInterfaceOnly:=True[/tt] before starting changing the sheet:


The protection has to be set every time you open the workbook, works as long as the workbook is open.


combo

Hi Combo,

Just to confirm, this allows all the functions of the macro to run, but still prevents the user from having access to the protected catagories?

I read the Microsoft article and just want to make sure that I have it correct.

Link

Thanks

Yes, after setting UserInterfaceOnly:=True protection affects only user actions, VBA can operate as if the worksheet has no protection set.

combo

Thanks combo. It looks like there is a secondary issue that I didn't see until I started testing.

So, the user is copying data from a file where the cells are defaulted to locked when the worksheet is password protected. When I set the paste to worksheet to not protect the cells in the column, I can add and delete cells without issue.

Once the user paste data from the outside workbook, the cells retain the locked cell properties and then prevent the pasted cells from being modified.

Is there a way to cause the pasted cells to paste by default with the paste to location properties instead of the source cell properties?

Just an idea…
With all protect/unprotect, cells, worksheets, columns, rows, passwords, etc. Maybe Excel is not the best tool to do it? Maybe a data base would be a better fit for your needs?

I know (hope?) you keep it straight in your head. But imagine: you win big money and move on. Who is going to keep it up / understand what had been done here? Unless you keep really good documentation about the whole process.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

The data and structure seems to be crucial in your application. If so, why not take full control of the paste process? You can add button(s) to the worksheet to paste data, control destination and additional processing.

I am with Andy, sometimes native Excel is too flexible to simply and securely handle designed processes. Even if, modifications in sophisticated solution may be horrible.

combo

Hi Andy and Combo,

Trust me when I say this, a database is going to be a whole lot more difficult for someone to understand and maintain lol.

I did think about the paste button, but that is going to create more problems for the particular user. It always sucks when you need to design something for the one user that will cause the most damage to something so simple.

The good thing this is going to be stored on a SharePoint site so I can recover the file. This might be one of those "oh well, keep a backup" kind of moments.

Thanks for the support.