Prepend data to selected cells in a column

[Phil]

I am trying to develop a sub that will allow a user to put specified
data in front of selected cells in the database. The following code
works fine as long as the column has not been renamed. I am looking
for suggestions on renameing a column, or an alternate idea for how to
prepend data.

------------ cut ------------------------
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,
First of all, this code isn't directly modifying the Project database,
it is operating on the application objects directly. The changes will
become part of the Project database once the file is saved.

Things look pretty good up to the point where you go through the loop
and those "GoTo" statement have got to go. Try the following type of
loop. It will be immaterial what column name is given to the field. This
code also gets you away from "foreground" processing and allows you to
operate directly on the selected field objects in the "background".

SelectTaskColumn
Set Area = ActiveSelection.Tasks
For Each t in Area
If Not t is Nothing Then
[your text manipulation code here]
End If
Next t

Try that and see how it works.

John

 

[John]

Phil,
See my response to your last "post".
John

 

[Phil]

See my response to your last "post".

John,
Thanks for the response.

For the first three posts, IE/Google kept timing out and I did not get
a confirmation. So I kept posting until I got a positive response.
Note to self: Assume the post goes through the first time.

With the changes based on what you suggested, the macro appended many
copies of the string to the first cell. I am sure I did not correctly
implement your guidance.

SelectTaskColumn
Set Area = ActiveSelection.Tasks
For Each t In Area
strTemp = ActiveCell
SetActiveCell (strPrepend & strTemp)
Next t

In any case, I wanted to give the user the opportunity to evaluate the
cells that were not blank and not already prepended with the text to
be inserted. I believe the background method does not allow this.

The function I was looking for to change the value of the active cell
was SetActiveCell (seems very obvious in retrospect).

I don't think GoTo statements to ensure all routes from a block of
statements pass through the same exit code are anathema, but I
re-arranged the code to eliminate them.

I ended up with this code; any comments are welcome:
----------------- cut -------------------------
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

If ActiveSelection.FieldIDList.Count = 1 Then

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)

lngTaskCount = ActiveProject.Tasks.Count
lngCounter = 0

Do While lngCounter < lngTaskCount

'In not blank and not already prepended with strPrepend
If Len(Trim(ActiveCell)) > 0 And InStr(ActiveCell, strPrepend)
<> 1 Then

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
SetActiveCell (strPrepend & strTemp)

Case vbCancel
Exit Do

End Select
End If
lngCounter = lngCounter + 1
SelectCellDown
Loop

Else

Msg = "Please select a single column"

Style = vbOKOnly + vbExclamation + vbDefaultButton1
Title = "Improper Selection"
Response = MsgBox(Msg, Style, Title)

End If

End Sub

 

[John]

Phil,
When I first started using the newsgroup I also accessed it through
Google. However the time delay (up to 9 hours) drove me nuts. Then I got
a newsreader and everything is pretty much real time.

My apology on my code snippet. I forgot one very critical part of the
code. That is to identify the actual field that is selected. Once a code
line is added to do that, just modify the "If" statement and follow with
the rest of your code. But, you don't need the IngCounter and you don't
need to do a "SelectCellDown". You also may not need the multiple column
selection message because the code automatically selects just the first
active column. Here is the corrected code:

SelectTaskColumn
Fld = ActiveSelection.FieldIDList(1)
Set Area = ActiveSelection.Tasks
For Each t In Area
If t.GetField(Fld) <> "" And _
InStr(t.GetField(Fld), strPrepEnd) = 0 Then
[your code here]
End If
Next t

There are very few procedures that cannot be performed using background
processing and this is not one of them. However, if your code does what
you need, you are all set.

John

 

[John]

Phil,
I had an afterthought later in the day. I re-read your original message
and wondered why the user needs to select the field in the first place.
I assume you only want to add (or not) the prefix to a given field. If
that is the case, the code could be further automated and simplified.

John