| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell Color changes On Input even if new input matches default | Excel Programming | |||
| How to input pictures automatically based on cell input? | Excel Worksheet Functions | |||
| input in number form is being multiplied by 1000 when i input. | Excel Discussion (Misc queries) | |||
| How do I add input data in the input ranges in drop down boxes. | Excel Discussion (Misc queries) | |||
| CODE to select range based on User Input or Value of Input Field | Excel Programming | |||
Linear Mode
