quicker more efficient formatting macro needed
in [Project]
|
From: Elvis on 11 Mar 2010 13:40 I have a decent size schedule that gets statused each week with percent work complete status, actuals starts, actual finishes and new ECD for start and finish. Toss in the fact most items are networked and may need to be modified for 'work arounds' this meeting is very time consuming and a large portion is spent watching me make the entries. to cut down on time I have created my own 'que sheet' for holding the updates submitted from the team. Instead of having them read off the updates they've made to a copy of theschedule, they just send it to me. I copy the project fields they will be editing and then pasted them into a View created with Dur1, start1, fin1, start2, fin2, start3, fin3 and a number field to hold the percent complete. I then run a macro to compare the deltas from their data to what I have in the standard project fields and highlight those changes. we just walk through and review the changes and afterwards when they are all agreed to, I on my one go through and make the changes. trouble is: I have to access each cell currently in my code to change the format and that takes serious time. In excel I can do that WITHOUT accessing a particular field but do not know how to assign the format in Project. any help speeding this up is appreciated. I'll post the code in another string Thanks so much for any ideas.
From: Elvis on 11 Mar 2010 13:55 here is what I have now except there are multiple ifs for eacf field evaluated: Sub Status_Evaluation() Dim Proj As Project Dim T As Task For Each T In ActiveProject.Tasks variance = "" If ActiveCell.Task.Start1 <> ActiveCell.Task.Start Then SelectTaskField Row:=0, Column:="start1" FontEx Color:=7, CellColor:=5 variance = "S/" End If Application.SelectCellDown Next T End Sub "Elvis" wrote: > I have a decent size schedule that gets statused each week with percent work > complete status, actuals starts, actual finishes and new ECD for start and > finish. Toss in the fact most items are networked and may need to be > modified for 'work arounds' this meeting is very time consuming and a large > portion is spent watching me make the entries. > > to cut down on time I have created my own 'que sheet' for holding the > updates submitted from the team. Instead of having them read off the updates > they've made to a copy of theschedule, they just send it to me. I copy the > project fields they will be editing and then pasted them into a View created > with Dur1, start1, fin1, start2, fin2, start3, fin3 and a number field to > hold the percent complete. I then run a macro to compare the deltas from > their data to what I have in the standard project fields and highlight those > changes. we just walk through and review the changes and afterwards when > they are all agreed to, I on my one go through and make the changes. > > trouble is: I have to access each cell currently in my code to change the > format and that takes serious time. In excel I can do that WITHOUT accessing > a particular field but do not know how to assign the format in Project. any > help speeding this up is appreciated. > > I'll post the code in another string > > Thanks so much for any ideas. >
From: Rod Gill on 11 Mar 2010 18:22 Try the code below, but you have questions to answer: Sub Status_Evaluation() Dim T As Task For Each T In ActiveProject.Tasks If not T is nothing then 'Handle empty rows If T.Start1 <> T.Start Then T.Marked=True T.Text1 = "S/" Else T.Marked=false T.Text1= "" End If End if Next T End Sub Select Format, text Styles and format the Marked style as you want it. This will automatically format all Tasks with Marked flag set to true. What is Variance? It isn't a field, so I've stored the string in Text1 -- Rod Gill Microsoft MVP for Project - http://www.project-systems.co.nz Author of the only book on Project VBA, see: http://www.projectvbabook.com "Elvis" <Elvis(a)discussions.microsoft.com> wrote in message news:CED9D422-01F5-49B0-BC91-3AA0C459C300(a)microsoft.com... > here is what I have now except there are multiple ifs for eacf field > evaluated: > > Sub Status_Evaluation() > > Dim Proj As Project > Dim T As Task > > For Each T In ActiveProject.Tasks > > variance = "" > > If ActiveCell.Task.Start1 <> ActiveCell.Task.Start Then > SelectTaskField Row:=0, Column:="start1" > FontEx Color:=7, CellColor:=5 > variance = "S/" > End If > > Application.SelectCellDown > > Next T > > End Sub > > > > > > "Elvis" wrote: > >> I have a decent size schedule that gets statused each week with percent >> work >> complete status, actuals starts, actual finishes and new ECD for start >> and >> finish. Toss in the fact most items are networked and may need to be >> modified for 'work arounds' this meeting is very time consuming and a >> large >> portion is spent watching me make the entries. >> >> to cut down on time I have created my own 'que sheet' for holding the >> updates submitted from the team. Instead of having them read off the >> updates >> they've made to a copy of theschedule, they just send it to me. I copy >> the >> project fields they will be editing and then pasted them into a View >> created >> with Dur1, start1, fin1, start2, fin2, start3, fin3 and a number field to >> hold the percent complete. I then run a macro to compare the deltas from >> their data to what I have in the standard project fields and highlight >> those >> changes. we just walk through and review the changes and afterwards >> when >> they are all agreed to, I on my one go through and make the changes. >> >> trouble is: I have to access each cell currently in my code to change >> the >> format and that takes serious time. In excel I can do that WITHOUT >> accessing >> a particular field but do not know how to assign the format in Project. >> any >> help speeding this up is appreciated. >> >> I'll post the code in another string >> >> Thanks so much for any ideas. >> > > __________ Information from ESET Smart Security, version of virus > signature database 4937 (20100311) __________ > > The message was checked by ESET Smart Security. > > http://www.eset.com > > > __________ Information from ESET Smart Security, version of virus signature database 4937 (20100311) __________ The message was checked by ESET Smart Security. http://www.eset.com
From: John on 12 Mar 2010 10:12 In article <CED9D422-01F5-49B0-BC91-3AA0C459C300(a)microsoft.com>, Elvis <Elvis(a)discussions.microsoft.com> wrote: > here is what I have now except there are multiple ifs for eacf field > evaluated: > > Sub Status_Evaluation() > > Dim Proj As Project > Dim T As Task > > For Each T In ActiveProject.Tasks > > variance = "" > > If ActiveCell.Task.Start1 <> ActiveCell.Task.Start Then > SelectTaskField Row:=0, Column:="start1" > FontEx Color:=7, CellColor:=5 > variance = "S/" > End If > > Application.SelectCellDown > > Next T > > End Sub Elvis, Rod provided a very simple solution that highlights the full task line for the changed rows. Hopefully that gives you something you can use. However if you really want to only highlight those field cells that are changed then the required code is a little more complex. Unfortunately Project does not have the VBA text formatting capabilities found in Excel. Generally text formatting in Project must be done using foreground processing and that takes longer. Your simple macro code is one way to do it but is very slow. There are ways to speed up the process considerably. Here is a pseudo-code structure you might want to pursue. 1. As you enter the change data in the spare fields, also set a flag field 2. Filter on the flag field so the view displays only task rows with changes 3. Select the first row and color format those fields with changes 4. Use Fill Down to color all change task rows 5. Apply the "All Tasks" filter Hope this helps. John Project MVP > > > > > > "Elvis" wrote: > > > I have a decent size schedule that gets statused each week with percent > > work > > complete status, actuals starts, actual finishes and new ECD for start and > > finish. Toss in the fact most items are networked and may need to be > > modified for 'work arounds' this meeting is very time consuming and a large > > portion is spent watching me make the entries. > > > > to cut down on time I have created my own 'que sheet' for holding the > > updates submitted from the team. Instead of having them read off the > > updates > > they've made to a copy of theschedule, they just send it to me. I copy the > > project fields they will be editing and then pasted them into a View > > created > > with Dur1, start1, fin1, start2, fin2, start3, fin3 and a number field to > > hold the percent complete. I then run a macro to compare the deltas from > > their data to what I have in the standard project fields and highlight > > those > > changes. we just walk through and review the changes and afterwards when > > they are all agreed to, I on my one go through and make the changes. > > > > trouble is: I have to access each cell currently in my code to change the > > format and that takes serious time. In excel I can do that WITHOUT > > accessing > > a particular field but do not know how to assign the format in Project. > > any > > help speeding this up is appreciated. > > > > I'll post the code in another string > > > > Thanks so much for any ideas. > >
From: Elvis on 18 Mar 2010 11:20 Thanks Rod and John I really need only the changed fields. My "variance" was an idea i had to identify changed fields (like your suggestion to flag changed tasks). knowing that the there is no direct way to format I will try your suggestion John. Thanks "John" wrote: > In article <CED9D422-01F5-49B0-BC91-3AA0C459C300(a)microsoft.com>, > Elvis <Elvis(a)discussions.microsoft.com> wrote: > > > here is what I have now except there are multiple ifs for eacf field > > evaluated: > > > > Sub Status_Evaluation() > > > > Dim Proj As Project > > Dim T As Task > > > > For Each T In ActiveProject.Tasks > > > > variance = "" > > > > If ActiveCell.Task.Start1 <> ActiveCell.Task.Start Then > > SelectTaskField Row:=0, Column:="start1" > > FontEx Color:=7, CellColor:=5 > > variance = "S/" > > End If > > > > Application.SelectCellDown > > > > Next T > > > > End Sub > > Elvis, > Rod provided a very simple solution that highlights the full task line > for the changed rows. Hopefully that gives you something you can use. > However if you really want to only highlight those field cells that are > changed then the required code is a little more complex. > > Unfortunately Project does not have the VBA text formatting capabilities > found in Excel. Generally text formatting in Project must be done using > foreground processing and that takes longer. Your simple macro code is > one way to do it but is very slow. There are ways to speed up the > process considerably. Here is a pseudo-code structure you might want to > pursue. > 1. As you enter the change data in the spare fields, also set a flag > field > 2. Filter on the flag field so the view displays only task rows with > changes > 3. Select the first row and color format those fields with changes > 4. Use Fill Down to color all change task rows > 5. Apply the "All Tasks" filter > > Hope this helps. > > John > Project MVP > > > > > > > > > > > > "Elvis" wrote: > > > > > I have a decent size schedule that gets statused each week with percent > > > work > > > complete status, actuals starts, actual finishes and new ECD for start and > > > finish. Toss in the fact most items are networked and may need to be > > > modified for 'work arounds' this meeting is very time consuming and a large > > > portion is spent watching me make the entries. > > > > > > to cut down on time I have created my own 'que sheet' for holding the > > > updates submitted from the team. Instead of having them read off the > > > updates > > > they've made to a copy of theschedule, they just send it to me. I copy the > > > project fields they will be editing and then pasted them into a View > > > created > > > with Dur1, start1, fin1, start2, fin2, start3, fin3 and a number field to > > > hold the percent complete. I then run a macro to compare the deltas from > > > their data to what I have in the standard project fields and highlight > > > those > > > changes. we just walk through and review the changes and afterwards when > > > they are all agreed to, I on my one go through and make the changes. > > > > > > trouble is: I have to access each cell currently in my code to change the > > > format and that takes serious time. In excel I can do that WITHOUT > > > accessing > > > a particular field but do not know how to assign the format in Project. > > > any > > > help speeding this up is appreciated. > > > > > > I'll post the code in another string > > > > > > Thanks so much for any ideas. > > > > . >
|
Next
|
Last
Pages: 1 2 Prev: seeking more efficient formatting macro Next: Set MS Project so it opens on last date saved |