From: al on
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
From: Per Jessen on
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

On 5 Feb., 16:52, al <transfer...(a)gmail.com> wrote:
> 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

From: JLGWhiz on
Also see response to original post.


"al" <transferxxx(a)gmail.com> wrote in message
news:393812e2-ec2c-4107-8c34-d63dd4c1b4b0(a)t17g2000prg.googlegroups.com...
> 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


From: al on
On Feb 5, 10:08 pm, Per Jessen <perjesse...(a)hotmail.com> wrote:
> 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
>
> On 5 Feb., 16:52, al <transfer...(a)gmail.com> wrote:
>
> > 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

Thxs - working perfectly