View Full Version : Using Trim, LTrim, RTrim in MS Project

07-18-2017, 11:45 AM

It's been quite a while since I have worked in VBA. I saw this question posted, but there was no solution and it was several years ago.

Goal: Review all lines of the MS Project schedule and remove extra spaces (leading, between words, and at the end). I believe I can use the Trim, LTrim, and RTrim... but I'm not having any luck.

Note that my FixCase macro works to remove the leading spaces but 1) I'm not sure why and 2) I need it to also find double spaces between words.

Here's my FixCase macro in case it's easy to add the LTrim to this somewhere.

Thank you in advance for the assistance.

Sub FixCase()
Dim t As Task
Dim ts As tasks
Set ts = ActiveProject.tasks
Dim Original As String
Dim TskCnt As Long
Dim diag As New ProgressDialogue
Dim i As Integer
'Turn auto calculation OFF - if ON it significantly slows the macro
OptionsCalculation Automatic:=False
'Configure the progress window
TskCnt = ActiveProject.tasks.Count
i = -(0.5 * TskCnt)
diag.Configure "Fix Case Progress", "Checking Task", -(0.5 * TskCnt), (0.5 * TskCnt)
For Each t In ts
'This will not check TASK 0

diag.SetValue i
diag.SetStatus "Checking Task " & t.ID
If diag.cancelIsPressed Then GoTo Endroutine
i = i + 1
On Error GoTo ErrorHandle
Selection = ProperCase(t.Name, 0, 0)
Original = t.Name

If Selection <> Original Then
diag.SetStatus "Updating " & t.ID
t.Name = Selection
End If

Next t

'Turn auto calcuation back ON
OptionsCalculation Automatic:=True
MsgBox ("Fix Case Complete")
Exit Sub
OptionsCalculation Automatic:=True
MsgBox ("FixCase ecnountered an error at Task ID: " & t.ID & vbNewLine _
& "Please check for double hyphens (--)")

End Sub