Autofilter within macros

[James Arundell]

Hi,

In order to format a Gantt chart I currently turn on autofilter, filte
the results then set the bar colour of all returned results to
particular colour. I then repeat filtering for a different type and se
the bar colour of the next set of returned results to a differen
colour. I repeat this several more times...

I'm hoping to automate this by way of a macro however if I record
macro whilst carrying this task out the macro does not work. Fro
looking at the VB code it appears this is because the autofilter valu
is not recorded within the macro when selected.

Can anybody give me any advice as to a way round this?

Many Thanks

James Arundel

 

[Rod Gill]

Hi,

For VBA macro posts, please use the project.programming news group.

To answer your question, create a filter to filter what you want (via
Project, Filtered for, more filters) then get the code to set Flag1 to Yes
for all visible tasks using the ActiveSelection object. Add to bar styles a
new format for bars where Flag1 is Yes and your code looks like:

Sub test()
Dim Tsk As Task

FilterApply "All Tasks"
For Each Tsk In ActiveProject.Tasks
' Test for blank task row
If Not (Tsk Is Nothing) Then
Tsk.Flag1 = False
End If
Next Tsk

FilterApply "Milestones"
SelectAll
If Not (ActiveSelection.Tasks Is Nothing) Then
For Each Tsk In ActiveSelection.Tasks
' Test for blank task row
If Not (Tsk Is Nothing) Then
Tsk.Flag1 = True
End If
Next Tsk
End If
SelectBeginning

End Sub


The bars will automatically format because of the new bar style (Format, Bar
Style to create it).
--

Rod Gill
Project MVP

NEW!! Project VBA Book, for details visit: http://www.projectvbabook.com