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