Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input box replace text in textbox
How can I modify the macro below with input box code to replace "2008"
by "2009" using an input box - so that i can replace "2008" by "2010" more easily if required - thxs Sub masschange() 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, "o", "X") 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 replace text in textbox
If you are using this macro to update annually, then change this:
Application.Substitute(shp.DrawingObject.Caption, "2008", "2009") To this: longstr = longstr & Application.Substitute(shp.DrawingObject _ ..Caption, Format(Date - 365, "yyyy"), Format(Date, "yyyy")) That should work every year. "al" wrote in message ... How can I modify the macro below with input box code to replace "2008" by "2009" using an input box - so that i can replace "2008" by "2010" more easily if required - thxs Sub masschange() 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, "o", "X") 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Textbox input control? | Excel Programming | |||
Textbox input interpretation | Excel Programming | |||
validating input in textbox | Excel Programming | |||
How do I search and replace text in a textbox in Excel? | Excel Discussion (Misc queries) | |||
validation of input in textbox | Excel Programming |