Input Box

[al]

How can I modify the macro below with input box code to replace "2008"
by "2009" using an input box more easily - (i.e an input message to
input 2008 followed by an input message to input 2009 thxs

Sub Replacetext()

Dim ws As Worksheet
Dim longstr As String
Dim i As Long

For Each ws In ActiveWindow.SelectedSheets
For Each shp In ws.Shapes
'shp.Select
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.TextBox Then
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.TextBox
Then
shp.OLEFormat.Object.Object.Text =
Application.Substitute(shp.OLEFormat.Object.Object.Text, "2008",
"2009")
End If
End If
End If
ElseIf shp.Type = msoTextBox Then
longstr = ""
For i = 1 To shp.DrawingObject.Characters.Count Step 250
longstr = longstr &
Application.Substitute(shp.DrawingObject.Caption, "2008", "2009")
Next i
For i = 1 To Len(longstr) Step 250
shp.DrawingObject.Characters(Start:=i, Length:=250).Text
= Mid(longstr, i, 250)
Next i
End If
Next shp
Next ws

End Sub

 

[Per Jessen]

Hi

Here's a way to do it:

Sub ReplaceText()
Dim ws As Worksheet
Dim longstr As String
Dim i As Long
Dim TextA As String
Dim TextB As String

TextA = InputBox("Existing text", "Replace")
TextB = InputBox("New text", "Replace")

For Each ws In ActiveWindow.SelectedSheets
For Each shp In ws.Shapes
'shp.Select
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.TextBox Then
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.TextBox
Then
shp.OLEFormat.Object.Object.Text =
Application.Substitute _
(shp.OLEFormat.Object.Object.Text, TextA, TextB)
End If
End If
End If
ElseIf shp.Type = msoTextBox Then
longstr = ""
For i = 1 To shp.DrawingObject.Characters.Count Step 250
longstr = longstr & Application.Substitute _
(shp.DrawingObject.Caption, TextA, TextB)
Next i
For i = 1 To Len(longstr) Step 250
shp.DrawingObject.Characters(Start:=i, Length:=250).Text
_
= Mid(longstr, i, 250)
Next i
End If
Next shp
Next ws
End Sub


Regards,
Per

 

[JLGWhiz]

Also see response to original post.

 

[al]

Hi

Here's a way to do it:

Sub ReplaceText()
Dim ws As Worksheet
Dim longstr As String
Dim i As Long
Dim TextA As String
Dim TextB As String

TextA = InputBox("Existing text", "Replace")
TextB = InputBox("New text", "Replace")

For Each ws In ActiveWindow.SelectedSheets
For Each shp In ws.Shapes
'shp.Select
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.TextBox Then
If shp.Type = msoOLEControlObject Then
If TypeOf shp.OLEFormat.Object.Object Is MSForms.TextBox
Then
shp.OLEFormat.Object.Object.Text =
Application.Substitute _
(shp.OLEFormat.Object.Object.Text, TextA, TextB)
End If
End If
End If
ElseIf shp.Type = msoTextBox Then
longstr = ""
For i = 1 To shp.DrawingObject.Characters.Count Step 250
longstr = longstr & Application.Substitute _
(shp.DrawingObject.Caption, TextA, TextB)
Next i
For i = 1 To Len(longstr) Step 250
shp.DrawingObject.Characters(Start:=i, Length:=250).Text
_
= Mid(longstr, i, 250)
Next i
End If
Next shp
Next ws
End Sub

Regards,
Per

Thxs - working perfectly