locked
User form for Resource Assignment RRS feed

  • Question

  • I may be way over my head here but I want to get this to work and just need a foot-hold to get started.

    I will breakdown this macro to see if I am being to over the top.

    1. The Userform will appear with a textbox and a cmdbutton next to it, underneath will be a combobox

    2. When users enter in #'s into the textbox and click the cmb button, it will filter out the resources based on the user entry.

    3. The combo-box will then pull the filtered list into itself allowing the user to see only the resources they filtered for

    4. Next to the combo box will be a button "Add to task" that will add the currently selected resource to the task (I think I figured this one out)

    My reasoning is I have a resource pool with over 2000 people and using the standard drop down menu within project is very tedious and takes a bit to load each time.

    My biggest challenge right now is to have the cmd button filter the resources and have them pulled in, not sure how to keep the resources filtered as I pull them into the combo box or if this is even possible.

    Any information to get me going would be greatly appreciated. 

    Wednesday, July 18, 2012 5:48 PM

Answers

  • Jake683

    I still think the Assign Resources tool may be your better bet. 

    The code below is a for a simple dialog box using VBA that uses the select to pull in the list of filtered resources.  The form initializes the combo-box to all resources in the actitve project.  When the button is clicked it invokes the out-of-box filter to show all material resources and fills the combo box with only those.   In your case you will need a filter with a parameter, you may be able to build a temporary filter programmatically and apply it, this will allow you to take your text box parameter and apply a filter around that.  To that end use the Application.FilterEdit method.

    Hope this helps


    Private Sub cmdGet_Click()
        Dim p As Project
       
        Set p = Application.ActiveProject
        Application.ViewApply "Resource Sheet"
        Application.SelectAll
       
        Application.FilterApply "Resources - Material"
           
        Me.cbResNames.Clear
        If Application.ActiveSelection <> 0 Then
            For Each r In Application.ActiveSelection.Resources
                Me.cbResNames.AddItem r.Name
            Next r
        End If
       
    End Sub

    Private Sub UserForm_Activate()
        Dim p As Project
       
        Set p = Application.ActiveProject
        Application.ViewApply "Resource Sheet"
        Application.SelectAll
       
        If Application.ActiveSelection <> 0 Then
            For Each r In Application.ActiveSelection.Resources
                Me.cbResNames.AddItem r.Name
            Next r
        End If
       
    End Sub

     

    • Marked as answer by Jake683 Monday, July 23, 2012 2:46 PM
    Friday, July 20, 2012 7:02 PM
  • Jake683

    From a coded approach standpoint after the filter is applied then programatically select the resources and loop throught the selection. 

    Pseudocode:

    Remove all current items from the combobox
    Retain current project view: Store name of current view (have to handle splits)
    Apply a Resource View: "Resource Sheet" for example
    Apply the Filter: Apply custom filter with your selection criteria
    Select the resources: Application.SelectAll
    Loop through Application.ActiveSlection.Resources and retireve resource names (Note be sure to handle empty set if ActiveSelection = 0)
    Add each resource to the combobox AddItem method
    Remove Filter, Restore orignial View

    Hope this helps

    • Marked as answer by Jake683 Thursday, July 19, 2012 3:18 PM
    Thursday, July 19, 2012 3:06 PM
  • With Regard to Item 1:  It sound line you are using a filter that you can type in a selected OBS value where the filter criteria something like: (OBS contans "Value"?).  Why the filter is being invoked several times is probably a function of the Event you are using apply the fiter. 

    I took a stab at updating the code I provided in a prior post that may help.  I added a text box into which I would type in values to be used in the creation of the filter.  I did not seem to receive an error when calling the Filter edit without the OverwriteExisting set to True which was odd.  Setting it to true eliminates the need to check for it though. 

    Private Sub cmdGet_Click()
        Dim p As Project
       
        Set p = Application.ActiveProject
        Application.ViewApply "Resource Sheet"
       
        Application.FilterEdit Name:="ABC", TaskFilter:=False, Create:=True, OverwriteExisting:=True, FieldName:="OBS", test:="contains", Value:=TextBox1.Value
        Application.FilterApply "ABC"
       
        Application.SelectAll
           
        Me.cbResNames.Clear
        If Application.ActiveSelection <> 0 Then
            For Each r In Application.ActiveSelection.Resources
                Me.cbResNames.AddItem r.Name
            Next r
        End If
       
    End Sub

    with regard to Item 2:  I'm not aware off hand of a property that will expand the depth of the list, there are some scrolling properties that may help.

    • Marked as answer by Jake683 Wednesday, July 25, 2012 4:51 PM
    Monday, July 23, 2012 6:42 PM

All replies

  • Jake683

    My first inclination after reviewing your requirements is to use the "Assign Resources" dialog (2010 - Resource Tab, Assignments Group, 2007 - Tools menu).  The dialog allows you to select a filter (draws on out-of-box as well as custom resource filters) to present a short list of relevant resources.   You may have to expand the "Resource list Options" to see the filters.  Outside of that, my though is any code developed for a Custom form would essentially do the same thing.

    Hope this helps

    • Proposed as answer by Rod Gill Thursday, July 19, 2012 5:21 AM
    Wednesday, July 18, 2012 6:15 PM
  • Alright I have gotten the list to filter out but how can I have the combo box pull in these results? And just the filtered results. I have gotten it to pull in all my resources but that would be the opposite of my goal. 
    Thursday, July 19, 2012 2:41 PM
  • Jake683

    From a coded approach standpoint after the filter is applied then programatically select the resources and loop throught the selection. 

    Pseudocode:

    Remove all current items from the combobox
    Retain current project view: Store name of current view (have to handle splits)
    Apply a Resource View: "Resource Sheet" for example
    Apply the Filter: Apply custom filter with your selection criteria
    Select the resources: Application.SelectAll
    Loop through Application.ActiveSlection.Resources and retireve resource names (Note be sure to handle empty set if ActiveSelection = 0)
    Add each resource to the combobox AddItem method
    Remove Filter, Restore orignial View

    Hope this helps

    • Marked as answer by Jake683 Thursday, July 19, 2012 3:18 PM
    Thursday, July 19, 2012 3:06 PM
  • Thank you this is a huge help, I was having trouble finding starting points. I will work out some code and post it later today or tomorrow if I have errors. Thank you for the help!
    Thursday, July 19, 2012 3:18 PM
  • How do I automate the filling of the combobox with the resource names? I can't seem to figure it out, would it have to be done manually or is there a trick to having it just automatically pull all the names, and then use the filter to display only the ones i want?
    Friday, July 20, 2012 4:08 PM
  • Jake683

    Are you using VBA, VB .net or C# .net to create this Form?

    Friday, July 20, 2012 6:25 PM
  • VBA

    I just can't figure out the objects to pull in the filtered list, or if I have to filter it afterwards..Pretty sure I went over my head with this one. 


    Friday, July 20, 2012 6:47 PM
  • Jake683

    I still think the Assign Resources tool may be your better bet. 

    The code below is a for a simple dialog box using VBA that uses the select to pull in the list of filtered resources.  The form initializes the combo-box to all resources in the actitve project.  When the button is clicked it invokes the out-of-box filter to show all material resources and fills the combo box with only those.   In your case you will need a filter with a parameter, you may be able to build a temporary filter programmatically and apply it, this will allow you to take your text box parameter and apply a filter around that.  To that end use the Application.FilterEdit method.

    Hope this helps


    Private Sub cmdGet_Click()
        Dim p As Project
       
        Set p = Application.ActiveProject
        Application.ViewApply "Resource Sheet"
        Application.SelectAll
       
        Application.FilterApply "Resources - Material"
           
        Me.cbResNames.Clear
        If Application.ActiveSelection <> 0 Then
            For Each r In Application.ActiveSelection.Resources
                Me.cbResNames.AddItem r.Name
            Next r
        End If
       
    End Sub

    Private Sub UserForm_Activate()
        Dim p As Project
       
        Set p = Application.ActiveProject
        Application.ViewApply "Resource Sheet"
        Application.SelectAll
       
        If Application.ActiveSelection <> 0 Then
            For Each r In Application.ActiveSelection.Resources
                Me.cbResNames.AddItem r.Name
            Next r
        End If
       
    End Sub

     

    • Marked as answer by Jake683 Monday, July 23, 2012 2:46 PM
    Friday, July 20, 2012 7:02 PM
  • Thank you so much for your effort. This works well I will continue to tweak with it and see if it can do what I need, I thank you for your help and time.
    Monday, July 23, 2012 2:20 PM
  • Happy to be of assistance
    Monday, July 23, 2012 2:29 PM
  • Having trouble writing out this filteredit method here is what mine looks like.

    Dim Resfilter as Variant

    Dim Found as Boolean

    'Look for a filter to be changed'

    For Each ResFilter in Activeproject.Resfilterlist

    If Resfilter = "OBS" then

    Found = True

    Exit For

    End If

    Next Resfilter

    'Filter does not exist, create it'

    If not found then Application.FilterEdit Name:="OBS", Create:=True, FieldName:="OBS", Test:="contains", _ Value="textbox1.value"

    FilterApply "OBS"

    End Sub

    Would that work with just text entered into a textbox? I haven't used this filter method on resources before only tasks so I am quite confused. Sorry for the excess helplessness.

    Monday, July 23, 2012 3:01 PM
  • Three things:

    Add the "TaskFilter:=False" (Creates a Resource Filter), remove the qoutes around Textbox1.value and add colon between Value and equals sign

    Application.FilterEdit Name:="OBS", TaskFilter:=False, Create:=True, FieldName:="OBS", test:="contains", Value:=TextBox1.Value

    Hope this helps

    Monday, July 23, 2012 3:42 PM
  • Yeah sorry I knew those things I just typed in the lines instead of copying my apologies.

    Here is where I am at now though. I have gotten all the resources to pull in and such but the filter isn't working the way I want. Here is my entire Commandbutton click so if you see any glaring mistakes let me know. 

    Private Sub CommandButton1_Click()
     Dim p As Project
     Dim r As Resource
     Dim Resfilter As Variant
     Dim Found As Boolean
        Set p = Application.ActiveProject
        Application.ViewApply "Resource Sheet"
        Application.SelectAll
        'Look for a filter'  ----This part doesn't seem right to me, it just checks for the filter but I think I want it to delete it right?
        For Each Resfilter In ActiveProject.ResourceFilterList
            If Resfilter = "OBS" Then
                Found = True
                Application.FilterClear
                Exit For
            End If
        Next Resfilter
        
        'Filter does not exist, create it'
        If Not Found Then Application.FilterEdit Name:="OBS", Create:=True, Taskfilter:=False, OverwriteExisting:=True, _
        FieldName:="Group" (Ignore that its just the group, just using that as a test field), Test:="contains", Value:=TextBox1.Value
        
        
        --- After the filter applies it just changes the resource sheet, the combobox still pulls in the entire list of resources
            
        Me.cbresnames.Clear
        If Application.ActiveSelection <> 0 Then
            For Each r In Application.ActiveSelection.Resources
                Me.cbresnames.AddItem r.Name
            Next r
        End If
        
    End Sub


    • Edited by Jake683 Monday, July 23, 2012 3:49 PM
    Monday, July 23, 2012 3:46 PM
  • What you have above should work, I tried adding a simple filter for Resource name to the dialog posted earlier and only pulled in the resource that was typed in, a couple of thoughts:

    You can try to Re-select (Application.SelectAll) after the filter is applied.

    Debug to see if the list is being cleared by the .Clear method before adding them back

    Check to see that the Application.ActiveSelection.Resources.Count is what you would expect for the filtered list

    Although I do not see the command in your post above, I am assuming that you are Calling the Application.FilterApply "OBS" command.

    Hope this helps

    Monday, July 23, 2012 4:16 PM
  • Sorry I seem to be a pain but I took an alternative path when too many errors made me wish to switch.

    I built a filter to use with my combo box and instead of having the text box inside of the User-form I have the combo box click have the filter apply. Now when it clicked I can enter into the new filter box that pops up and it will filter the results and even pull them in. But now I have two issues:

    1. The filter text box continues to pop up, it will pop up once before to filter everything, then it pops again when i click anywhere on the Userform. And then a final time when I click out of my combobox. Perhaps the program is just re-running the filter over and over?

    2. Can I increase the amount of results appearing in the combobox?

    Monday, July 23, 2012 5:51 PM
  • With regard to item 1. 

    What is the nature of the Filter Text Box?  Is this a custom form, filter dialog or other?  What Event are you using to trigger the Filter Text Box?  Are you using the ? option in the filter such as in the case of the "Date Range" filter?

    With regard to item 2.

    By increasing the amount of results appearing do you mean appending to the current list?  If so, then you would not call the Clear method, however you will need to check to see if the entry is already in the list.

    Monday, July 23, 2012 6:07 PM
  • 1. The filter is just what I used to use under the assignresource dialog box. It just uses the OBS number in (Text30) and filters out for that. It actually worked wonders for filtering and adding resources but the Userform was meant as a quicker means. The filter box pops up constantly I have it attached to a button that just says Apply Filter, then the box comes up I type in an "OBS number" and it will display only the resources with that number. However the box just keeps re-appearing , it takes about 3 tries to get it to keep the filter and let me hit the drop down box on my combobox. (I have tried to have it just trigger when I click the drop-down on the combobox but that caused even more issues)

    2. I mean currently the list shows about 15 names off the bat, can I make it show 20 or 30? I have a lot of resources and this tool is meant to alleviate searching through a large list of names.

    Monday, July 23, 2012 6:11 PM
  • With Regard to Item 1:  It sound line you are using a filter that you can type in a selected OBS value where the filter criteria something like: (OBS contans "Value"?).  Why the filter is being invoked several times is probably a function of the Event you are using apply the fiter. 

    I took a stab at updating the code I provided in a prior post that may help.  I added a text box into which I would type in values to be used in the creation of the filter.  I did not seem to receive an error when calling the Filter edit without the OverwriteExisting set to True which was odd.  Setting it to true eliminates the need to check for it though. 

    Private Sub cmdGet_Click()
        Dim p As Project
       
        Set p = Application.ActiveProject
        Application.ViewApply "Resource Sheet"
       
        Application.FilterEdit Name:="ABC", TaskFilter:=False, Create:=True, OverwriteExisting:=True, FieldName:="OBS", test:="contains", Value:=TextBox1.Value
        Application.FilterApply "ABC"
       
        Application.SelectAll
           
        Me.cbResNames.Clear
        If Application.ActiveSelection <> 0 Then
            For Each r In Application.ActiveSelection.Resources
                Me.cbResNames.AddItem r.Name
            Next r
        End If
       
    End Sub

    with regard to Item 2:  I'm not aware off hand of a property that will expand the depth of the list, there are some scrolling properties that may help.

    • Marked as answer by Jake683 Wednesday, July 25, 2012 4:51 PM
    Monday, July 23, 2012 6:42 PM
  • I got it to work! My list filters and now even sorts out everything the way I want. I have one more step and I thank you for all the help you have provided me.

    Of course my final step is having a command button to allow me to add whatever selected resource to a selected task. But I do recall a function to allow multiple things to be selected? (I am used to Excel vba all this project vba has been quite confusing)

    So basically my form will filter and now I just wish to select resources to add, this has already proved to be what I have been looking for so you have been an incredible help. 

    Not sure what function to use to pull the data from the combobox and add it to the resource field on a task.

    Monday, July 23, 2012 6:47 PM
  • Somewhere along the way a task was selected by the user before this dialog was invoked.  If that task object is stored away someplace then the short answer is to use the Task.Assignments.Add method.   You may need to trim your combo list back a bit to remove any currently assigned resources as you will likely recieve an error if the user selects one of these resources.
    Monday, July 23, 2012 7:00 PM
  • I can't quite figure out what object the selected combobox entry would be? I had a few different Task.Assignments.Add lines written out, and so far it just adds either every single resource on the list. Or none at all.

    Tuesday, July 24, 2012 2:39 PM
  • The combobox returns the current selection in the .Value property (i.e. Me.cbResNames.Value).  This is a string value so you may be able to use the Len() function to determine if the string has a length greater than zero to determine if a value is selected.

    Hope this helps

    Tuesday, July 24, 2012 3:02 PM
  • Do I need to pull in the resource through the uniqueid?

    Sorry I can't get this button to work properly this is my ideology without code.

    'Focus on currently selected task'

    'Check to see if resource name exists'

    'If it doesn't, add resource from .cbresnames'

    I also don't want this to overwrite anything, just add on to the current resources. (Hopefully to eventually let them add a few resources from the user-form at one time)

    Tuesday, July 24, 2012 3:46 PM
  • Since we are pulling from the list of resources is should be safe referencing the resource explicitly by name to arrive at a Resource ID.  The sample code below does just that.

    You sould not write over any exisiting resources or data as an error will be thrown if the resource is already assigned to the task.  You may want to ensure that effort driven setting for each tasks is set in the manner which you expect the new assignment to be allocated hours.  We normally recommmend effort driven on for peformance tasks which result in a new resource with a full complement of hours as opposed to destributing remaining work over all assigned resources.

    As for the multi-select, I do not belive the combo-box has settings that will allow multi-selection but I could be wrong it is worth a look.  You may want to consider a list box which I believe does allow multi-selection and treat the selections as a subset list. 

    Private Sub cbAppRes_Click()
        Dim resname As String
       
        resname = Me.cbResNames.Value
        If Len(resname) > 0 Then    
            ActiveProject.Tasks(2).Assignments.Add ResourceID:=Application.ActiveProject.Resources(resname).ID
        End If
    End Sub

    Tuesday, July 24, 2012 7:16 PM
  • Everything works beautifully! I appreciate everything you have helped me with but I have one final question and I think this will be complete.

    Is there anyway to have it stay in the Gantt Chart Spreadsheet view while it does the filtering and name pulling? For example I want the user to be able to see the task they selected to add the resource to as opposed to the macro pulling over to the resource sheet. I tried changing the applyview method around, but it just caused errors.

    Wednesday, July 25, 2012 3:09 PM
  • And now my filter isn't working anymore...I didn't change anything, it just pulls all the names now.

    Private Sub UserForm_Activate()
     Dim p As Project
        
        Set p = Application.ActiveProject
        Application.ViewApply "Resource Sheet"
        Application.SelectAll
        
        If Application.ActiveSelection <> 0 Then
            For Each R In Application.ActiveSelection.Resources
                Me.cbresnames.AddItem R.Name
            Next R
        End If
    End Sub

    The only change I have made was I just added the filter method to my command button. (Application.Filterapply "OBS Filter". It was working fine yesterday and now it won't filter.

    Wednesday, July 25, 2012 3:28 PM
  • Glad to hear that you have a working solution, or hopefully get the one you had working again.

    As for your last post, the UserForm_Activate event depicted will bring in all resources, the Filter button should apply the filter, update the selection and populate the combo-box.

    As for returning to the Gantt View, one of the downsides to using this selection technique is that upon return to project from the user form you will be in the Resource sheet View.  Upon closing the form you can either explicitly Apply the out-of-box Gantt Chart view re-instate the active view prior to opening the dialog.  I usually will store the current view name (ActiveProject.CurrentView) in a text field as part of the Form Activate Event at I can re-instate that view when I am complete using the ViewApply method.

    Hope this Helps

    Wednesday, July 25, 2012 3:52 PM
  • Its working beautifully, I don't know how bad it will be that I am using pre-made filters but I can add them to my PM's global files. 

    Thank you for bearing with me throughout this you were an incredible help and actually taught me a bit!

    As for getting back to the gantt chart view, I added Viewapply to my add_to_task button and it works well, of course it just shows it after the resource is added but at least it saves a few seconds each time from switching back in forth. Only extra usability features I can think of is a way to change to a new task from within the form I tried this code and it worked for another macro but doesn't seem to work here.

    Private Sub GotoNewTask_Click()
    Dim varID As Variant
    Dim lngID
        varID = InputBox("Enter Task ID to Go to")
        If IsNumeric(varID) Then
            lngID = CLng(varID)
        End If

    Also I am trying to write an error handler for when the resource is already existent on the task? Would that be an assignment object? Or maybe just 

    Wednesday, July 25, 2012 4:33 PM
  • Great to hear.

    The task ID selection has it's own issues and could be a small developement in and of itself.

    Assignments are created when a resource is assigned to a task or vice-versa.  Your error handler can responond with a statement that the selected resource is already assigned to the task.

    Regards

    Wednesday, July 25, 2012 4:42 PM
  • I can't seem to get this darn thing to add resources to the task I have selected. I change the form properties so I can click on and off of the form and I want to be able to keep clicking new tasks for it to add resources to.

    I use Activeproject.Tasks(2) <--Obviously not right.Assignments.Add ResourceID....etc But I am unsure of what code to use to let me constantly click new tasks and have it add to the new one. Perhaps this is not possible?

    • Proposed as answer by Mike Agnello Monday, August 20, 2012 8:57 PM
    • Unproposed as answer by Mike Agnello Monday, August 20, 2012 8:57 PM
    Monday, August 13, 2012 2:52 PM
  • Not sure if this is possible or not.  Conceptually though it will be a matter of looping through the Task(s) of the Active Selection object:

    For each T in ActiveSelection.Tasks

       T.AssignmentsAdd ResurceID

    Next T

    Hope this helps

    Monday, August 20, 2012 9:05 PM