LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
al al is offline
external usenet poster
 
Posts: 64
Default 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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Color changes On Input even if new input matches default JBark Excel Programming 1 September 25th 09 05:24 AM
How to input pictures automatically based on cell input? bsharp Excel Worksheet Functions 9 May 30th 09 07:16 AM
input in number form is being multiplied by 1000 when i input. jweinograd Excel Discussion (Misc queries) 4 April 16th 07 11:18 PM
How do I add input data in the input ranges in drop down boxes. oil_driller Excel Discussion (Misc queries) 1 November 9th 05 10:31 PM
CODE to select range based on User Input or Value of Input Field Sandi Gauthier Excel Programming 4 December 8th 03 03:22 PM


All times are GMT +1. The time now is 06:27 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"