[SOLVED] Macro to Unprotect Sheet, but on Protection not Selecting Correct Filters

[Blue_Eyed_Di]

Hi All,

I'm using this macro, which I got from a thread on here, but when it protects the sheet again, it only ticks the first twp boxes, 'Select locked and unlocked cells'. I need to to protect what originally was selected, which was select locked and unlocked cells, format cells columns and rows, and also sort and use auto filter.

I'm new to macros, so not entirely sure this can be done?

Here is the code I'm using:

Option Explicit
Sub findandreplace()
Dim fStr As String
Dim tStr As String
Dim myRng As Range
Dim myUnlockedCells As Range
Dim myCell As Range
Dim wks As Worksheet
Dim myPWD As String

myPWD = "Password"

Set wks = ActiveSheet

With wks

If .ProtectContents _
Or .ProtectDrawingObjects _
Or .ProtectScenarios Then
'keep going
Else
MsgBox "Sheet is unprotected--just use Edit|Replace!"
Exit Sub
End If

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, .UsedRange)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "Please select cells in the used range"
Exit Sub
End If

For Each myCell In myRng.Cells
If myCell.Locked = False Then
If myUnlockedCells Is Nothing Then
Set myUnlockedCells = myCell
Else
Set myUnlockedCells = Union(myUnlockedCells, myCell)
End If
End If
Next myCell

If myUnlockedCells Is Nothing Then
MsgBox "No unlocked cells in the selected range"
Exit Sub
End If

fStr = InputBox(Prompt:="Change what")
If Trim(fStr) = "" Then
Exit Sub
End If

tStr = InputBox(Prompt:="To what")
If Trim(tStr) = "" Then
Exit Sub
End If

.Unprotect Password:=myPWD

If myUnlockedCells.Cells.Count = 1 Then
Set myUnlockedCells _
= Union(myUnlockedCells, _
.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1))
End If

On Error Resume Next
myUnlockedCells.Cells.Replace what:=fStr, _
replacement:=tStr, lookat:=xlPart, _
searchorder:=xlByRows, MatchCase:=False
If Err.Number <> 0 Then
MsgBox "An error occurred during the mass change!"
Err.Clear
End If
On Error Goto 0

.Protect Password:=myPWD

End With

End Sub


Thank you!

Diane

[ORoos]

Hi Diane,

Try the below;

Towards the end of your code, select:

Please Login or Register  to view this content.

Replace with:

Please Login or Register  to view this content.