Input Box
in [Programming]
|
From: al on 5 Feb 2010 10:52 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 5 Feb 2010 13:08 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 5 Feb 2010 14:04 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 5 Feb 2010 23:31 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
|
Pages: 1 Prev: Excel 2003 VBA Phantom Breakpoints Next: Dynamic Array not response |