×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Delete dynamic table rows without removing password protection

Delete dynamic table rows without removing password protection

Delete dynamic table rows without removing password protection

(OP)
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.


CODE

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

Allowed Protections required:

  • Select Unlocked cells
  • Format Cells
  • Insert Rows
  • Delete Rows
  • Sort
  • User Autofilter
  • Use PivotTables and PivotCharts
  • Edit Objects
Thanks for the help

RE: Delete dynamic table rows without removing password protection

Repeat protection with UserInterfaceOnly:=True before starting changing the sheet:

CODE -->

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

RE: Delete dynamic table rows without removing password protection

(OP)
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

RE: Delete dynamic table rows without removing password protection

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

combo

RE: Delete dynamic table rows without removing password protection

(OP)
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?

RE: Delete dynamic table rows without removing password protection

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

RE: Delete dynamic table rows without removing password protection

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

RE: Delete dynamic table rows without removing password protection

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close