ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Input Box (https://www.excelbanter.com/excel-programming/439251-input-box.html)

al
Input Box
 
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[_2_]
Input Box
 
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 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

JLGWhiz[_2_]
Input Box
 
Also see response to original post.


"al" wrote in message
...
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



al
Input Box
 
On Feb 5, 10:08 pm, Per Jessen 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 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


All times are GMT +1. The time now is 11:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com