Delete dynamic table rows without removing password protection

[remeng]

I have a simple line of code that I'd like to run while a worksheet is password protected. Is it possible to run this without removing the password protections? There will not be a password since that level of restriction isn't required.

    ThisWorkbook.Sheets("Pick List").ListObjects("pick_table").DataBodyRange.Delete

Allowed Protections required:

[ul]
[li]Select Unlocked cells[/li]
[li]Format Cells[/li]
[li]Insert Rows[/li]
[li]Delete Rows[/li]
[li]Sort[/li]
[li]User Autofilter[/li]
[li]Use PivotTables and PivotCharts[/li]
[li]Edit Objects[/li]
[/ul]

Thanks for the help

 

[combo]

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

ThisWorkbook.Sheets("Pick List").Protect Password:="ThePassword", UserInterfaceOnly:=True
ThisWorkbook.Sheets("Pick List").ListObjects("pick_table").DataBodyRange.Delete

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


combo

 

[remeng]

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

 

[combo]

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

combo

 

[remeng]

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?

 

[Andrzejek]

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

 

[combo]

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

 

[remeng]

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.