?
Solved

I have a macro that will delete rows in a protected sheet, but...

Posted on 2014-08-07
3
Medium Priority
?
309 Views
Last Modified: 2014-08-07
I use this code to allow users to delete rows in a protected worksheet.

Sub DeleteMe()
    Dim Ret As Range, Cl As Range

    On Error Resume Next
    Set Ret = Application.InputBox("Please select the Cells", "Delete Rows", Type:=8)
    On Error GoTo 0

    ActiveSheet.Unprotect Password:="Encore"

    If Not Ret Is Nothing Then Ret.EntireRow.Delete

    ActiveSheet.Protect Password:="Encore"
End Sub


Is there a way to exempt one particular row (or a range of rows) from this?  In other words, allow users of a protected sheet to run the macro on all rows EXCEPT specific rows that I don't ever want deleted?

(Disclaimer - I grabbed this code off the internet, and am only vaguely aware of what it means.)
0
Comment
Question by:eracer
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Ejgil Hedegaard earned 2000 total points
ID: 40247168
Try this, protected for rows 7:10, change to your use.

Sub DeleteMe()
    Dim Ret As Range

    On Error Resume Next
    Set Ret = Application.InputBox("Please select the Cells", "Delete Rows", Type:=8)
    On Error GoTo 0
    
    If Not Ret Is Nothing Then
        If Intersect(Ret, Rows("7:10")) Is Nothing Then
            ActiveSheet.Unprotect Password:="Encore"
            Ret.EntireRow.Delete
            ActiveSheet.Protect Password:="Encore"
        Else
            MsgBox "Protected rows selected"
        End If
    End If
End Sub

Open in new window

0
 

Author Comment

by:eracer
ID: 40247275
God I love this place!

Thank you!!!
0
 

Author Closing Comment

by:eracer
ID: 40247284
Quick and exactly what I needed.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question