Find & Replace within Unlocked Cells in a Protected Worksheet

Hi

Does anyone know how I can allow users to execute Find & Replace actions on
unlocked cells within a protected worksheet? Other cells on the worksheet
are locked.

Best regards
Dave

Maybe you could give the users a macro that gets the info to change and then
unprotects and changes the unlocked cells--then reprotects.

There are lots of options when you do the edit|replace. I chose my favorites
settings. If you want support all the options that excel's edit|Replace dialog
supports, you could build a userform that collects all that information. (Or
you could just assume that they don't care about case and want xlpart (not
xlwhole)...)

Option Explicit
Sub testme()
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 = "hi"

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

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

DaveyC4S wrote:
>
> Hi
>
> Does anyone know how I can allow users to execute Find & Replace actions on
> unlocked cells within a protected worksheet? Other cells on the worksheet
> are locked.
>
> Best regards
> Dave

--

Dave Peterson

Dave

Thanks for this, I am new to macros and this has put me on the right track
to solving this problem.

Many thanks
Dave

"Dave Peterson" wrote:

> Maybe you could give the users a macro that gets the info to change and then
> unprotects and changes the unlocked cells--then reprotects.
>
> There are lots of options when you do the edit|replace. I chose my favorites
> settings. If you want support all the options that excel's edit|Replace dialog
> supports, you could build a userform that collects all that information. (Or
> you could just assume that they don't care about case and want xlpart (not
> xlwhole)...)
>
> Option Explicit
> Sub testme()
> 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 = "hi"
>
> 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
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> DaveyC4S wrote:
> >
> > Hi
> >
> > Does anyone know how I can allow users to execute Find & Replace actions on
> > unlocked cells within a protected worksheet? Other cells on the worksheet
> > are locked.
> >
> > Best regards
> > Dave
>
> --
>
> Dave Peterson
>

Hi!

I know this is an old post, but I'm using it at the moment, but I have a problem! Macro works fine,
except, when I come to look at the protection after its run, it only has 'select locked and unlocked cells' ticked.

I need the options I selected to be ticked when the macro has run, to be ticked after the macro the macro has run.
So these also need to be included:

Format cells, columns, rows, sort and use auto filter.

Is there anyway the macro can be altered to tick these as well?

New to macro's so trying to get a grasp on things!

Thanks

Di