MSP VBA Basic Help Needed!

[Aerospace_Brett]

I'm trying to add common text names from a Text Column in MSP-07. I'm using
Rod Gill's book to learn VBA for Office and primarily MSP and have also
emailed him specifically about whether or not MSP-07 is even capable of
accomplishing the simple report I'm needing.

Basically I have a column (Text26) with some specific verbage which will be
standardized and used for several real schedules in our standard template
with our company. The verbage is simply:
* Early Delivery
* Late Delivery
* On-Time Delivery
* Not Delivered

The verbage is calculated as a formula based on other criteria, bottom line
is I need to add or count the number of each type of the above verbages
within a report. I'd like our Control Account Managers for our projects to
be able to click a button/macro in Project and produce this end-product.

Can anyone help me with this or even tell me if I'm dreaming and it's not
possible, even that's fine. I'm scrambling to learn VBA and realize now
it'll be a good assett with my background in project management &
planning/scheduling but we've all been there where we need to be experts
yesterday!

Thanks in advance for any help!

Brett

 

[Jim Aksel]

I think Rod's book explains how to put up a button on a custom toolbar, look
for one of his examples.

As for the guts of it, here is some code below that will do the trick and
send up a message box with the task counts for you. It would also be
possible to write this out to a spreadsheet but that would get a little
involved for four numbers.

A couple more things, this is really a developer question and additional
questions like this should be posted there. If you need detailed Project
Management expertise and additional tools for MS Project, you may want to
consider some research on the link below.

Here' some get started code:

Public Sub Counter()
Dim tsk As Task
Dim intEarlyDelivery As Integer
Dim intLateDelivery As Integer
Dim intOnTime As Integer
Dim intNotDelivered As Integer
Dim intIdunno As Integer
intEarlyDelivery = 0
intLateDelivery = 0
intOnTime = 0
intNotDelivered = 0
intIdunno = 0

For Each tsk In ActiveProject.Tasks
Select Case tsk.Text26
Case "Early Delivery"
intEarlyDelivery = intEarlyDelivery + 1
Case "Late Delivery"
intLateDelivery = intLateDelivery + 1
Case "OnTime"
intOnTime = intOnTime + 1
Case "Not Delivered"
intNotDelivered = intNotDelivered + 1
Case Else
intIdunno = intIdunno + 1
End Select
Next tsk
MsgBox ("Early Delivery: " & intEarlyDelivery & vbCr & vbLf & _
"Late Delivery: " & intLateDelivery & vbCr & vbLf & _
"On Time: " & intOnTime & vbCr & vbLf & _
"Not Delivered: " & intNotDelivered & vbCr & vbLf & _
"I Dunno " & intIdunno)


End Sub

--
If this post was helpful, please consider rating it.

Jim Aksel, MVP

Check out my blog for more information:
http://www.msprojectblog.com

 

[Aerospace_Brett]

I just came across Rod's custom toolbar button section, that would be nice to
have on our standard template, easy to use, but the end-result I should have
been 100% clear on is a "report) that shows the actual count summaries. In
other words click a button with the end product or report being the output to
print, pdf, email, etc to the PM. Exporting to Excel although I've
considered would not suffice based on our requirements for the CAMs to
efficiently use this feature provided I can create it.

I'll try your example below and thanks for the tip on where I should post
this, I didn't think it was a developer question but can see it's
relativeness to that subject now.

Thanks again,
Brett