Sleeper: Find and replace txtbox in toolbar

[ilyaskazi]

hi there,

how to make 2 txtboxes in toolbar for find and replace???

[Regouin]

hi,

When asking questions try to elaborate as much as possible, that way it is easier for both of us. You want a userform with 2 txtboxes where you can input the data to be found in one textbox and the data to replace it with in the other?

frank

Make a new userform
create textboxes
create a button

in the code for the userform copy paste this code.

give the button the name cmdreplace
make sure you use the first textbox for your find string
and the second textbox for the replace string
alter the range as desired




Option Explicit

Private Sub cmdreplace_Click()
Dim original As String
Dim edited As String
original = TextBox1.Text
edited = TextBox2.Text
Worksheets("blad1").Range("A1:A65536").Replace _
what:=original, Replacement:=edited, LookAt:=xlPart, _
searchorder:=xlByRows, MatchCase:=False
End Sub

[Killian]

or for a toolbar:


Dim TBar As CommandBar
'routine to build toolbar - put in workbook open event

Sub BuildToolbar()
Dim ctrle As CommandBarControl
Dim ctrlb As CommandBarButton
DeleteToolbar
Set TBar = Application.CommandBars.Add("Find & Replace")
With TBar
Set ctrle = .Controls.Add(msoControlEdit)
ctrle.Caption = "txtFind"
Set ctrle = .Controls.Add(msoControlEdit)
ctrle.Caption = "txtReplace"
Set ctrlb = .Controls.Add(msoControlButton)
ctrlb.Caption = "Go"
ctrlb.Style = msoButtonCaption
ctrlb.OnAction = "ReplaceString"
.Visible = True
End With
End Sub

'routine to delete toolbar - put in workbook close event


Sub DeleteToolbar()
For Each TBar In CommandBars
If TBar.Name = "Find & Replace" Then TBar.delete
Next
End Sub

'search and replace code - put in new module


Sub ReplaceString()
Dim myRange As Range, c As Range
Dim firstAddress
Dim strF As String, strR As String
strF = Application.CommandBars("Find & Replace").Controls(1).Text
strR = Application.CommandBars("Find & Replace").Controls(2).Text
If strF <> "" And strR <> "" Then
Set myRange = Application.ActiveSheet.Cells
With myRange
Set c = .Find(strF, LookIn:=xlValues)
If Not c Is Nothing Then
Do
c.Value = strR
Set c = .FindNext(c)
Loop While Not c Is Nothing
End If
End With
End If
End Sub

but maybe with some more work done on the search&replace code. I don't like the way these textboxes behave - you have to hit return for the text field to be set or it reverts to the previous. also, because its a toolbar, you can't tab thru the fields so I think a UserForm would probably be easier.
I suppose it might be possible to build a custom class for the toolbar text controls and try to get them a bit more useable !?