VBA, Getting a drop down resource list when filtering by resource and date range

Billy Shortstaf
#1 / 3

Getting a drop down resource list when filtering by resource and date range

Hi there

When you filter (show project by:) by resource in MS Project you are
presented with a drop down list of all the resources on the project.

Why, when you filter by 'resource in date range', are you not presented with
a drop down list of resources, or a calendar for the dates, forcing you to
enter the information manually, and increasing the room for error?

Following on from this, is there a way in which using VB you can make this
view option allow you to select the resource from a list and the dates from
calendars?

Thanks

Billy

Tue, 25 May 2004 01:40:42 GMT

Venkata Krishn
#2 / 3

Getting a drop down resource list when filtering by resource and date range

Billy

You need to use a userForm and filter methods. I will show you how to create one for filtering for a resource range in resource usage view. Same principle can be used for whatever filter you want to create

This example does the same thing as in built "Rsource Range.... " filter in Resource Usage view, but user will be able to select from the list of resources in the combo box.

Create UserForm1 and place 2 combo boxes - ComboBox1, ComboBox2 on it for selecting resource range - from and to. Also place commandButton1. The combo boxes will have 2 columns each one for showing id and one for name, but the controlling value will be the id.

'Code in UserForm1
Option Base 1

Private Sub UserForm_Activate()
Dim R As Resource
Dim Arr() As String
Dim i As Integer

Me.ComboBox1.ColumnCount = 2
Me.ComboBox1.BoundColumn = 1
Me.ComboBox1.Width = 170
Me.ComboBox1.ColumnWidths = "20pt;150pt"

Me.ComboBox2.ColumnCount = 2
Me.ComboBox2.BoundColumn = 1
Me.ComboBox2.Width = 170
Me.ComboBox2.ColumnWidths = "20pt;150pt"

ReDim Arr(ActiveProject.Resources.Count, 2)
For Each R In ActiveProject.Resources
If Not R Is Nothing Then
i = i + 1
Arr(i, 1) = R.ID
Arr(i, 2) = R.Name
End If

Next
Me.ComboBox1.List() = Arr
Me.ComboBox2.List() = Arr
End Sub

Private Sub CommandButton1_Click()
Dim ResIDFrom As Integer
Dim ResIDTo As Integer
ResIDFrom = Me.ComboBox1.Value
ResIDTo = Me.ComboBox2.Value

FilterEdit Name:="ResRange", TaskFilter:=False, Create:=True, OverwriteExisting:=True, FieldName:="ID", Test:="is within", Value:=ResIDFrom & "," & ResIDTo, ShowInMenu:=False, ShowSummaryTasks:=False
FilterEdit Name:="ResRange", TaskFilter:=False, FieldName:="", NewFieldName:="Assignment", Test:="equals", Value:="No", Operation:="And", ShowSummaryTasks:=False
FilterApply Name:="ResRange"
End Sub

'Code in Module1
Public Sub ResRangeFilter() 'User needs to run this macro
UserForm1.Show
End Sub

For Data range you can use 2 date and time picker controls instead of combo boxes to show the calendar to pick date.

Regards
Venkata Krishna
MCSD

Quote:

> Hi there

> When you filter (show project by:) by resource in MS Project you are
> presented with a drop down list of all the resources on the project.

> Why, when you filter by 'resource in date range', are you not presented with
> a drop down list of resources, or a calendar for the dates, forcing you to
> enter the information manually, and increasing the room for error?

> Following on from this, is there a way in which using VB you can make this
> view option allow you to select the resource from a list and the dates from
> calendars?

> Thanks

> Billy

Wed, 26 May 2004 13:05:35 GMT

Billy Shortstaf
#3 / 3

Getting a drop down resource list when filtering by resource and date range

Thanks very much for this - I'm giving it a try at the moment.

Your help is appreciated.

Billy S.

Billy