MSP VBA Basic Help Needed!

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
0
Utf
12/8/2009 6:24:01 PM
project 1276 articles. 0 followers. Follow

2 Replies
1615 Views

Similar Articles

[PageSpeed] 13

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" wrote:

> 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
0
Utf
12/8/2009 7:57:01 PM
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

"Jim Aksel" wrote:

> 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" wrote:
> 
> > 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
0
Utf
12/8/2009 8:52:01 PM
Reply:

Similar Artilces:

Formula in MSP
Hi, I am using MSP 2003. I want to know about all the customizing formulas used in MS project with examples. It is there any website, how the formula we can use in ms project?. Thanks In the Project Help screen, type in "formula" and you should get an option for all formulas in custom fields. - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > Hi, > I am using MSP 2003. I want to know about all the customizing > formulas > used in MS project with examples. It is there any website, how the > formula we > can use in ms project?. ...

Help with a module
Hi, A few months ago I posted this question: > I am trying to make the default value the date of the first monday in > the month in an unbound box. Is there a way to do this? I used the following solution by Arvin Meyer, but today I ran into a problem. Since the first monday is the 5th and today is the 1st, I need the first monday of last month instead. I know this only happens a few days every month, but I thought there might be a way to fix this. Could someone help me modify this module for this situation? James ____________________________________________________ Paste the fol...

need some help with an array function
I want to replace a calculated column (Dif in example below), with an array. I want to create the fourth column (Ranking) without having the third column (Dif). EXAMPLE: A B Dif Ranking 12 11 1 1 34 22 12 4 16 12 4 2 18 13 5 3 Dif is A2-B2 for row 2, A3-B3 for row 3, etc. My plan was to use =rank(A2-B2,A2:A5-B2:B5,0) and hit Ctrl+Shift+Enter to tell it that A2:A5-B2:B5 is an array, but it isn't working. (Excel won't let me enter that formula at all.) =rank(A2-B2,C2:C5,0) works as expected. Obviously, I could calculate column C and hide it but this is not what I want to do. Can some...

VBA-Openquery
I have a command,and I click it,it needs to open a query ,but for some conditions(for example:"name"="tom" or "sex"="male" etc)What's the VBA should be?Thanks!John Jiang2007-03-14 Thank you very much!John Jiang07-03-14"John Jiang" <toddy8205@hotmail.com> д����Ϣ����:%23PdqmphZHHA.2436@TK2MSFTNGP06.phx.gbl...> Mr B,>> Thank you for your answer.> There is a predefined query, the values of fields are already > supplied by the user,and "names" is a field of the query.> Otherwise,the user have Tex...

FetchXML help!
I am trying to fetch all records which match certain criteria, and am having a problem when the attribute name I am looking for exists in both the root entity and the link-entity. Example: Fetch all opportunities where the opportunity name starts with "Test" OR the account name starts with "Test". This will find all opportunities whose account name starts with "Test" <fetch mapping='logical'> <entity name='opportunity'><all-attributes/> <link-entity name='account' to='customerid'> <fil...

VLookup help !!
Can anyone help with this. I have give a workbook a variable name . When I try to call it in the Vlookup formula it does not recognise the file name. Here is the sequence dltr = InputBox(" Enter date of file (Ex: 12_07 ):") Set swb = Workbooks.Open("c:\Sprint\vendors\FMHC\FMHC SD Tracker " + dltr + ".xls") mwb.Activate Range("o1") = "column title" Range("o2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],swb.worksheets(1)!C1:C35," & hdr & ",0)" Can anyone tell me how to use swb wi...

Help Program Bug
When the money help program starts I receive the following error pop up: "A Runtime Error as occured, Do you wish to Debug", Line 7, Error: Expected ";" Never Mind, I found the bug. My money file name had a apostrophe in it. Apparently Microsoft did not take this into consideration. I simply removed the apostrophe and all is better. "JohnC" wrote: > When the money help program starts I receive the following error pop up: > "A Runtime Error as occured, Do you wish to Debug", Line 7, Error: Expected > ";" ...

SUMIF help and another query
I'm wondering if someone could help with a problem I am having - not so much a problem - more I'm not as good at Excel as others here and they may be able to help. I'm treasurer of a semi-professional non-league football club. As such I have to calculate travelling expenses for each player. The tax man taxes players on home games and training sessions but doesn't tax the travelling expenses for away games for those players that take their own cars and not the bus. Currently we pay them 15p per mile plus an additional 3p per mile for every passenger - I know, not much - ...

VBA wildcards
Im trying to use a wildcard calling the names of subshapes (grouped shapes). The shape names are in the format of "Property Cell.#" or "Property Cell.TEXT" Like "Property Cell.3" and "Property Cell.UnitNumber" etc. I want it to iterate through all shapes with "Property Cell" in the name. However, I cannot get the wild cards that are in the VBA help to work. Im trying "*" and "?" although from what Im reading, "*" should work. My code looks like this: For Each subShpObj In shpsObj If subShpObj.Name ...

lookup help #5
Second try with this...I'll try to explain it better. I have a two-cell worksheet win workbook1. It is replaced every day, automatically with another two cell sheet in a new workbook with the same name. A1 is a date. B1 is number, eg. 1542. I want to automatically place the number into a cell in another workbook and sheet, by matching the date with dates in column A of the second sheet, and Column E (fillers)of the second sheet. I've been playing with match, offset and index, lookup etc. I've been able to get a number from DATE:E and have it show up in the first workbook.sheet, but...

Transfer Worksheet into Array in one chunk with VBA?
I've searched the Microsoft.public.Excel.misc archives and my VBA books but have not found the answer to this query If you have a spreadsheet with numbers that has 40 columns and 3000 rows how do you transfer that sheet into a 2D array in one chunk without using a loop? For instance you can transfer an 3000x40 array into a spreadsheet by Activeworkbook.Sheets(1).Cells(1,1).Resize(3000,40) = Array How do you go the other way? I've tried Dim Array () as Variant ReDim Array (1 to 3000, 1 to 40) Array() = Activeworkbook.Sheets(1).Range(Columns(1),Columns(40).Value but this did not w...

Make Table v. Append to Table: Need Some Expert Design Inputs
I am working on updates to a couple related databases (not linked by any tables) and have a question about virtues of Make Table vs Append queries. Make Table: I understand that this will create a NEW table where ever it is pointed, so if that table already exists in the destination it will be deleted, and the new table written in its place. Append : In this case I understand that it would require a little more "work" to do same as Make Table, i.e. clearing out table before appending data. The 1st ma...

Excel Add-in: Setup Wizard or Installation program needed
Hello, I have created an add-in that I want to be able to distribute as shrink-wrap solution. The problem is that I can't find a way to create an install progra that will identify the user's O/S, Excell version, and the Add-in pat to place the module in. I had thought I could use the "setup wizard" but I have Office 200 Professional (not the developer edition) and so it appears that th wizard is not in my version... I also thought that I could use the "Package & Deployment wizard" fro VB studio 6, but it only looks for "Visual Basic Projects (*.vba) wh...

Opening multiple programs in VBA
I am trying to open two different program files, one after the other, in VBA within an Excel macro. This is what I have so far: Shell ("C:\Program Files\Quick3270\Quick3270.exe" "C:\Program Files\Quick3270\ IBM System.ecf") This does not work, though. Is it because one of these files is an ecf? Thanks The Shell command is passing the second program's name as an input to the first program. Use two Shell commands, one for each program. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Mr. Smiley"...

freeze 2 panes help please
hi all...... i hope this is an easy ? I want to make a worsheet kind of like a framed web page, like this -------------------------------------------------||----------------------------| | title || date | |____________________________|| | | || | | info 1 || | | ...

VBA code not running
Hello all, I imported a VBA modifier package onto a GP 10 (10.00.0903) client installation and the vba code won't run. It runs just fine on a different client installation (same GP version). I put a break in the vba code and when I do the activity to make it run in GP the code never breaks. I can clearly see the vba code in the editor window so I know it's there. It just doesn't run. I checked that Modifier is registered under Options. I'm in the dark. Any leads? Maatthias, Try CustomerSource/Partnersource and search for article 929612. It talks about adding pe...

I need to know how to insert my information, time and tides in ex.
I am trying to create a chart by inputing the high and low tides of any given day and the time in which they occured on the day but I don't know how to input that information into excel to get the desired result. Help! I have the moon cycle for those days as well but as well I am unsure of how to begin. This is a home school project for primary students. Jan, Try setting up your data as follows (note: this example assumes two days of data at three hour intervals but you can customize it to your own specs). First, place the "Day" header in cell A1, the "Time" he...

Need to EDC Settle an already Settled batch
Our mgr. mis-entered our new credit card merchant info in the admin>edc area. We haven't been getting any errors on cc transactions or settlements but today we noticed the money is not in the bank and our processor says we need to re-settle the batch...is there any way to do this...can I unsettle a batch then settle it again? Not getting a clear answer from MS support. Thanks in advance for your help. I'm running Version 1.3 "David" wrote: > Our mgr. mis-entered our new credit card merchant info in the admin>edc > area. We haven't been getting a...

Excel MSP linking
Dear sir , I have prepared one excel sheet.Some cells have linkings from MSP.But the Problem is that the links dont get updated when I reopen the book.I have even changed the settings in excel options,but it doesn't work. Can MSP fields like start or finish be linked to excel and will the fields get updated as per the MSP sheets? -- Amey B. Vidvans, Planning Engineer Vidvans_amey@rediffmail.com ...

HELP
Hi All, I'm currently running Microsoft Excell 2003 on Workstations running WindowsXP. My network is both Windows 2000 and 2003. Suddenly my file names are being changed from the names which were given and are not showing up as numbers. Does anyone have any suggestion why this may be so. Thanks Nik Did you mean "...are NOW showing as numbers." instead of "...are not showing as numbers." If yes, then maybe... When excel saves the file, it saves it as a temporary file with a funny name (8 characters--no extension). If the save is successful, xl will delete t...

multiple pivot tables in a book by using VBA
Hi, The data in the different sheets of my workbook have all the same headings. To ease the analysis of the data, I would like to design a code that creates the pivot table for the 'Active Worksheet' respecitively. I evolved a macro for one sample but it is not possible to assign it to the other sheets due to error 1004. As I am not very familiar with VBA I do not know what to change to get the working code for all sheets in the workbook and for all different workbooks accordingly. Here the code: tablename = Right(ActiveSheet.Name, 1) Cells.Select ActiveWorkbook.PivotCaches.Add...

Help please #10
thank you it works eventhough Ihave to customize it to my specs. Concerning the possibilities my number list is 6 to 9 digits with almost no chance of repeating so it should a little easier. But i can run it overnight. much appreciatted!! -- mik00725 ------------------------------------------------------------------------ mik00725's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16169 View this thread: http://www.excelforum.com/showthread.php?threadid=275961 ...

Writing Macros without using VBA
Recently installed Office XP. I've been hit with an emergency project that requires some macros. When I try to record the macro, I'm dumped immediately into the VBA. Problem, I'm VBA dumb and just want to record the macro in the worksheet by doing the steps. Is there a method for turning off the VBA so I can record directly in the worksheet? vba is the macro language that would be used when recording. This is not lotus. Try it. It will do what you want... -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Mark Smith" <mhsmith@sbcglobal.ne...

Center form by VBA
Hello All- I have a form that acts as a splash screen, gives the title of the application and a graphic while items load in the background. I am trying to use the Docmd.MoveSize function to center this form in the application window. When the application starts, this form will be the only one open for several seconds so it will be against the gray background of the Access Window. I thought I could use the following code, but it seems to be trying to center the database tray. intScreenHeight = Me.InsideHeight intScreenWidth = Me.InsideWidth intScreenHeightHalf = i...

VBA Help
I need to have expand button clicked using VBA when a window with a scrolling window opened. For example, on GL Detail Inquiry window, you can see the details of the journal entry in two lines. I would like to open up this window (GL Detail Inquiry) so that user can see both lines in the scrolling window (Scrolling Window for GL inquiry window) without clicking on the Expand button. Please help. Let me know if you have any questions. ...