Macro 'Find and Replace' Function

[darker008]

Is there a way to create a macro using a
custom user form that will act in the same way as the
'Find and Replace' function. I am asking because I am
looking for a way to limit the amout of changes than can
be done while still giving access.

Thank you for your time!

Mystro...

 

[WillR]

Here's a quick example of replacing text using inputboxes & excel's find & replace method. If the sheet is protected you can add some unprotect/protect code before & after the replace code.

If using a custom userform you could assign the values of strMyChar & strMyReplace to two textboxes on the form..

Sub replace_text()
Dim strMyChar As String
Dim strMyReplace As String
Dim rngMyrange As Range
On Error Resume Next
    Do
        Set rngMyrange = Application.InputBox _
            (prompt:="Select a range of cells to action", Type:=8)
            On Error GoTo 0
'Is a range selected? Exit sub if nowt selected
            If rngMyrange Is Nothing Then
                End
                Else
                Exit Do
            End If
    Loop
strMyChar = Application.InputBox _
            (prompt:="What do you want to replace?", Type:=2)
strMyReplace = Application.InputBox _
            (prompt:="What do you want to replace it with?", Type:=2)
With rngMyrange 'with the range just selected
    .Replace What:=strMyChar, Replacement:=strMyReplace, _
    SearchOrder:=xlByColumns, MatchCase:=True
End With
End Sub

Hope it helps

 

[darker008]

Thanks...I'll give it a try

 

[darker008]

is there away, (I know there is), to set in the macro what would already be in the input box?
Even if we scrap the first box, which either way dosent
matter if it is there or not

 

[Smitty]

Welcome to the Board!

Just add a bit to Will's code:

<font face=Tahoma>    InputBox _
        Prompt:="What do you want to replace?", _
        Title:="Find & Replace", _
        Default:="Default Text Here"</FONT>

Hope that helps,

Smitty