Prepend text to selected cells

[Phil]

I am developing a sub that will allow a user to specify text to be
prepended to cells in a column, then specify what cells to change.
This code works well as long as the column has not been renamed. Any
suggestions on hot this code could be modified to handle renamed
columns? Or an alternate method for prepending data?


Option Explicit
Sub Prepend()

Dim strPrepend As String
Dim strTemp As String
Dim strFieldName As String
Dim Msg, Style, Title, Response
Dim lngTaskCount As Long
Dim lngCounter As Long

Msg = "What text do you want to prepend to selected cells in this
column?"
Title = "Enter Text to Insert" ' Set title.
strPrepend = "38A71" ' Set default.
' Display message, title, and default value.
strPrepend = InputBox(Msg, Title, strPrepend)
strFieldName = ActiveCell.FieldName ' get the current name of the
column

lngTaskCount = ActiveProject.Tasks.Count
lngCounter = 0

Do While lngCounter < lngTaskCount

'skip if blank
If Len(Trim(ActiveCell)) = 0 Then GoTo SkipCell

' skip if already prepended with strPrepend
If InStr(ActiveCell, strPrepend) = 1 Then GoTo SkipCell

Msg = "Do you want to insert " & strPrepend & " at the start of
this cell?"
Msg = Msg & vbCrLf & vbCrLf & "Yes to insert " & strPrepend
Msg = Msg & vbCrLf & "No to skip this cell"
Msg = Msg & vbCrLf & "Cancel to stop this macro"

Style = vbYesNoCancel + vbDefaultButton3
Title = "Insert Data?"
Response = MsgBox(Msg, Style, Title)

Select Case Response
Case vbYes
strTemp = ActiveCell
SetTaskField Field:=strFieldName, value:=strPrepend &
strTemp
'Above line does not work if column has been renamed

Case vbCancel
Exit Do

Case Else
GoTo SkipCell

End Select

SkipCell:
SelectTaskField Row:=1, Column:=strFieldName
lngCounter = lngCounter + 1

Loop

End Sub

 

[John]

Phil,
See you first post.
John