MS Project 2010 VBA: Uppercase or Lowercase

Today we are going to try our first VBA script on MAD Schedules!  I promise I’ll try to keep it as simple as possible so you can follow easily.  I assume you have the VBA Editor open and have a module ready for code.  If not, then go back to my post on How to Start with VBA in MS Project 2010.

Okay, let’s start!  I assume you have a schedule with some information.  Here is an example of a schedule I built:

I have a custom field in text1 called “Contract#”.  All the text in text1 is in lower case letters, but what if I want them to all be in upper case letters?  It would take me about 10 or 15 minutes to fix each one at a time… but what if I had 1000 lines?  That would take me quite some time!  Especially if I had different Contract numbers and couldn’t just fix it quickly.  That’s where the beauty of VBA comes in for our MS Project schedules!  I go to my VBA Editor with my blank module such as here:

In the editor you can type:

Sub UpperCase()
Dim tskTask As Task

For Each tskTask In ActiveProject.Tasks
tskTask.Text1 = UCase(tskTask.Text1)
Next tskTask

End Sub

This code can be used for any text field in Microsoft Project.  Just change out Text1 in the code for whatever field you need to change to Uppercase.

How to run your new VBA Script

Now you are wondering, how do I test this script?  First of all, make sure you are testing code on a copy of a schedule.  Do NOT test on a current file!  Once the changes are done with a script, there is NO undo button!  So proceed with caution…

There are a couple of ways you can access and use your new script.  I will touch on one way today.  Go to your project file and click on the Developer tab.  Then click on View Macros.

Make sure you are viewing macros only for your current project and choose Uppercase macro.  Then click on Run.

As you can see it worked!  My “project 001” text was all changed to “PROJECT 001”.

How to change to Lowercase Letters

To change your text to lowercase letters you add a new module to your VBA Editor.  If you try to use the same module as for the Uppercase script, there is potential for errors to be generated because I’m using the same variable for both scripts.

Type in the new module:

Sub LowerCase()
Dim tskTask As Task

For Each tskTask In ActiveProject.Tasks
tskTask.Text1 = LCase(tskTask.Text1)
Next tskTask

End Sub

To run it, go back to your project file Developer > View Macros > Lowercase > Run.  That should do it!

Now that you have a little practice with VBA Script, you can keep modifying this code to do other things in MS Project!

---