Trigger Macro on Project Status Drop Down

Hi,

I would like to change the value of the "Publish "columm based on when a 
user makes a choice for the "Project Status." 

For example, when a user selects "In Progress" as the Project Status I would 
like to set all tasks where percent complete does not equal 100% to Publish 
equals "Yes."

I'm assuming a VBA macro is the way to achieve this functionality. Could 
someone point me in the right direction?

Many thanks,
Carl
0
Utf
1/6/2010 2:06:02 AM
project.server 1346 articles. 1 followers. Follow

7 Replies
1486 Views

Similar Articles

[PageSpeed] 2

There is nothing in VBA which would trigger off changing the project status 
through a dropdown, but I''d probably just put a button on the toolbar which 
changes the status for the project and sets the publish for all the tasks.

For how to run through all the tasks, take a look at the postings on my 
blog:

http://zo-d.com/blog/archives/programming.html

-Jack

"Mr. Entropy" <Mr. Entropy@discussions.microsoft.com> wrote in message 
news:6D507656-3409-4005-A9B3-F1AC7C4208C4@microsoft.com...
> Hi,
>
> I would like to change the value of the "Publish "columm based on when a
> user makes a choice for the "Project Status."
>
> For example, when a user selects "In Progress" as the Project Status I 
> would
> like to set all tasks where percent complete does not equal 100% to 
> Publish
> equals "Yes."
>
> I'm assuming a VBA macro is the way to achieve this functionality. Could
> someone point me in the right direction?
>
> Many thanks,
> Carl 

0
Jack
1/6/2010 3:16:16 AM
Thank you very much, Jack! Your website looks like a great resource. I'll 
start reading.

"Jack Dahlgren MVP" wrote:

> There is nothing in VBA which would trigger off changing the project status 
> through a dropdown, but I''d probably just put a button on the toolbar which 
> changes the status for the project and sets the publish for all the tasks.
> 
> For how to run through all the tasks, take a look at the postings on my 
> blog:
> 
> http://zo-d.com/blog/archives/programming.html
> 
> -Jack
> 
> "Mr. Entropy" <Mr. Entropy@discussions.microsoft.com> wrote in message 
> news:6D507656-3409-4005-A9B3-F1AC7C4208C4@microsoft.com...
> > Hi,
> >
> > I would like to change the value of the "Publish "columm based on when a
> > user makes a choice for the "Project Status."
> >
> > For example, when a user selects "In Progress" as the Project Status I 
> > would
> > like to set all tasks where percent complete does not equal 100% to 
> > Publish
> > equals "Yes."
> >
> > I'm assuming a VBA macro is the way to achieve this functionality. Could
> > someone point me in the right direction?
> >
> > Many thanks,
> > Carl 
> 
0
Utf
1/6/2010 7:31:01 PM
In addition to Jack's posting (and excellent site), I published some VBA on 
changing the task publish field where %comp = 100 - 
http://appleparkltd.spaces.live.com/ 
-- 
Thanks,  Ben.

Microsoft Most Valuable Professional
http://appleparkltd.spaces.live.com/



"Mr. Entropy" wrote:

> Thank you very much, Jack! Your website looks like a great resource. I'll 
> start reading.
> 
> "Jack Dahlgren MVP" wrote:
> 
> > There is nothing in VBA which would trigger off changing the project status 
> > through a dropdown, but I''d probably just put a button on the toolbar which 
> > changes the status for the project and sets the publish for all the tasks.
> > 
> > For how to run through all the tasks, take a look at the postings on my 
> > blog:
> > 
> > http://zo-d.com/blog/archives/programming.html
> > 
> > -Jack
> > 
> > "Mr. Entropy" <Mr. Entropy@discussions.microsoft.com> wrote in message 
> > news:6D507656-3409-4005-A9B3-F1AC7C4208C4@microsoft.com...
> > > Hi,
> > >
> > > I would like to change the value of the "Publish "columm based on when a
> > > user makes a choice for the "Project Status."
> > >
> > > For example, when a user selects "In Progress" as the Project Status I 
> > > would
> > > like to set all tasks where percent complete does not equal 100% to 
> > > Publish
> > > equals "Yes."
> > >
> > > I'm assuming a VBA macro is the way to achieve this functionality. Could
> > > someone point me in the right direction?
> > >
> > > Many thanks,
> > > Carl 
> > 
0
Utf
1/6/2010 8:15:01 PM
Thank you, Ben! Very much appreciated! I will see if this solves our 
requirement.

"Ben Howard" wrote:

> In addition to Jack's posting (and excellent site), I published some VBA on 
> changing the task publish field where %comp = 100 - 
> http://appleparkltd.spaces.live.com/ 
> -- 
> Thanks,  Ben.
> 
> Microsoft Most Valuable Professional
> http://appleparkltd.spaces.live.com/
> 
> 
> 
> "Mr. Entropy" wrote:
> 
> > Thank you very much, Jack! Your website looks like a great resource. I'll 
> > start reading.
> > 
> > "Jack Dahlgren MVP" wrote:
> > 
> > > There is nothing in VBA which would trigger off changing the project status 
> > > through a dropdown, but I''d probably just put a button on the toolbar which 
> > > changes the status for the project and sets the publish for all the tasks.
> > > 
> > > For how to run through all the tasks, take a look at the postings on my 
> > > blog:
> > > 
> > > http://zo-d.com/blog/archives/programming.html
> > > 
> > > -Jack
> > > 
> > > "Mr. Entropy" <Mr. Entropy@discussions.microsoft.com> wrote in message 
> > > news:6D507656-3409-4005-A9B3-F1AC7C4208C4@microsoft.com...
> > > > Hi,
> > > >
> > > > I would like to change the value of the "Publish "columm based on when a
> > > > user makes a choice for the "Project Status."
> > > >
> > > > For example, when a user selects "In Progress" as the Project Status I 
> > > > would
> > > > like to set all tasks where percent complete does not equal 100% to 
> > > > Publish
> > > > equals "Yes."
> > > >
> > > > I'm assuming a VBA macro is the way to achieve this functionality. Could
> > > > someone point me in the right direction?
> > > >
> > > > Many thanks,
> > > > Carl 
> > > 
0
Utf
1/6/2010 10:43:01 PM
Hi Jack and Ben,

One more question for you: how do you access the "Project Status" in VBA?

Thanks,
Carl

"Jack Dahlgren MVP" wrote:

> There is nothing in VBA which would trigger off changing the project status 
> through a dropdown, but I''d probably just put a button on the toolbar which 
> changes the status for the project and sets the publish for all the tasks.
> 
> For how to run through all the tasks, take a look at the postings on my 
> blog:
> 
> http://zo-d.com/blog/archives/programming.html
> 
> -Jack
> 
> "Mr. Entropy" <Mr. Entropy@discussions.microsoft.com> wrote in message 
> news:6D507656-3409-4005-A9B3-F1AC7C4208C4@microsoft.com...
> > Hi,
> >
> > I would like to change the value of the "Publish "columm based on when a
> > user makes a choice for the "Project Status."
> >
> > For example, when a user selects "In Progress" as the Project Status I 
> > would
> > like to set all tasks where percent complete does not equal 100% to 
> > Publish
> > equals "Yes."
> >
> > I'm assuming a VBA macro is the way to achieve this functionality. Could
> > someone point me in the right direction?
> >
> > Many thanks,
> > Carl 
> 
0
Utf
1/7/2010 1:20:01 AM
Sorry for to create so many posts. I just realized "Project Status" is an 
Enterprise Custom Field. Can it still be changed using VBA?

"Mr. Entropy" wrote:

> Hi Jack and Ben,
> 
> One more question for you: how do you access the "Project Status" in VBA?
> 
> Thanks,
> Carl
> 
> "Jack Dahlgren MVP" wrote:
> 
> > There is nothing in VBA which would trigger off changing the project status 
> > through a dropdown, but I''d probably just put a button on the toolbar which 
> > changes the status for the project and sets the publish for all the tasks.
> > 
> > For how to run through all the tasks, take a look at the postings on my 
> > blog:
> > 
> > http://zo-d.com/blog/archives/programming.html
> > 
> > -Jack
> > 
> > "Mr. Entropy" <Mr. Entropy@discussions.microsoft.com> wrote in message 
> > news:6D507656-3409-4005-A9B3-F1AC7C4208C4@microsoft.com...
> > > Hi,
> > >
> > > I would like to change the value of the "Publish "columm based on when a
> > > user makes a choice for the "Project Status."
> > >
> > > For example, when a user selects "In Progress" as the Project Status I 
> > > would
> > > like to set all tasks where percent complete does not equal 100% to 
> > > Publish
> > > equals "Yes."
> > >
> > > I'm assuming a VBA macro is the way to achieve this functionality. Could
> > > someone point me in the right direction?
> > >
> > > Many thanks,
> > > Carl 
> > 
0
Utf
1/7/2010 2:20:01 AM
Using the MS Projec VBA Events, you can do ANYTHING you want...why this was not stated, I am puzzled.

Use the App_ProjectBeforeTaskChange class

Private Sub App_ProjectBeforeTaskChange(ByVal tsk As MSProject.Task, ByVal Field As PjField, ByVal NewVal As Variant, Cancel As Boolean)

    If (Field = (name of field to monitor ) ) Then
        If (InStr(NewVal, "XYZ_") = 1) Then
            ActiveCell.CellColor = pjYellow
        End If
    End If
End Sub
0
George
6/2/2010 3:51:51 PM
Reply:

Similar Artilces:

pasting of variable cell value into macro
I have written a macro that runs an autofilter which grabs cell info from a different worksheet within that workbook (an entered date). It then uses that date to autofilter. It works fine the first time, but aparently plugs that info permanently into the macro and will not work on subsequent runs. I need a macro that will grab variable info from a certain cell each time and run with that new cell value each time. Any ideas?. (& yes, I am a newbee) sorry.. Your problem is refreshing the autofilter. It is best to clear the old filter before applying the new filter. Here ...

Internationalization for formulas & macros
Hi all, I have a relative who send me some Excel spreadsheet (97, 2000 or XP that contains formulas and macros written in english. However, I have a french version of Excel. Because of this, I can' open these spreadsheets. My question is quite simple. Is there a tool or an easy way to convert all formulas & macros withi a spreadsheet from english to french? Any help will be appreciated. Thank' -- micto ----------------------------------------------------------------------- mictou's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1637 View this th...

Integrating GP with Microsoft Project Server
Hello, we have a prospect who is really interested in integrating GP 10 with their Microsoft Project Server 2007. They want to manage the projects in Project Server, and get project P&L information reporting out of GP, but only do time entry one time. We're looking at Business Portal Project Time Entry for the entry point, but can't find any information or experience in integrating the entries into Project Server. Does anyone have any ideas? -- BrianB I have no experience with these but searched the internet by using the keywords "GP Project interface". Here ...

able to record & run macro but not edit !
All Excel97 . When I open a file with an existing macro (or create a new spreadshee and simply record an entry in a cell ) and go to Tools/Macro/Macros th macro list is visible and I can run the macro but if I try to edit it get a Microsoft Visual Basic pop-up 'Unexpected error ; quitting' o if I simply try to open the VBE i get a similar pop-up with ' Out o Memory'. Same files work fine at work (Excel 97) where I edited macros n problem. I've tried uninstalling/reinstalling Excel from the office 97 dis along with data access & Office tools (and their elements) ti...

Visual basic for application project in database is corrupt
I'm running Office 2003 on Windows XP. I have a database that does not open, and gives me a message stating that Microsoft Access has detected corruption in this file. The message goes on to suggest that I run Compact and Repair Database from the Tools menu. When I do, I get the error message above. Is there any way of recovering this database? Thank you for your response. I actually tried both ways, linking and inporting, but I'm unable to gain access to the corrupted database. The only thing I have been able to do is access it through MSQuery in Excel, but that only gets me to ...

We can drag and drop between 2 buttons
Hi all we can drag and drop between 2 butons. My requirement is when user drag from a button, we will restore some info in some place, then user drop to another button. And we can get that restored info to process. It can ? Please help me. Thanks very much. Whats the problem if you are able to drag and drop? All you need to do is handle OnDrop, as you appear to be doing and do the specific work there. Perhaps I am missing something. --- Ajay "James Duy Trinh" <vietdoor@gmail.com> wrote in message news:ehzA7QgPIHA.3916@TK2MSFTNGP02.phx.gbl... > Hi all > > ...

Macro
Thanks to those who offered help but I still am not quite there with the correct solution. R.Venkataraman's code was almost correct but I don't think I explained myself correctly. In my sheet1 the user inputs numbers (1-50) in column A.(pluss loads of other data in columns B:P) what I require is the macro to ask the user to input the 'job' they want to print. So if the user inputs <9> then column A in sheet 1 is searched for the number 9 then the contents of that particular row are copied (without formula - just cell values) and the data pasted onto row A2 of sheet...

Hide worksheets if macros are disable
Hi, what I was hoping to do is if the marcros are disable when the file is opened all the worksheets are hidden except one with a message saying to enable macros in which case this sheet would be hidden if the macros were enable. TIA If macros are disabled, there's nothing you can do to change they way it opens. Maybe you can save the file with the worksheets hidden the way you want. Then have your auto_open/workbook_open unhide the ones you want to see. Eintsein_mc2 wrote: > > Hi, what I was hoping to do is if the marcros are disable when the file > is opened all the work...

Excel should allow macro on a shared workbook.
We are using excel tables all the time at my company. There are more than 20 people working on the same excel file which has thousands of columns so we use autofilter or some other macros. Shared workbook will help us alot because we will be able to change the file at the same time which is very time saving. Is there any way that macros are allowed at shared workbooks? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not ...

About Macros #2
Hi ! Even though I have deleted all macros from my workbook, I still get the pop-up saying that macros are disabled. How do I get rid of this annoying feature? Thanks for all help, David Debra Dalgleish has a great explanation with pictures on how to deal with this issue. http://www.contextures.com/xlfaqMac.html#NoMacros HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. ------------------------------------------------------------------------------...

VBA Question: Called macro stops unexpectedly
Hello, I'm new to using VBA in Excel. I'm hoping I'm missing something simple and that someone here can help me. I have one Excel workbook that has many subroutines/macros in Module 1. I have 5 worksheets in the workbook and each has navigation buttons to move from sheet to sheet. On one sheet, there are required fields that must be checked before a user can be moved to the desired sheet. The buttons call the appropriate macro, like GoTo_Report, which takes the user to the Report sheet. But, on the Project Info screen, I need all these buttons to also run the macro that chec...

Macro to find the first cell in a column with different data
Hello, I really could use some of your expert and excellent help with a problem I am having in creating a macro to perform a search in "Column A" to locate a change in the data in that column and then move one column to the right and one row down and type "1" in that cell. I really appreciate your help, and Have a Blessed Holiday Season. Perhaps this might also do it here .. Assuming data is in A1 down, of the structure as below: 1 1 1 2 2 3 3 etc Put in B2: =IF(A2<>A1,1,"") Copy down Kill the formulas in col B with an in-place: Copy > Paste spe...

can't record a new macro or insert module in editor
When i try to record a new macro it tells me it is unalbe to record. When i go into the VB editor and go to the insert menu all the items are greyed out so I can't select module and so create a new macor that way. I have changed the security off from high (tried on both medium and low). Does anybody know why I can't make new macros? Douglas Allen Douglas Unfortunately the "Unable to record" message has several potential causes. Suggest you wander through these google search results dealing with this issue. Perhaps something will do the trick. The Personal.xls file ...

Filtering data within macros
I have a spread sheet with 6 coloums of data such as name,month,job. have filters so i can see how many jobs are done in a month or how man jobs one person is doing. I would like to be able to write a macr which works out most of the combinations of filters and outputs it t a seperate sheet. There are lots of different options for each coloum tho! Is there any quick ways i can do this other than reseting th filters everytime for every combination? hope i have explained this ok....its even confusing me!! Cheers Robert -- Message posted from http://www.ExcelForum.com This may be helpful ...

macro to delete multiple rows
I have recorded, then modified, the following macro, which unprotects the sheet, aks if you are sure you want to delete the row, deletes the current row, copies down a cell in column A that numbers the row, and protects the sheet. Works fine, but if the user wants to delete not only the current row, but multiple rows below it also, the macro must be repeated. How can I accomplish deleting multiple rows, as indicated by the user? For example, rows 18 thru 20, or 29 thru 33, etc, or of course, just the current row, with the message box prompting the user for the number of rows to delete. Thanks...

Macro-Save as
I need to know how to 'default' the save file type as an .xls from a .dat within a macro. After a macro has completed changing many .dat files into excel spreadsheets to include subtotals, the file type is defaulted to .dat and when I go to save the file, I have to constantly change the file type from .dat to .xls. "Lois" wrote: > I need to know how to 'default' the save file type as an .xls from a .dat > within a macro. After a macro has completed changing many .dat files into > excel spreadsheets to include subtotals, the file type is defaulted to...

Data validation
When I use column of data as source for list of allowed values it does not give me unique values (like AutoFilter does for example). I get the same value as many times as it appers in list source. The other problem is that values in a list are not sorted as they are in AutoFilter. Looking at the questions in Excel User Goups I can see many users see this as a problem... Regards, Oscar. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button...

Using a macro question.
Can you execute a macro from inside a formula? IF(A1="Y",execute.macro,"") Thanks Adam, not with a formula, but you can use a worksheet change event like this, put in sheet code Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count > 1 Then Exit Sub If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then 'put your code here End If End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Usin...

ASP.Net 2005 Editable Drop down server control
Could any one share me open source code to customize Asp.net 2005 drop down control as an editable drop down control. This is an urgent requirement. Thanks in advance Check out the code at this link: http://www.codeproject.com/KB/aspnet/EditableDropdown.aspx - Dave "JPrasad." <JPrasad@discussions.microsoft.com> wrote in message news:50FF9826-BB9B-4E99-9D73-745274229164@microsoft.com... > Could any one share me open source code to customize Asp.net 2005 drop > down > control as an editable drop down control. This is an urgent requirement. > > Thanks in a...

How do I run a macro when a value occurs in a cell
I want to run a macro when a value appears in a cell. How can this be accomplished? Hi! This is a trivial example but you can build on it: Private Sub Worksheet_Change(ByVal Target As Range) If Range("D3") = 36 Then Range("D3").Interior.ColorIndex = 6 Else Range("D3").Interior.ColorIndex = 2 End If End Sub Put =A3*3 in D3 Try values such as 12, 10 in A3 Al -- Alf ----------------------------------------------------------------------- AlfD's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=478 View this thread: http://www.excelforu...

Hyperlink to open .dot with a macro
I have a word .dot that has a simple macro when opened, but when I try to open the .dot from a hyperlink the macro doesn't autostart. Any ideas? I opened word and created a new test2.dot template file. I put this code under the ThisDocument module: Option Explicit Private Sub Document_New() MsgBox "hi from new" End Sub Private Sub Document_Open() MsgBox "hi from open" End Sub I put a hyperlink in a cell in an excel worksheet that pointed to that test2.dot file. The Document_Open() event fired--but the actual template (test2.dot) opened--a new document bas...

Project settings for final release
Could I get some advice on exactly which setting I should change for my Release build in Project Settings? Up until now, I have enabled browsing and debugging, but at this final stage, I would like to strip out everything possible. What should Optimizations be set to? Should I set Debug Info to none? What else should I consider? Typically, when you create a new project the settingsf or debug and release are created automatically and I've found the defaults to be pretty good. I usually just add the non-debug versions of any libraries I've added. Typcially Optimization is dis...

FRx DAX Drop down not available
Hi, We have multiple companies with several different DAX tables set up. When I go into my row format I have to key in the name of the rate table in column D because the drop diown does not present the options. When I click on the drop down I get "Unable to find any Historic Rate IDs. Please run Rate Maintenance and create Historic Rate IDs for use in your report". ideas?? -- Doug Wilson Flagstone RE Doug, are you using rate tables from GP or from FRx? Do you have the rate table source selection correct? Under Report Options in your report catalog, check the Currency Trans...

Can I create a report from this without a macro?
(XL2007) My data is presented in 5-row groups. Cols A, B, and C have my identifiers; col AU has a text value I need to include. Headers in row 9; cells in cols A, B and C are merged in groups of five (A10:A14 merged, A15:19 merged, etc, and same for B and C); in the Name Box, though, data in the A10:A14 merged group shows up as A10. Col A is the major group, col B is the sub-group, and col C is the ID number of the sub-group. What I would like is something like a pivot table. I tried that, though, and XL told me I didn't have a valid range, or valid headers, or something like that. ...

Help: Macro that runs various if statements, inserting rows etc
I am a newcomer to Macros and excel programming and was hoping someone could assist / provide the code to do the following: I have a +50,000 line spreadsheet, that needs to be formatted. Given the size I would like to automate this. There are 3 key formatting changes required and detailed below: n = 1 1. If Cell A(n) not equal Cell A(n+1) then insert a new row below A(n), make Cell F(insertedrow)=1, make Cell G(insertedrow)=A(n+1), make Cell H(insertedrow)=B(n+1) 2. If Cell C(n) not equal Cell C(n+1) then insert a new row below C(n), make Cell F(insertedrow)=2, make Cell G(inserte...