User form for Resource Assignment
-
mercredi 18 juillet 2012 17:48
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.
Toutes les réponses
-
mercredi 18 juillet 2012 18:15
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
- Proposé comme réponse Rod Gill MVPMVP, Moderator jeudi 19 juillet 2012 05:21
-
jeudi 19 juillet 2012 14:41Alright 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.
-
jeudi 19 juillet 2012 15:06
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 ViewHope this helps
- Marqué comme réponse Jake683 jeudi 19 juillet 2012 15:18
-
jeudi 19 juillet 2012 15:18Thank 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!
-
vendredi 20 juillet 2012 16:08How 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?
-
vendredi 20 juillet 2012 18:25
Jake683
Are you using VBA, VB .net or C# .net to create this Form?
-
vendredi 20 juillet 2012 18:47
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.
-
vendredi 20 juillet 2012 19:02
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 SubPrivate 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- Marqué comme réponse Jake683 lundi 23 juillet 2012 14:46
-
lundi 23 juillet 2012 14:20Thank 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.
-
lundi 23 juillet 2012 14:29Happy to be of assistance
-
lundi 23 juillet 2012 15:01
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.
-
lundi 23 juillet 2012 15:42
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
-
lundi 23 juillet 2012 15:46
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- Modifié Jake683 lundi 23 juillet 2012 15:49
-
lundi 23 juillet 2012 16:16
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
-
lundi 23 juillet 2012 17:51
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?
-
lundi 23 juillet 2012 18:07
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.
-
lundi 23 juillet 2012 18:11
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.
-
lundi 23 juillet 2012 18:42
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 Subwith 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.
- Marqué comme réponse Jake683 mercredi 25 juillet 2012 16:51
-
lundi 23 juillet 2012 18:47
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.
-
lundi 23 juillet 2012 19:00Somewhere 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.
-
mardi 24 juillet 2012 14:39
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.
-
mardi 24 juillet 2012 15:02
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
-
mardi 24 juillet 2012 15:46
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)
-
mardi 24 juillet 2012 19:16
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 -
mercredi 25 juillet 2012 15:09
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.
-
mercredi 25 juillet 2012 15:28
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 SubThe 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.
-
mercredi 25 juillet 2012 15:52
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
-
mercredi 25 juillet 2012 16:33
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
-
mercredi 25 juillet 2012 16:42
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
-
lundi 13 août 2012 14:52
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?
- Proposé comme réponse Mike Agnello lundi 20 août 2012 20:57
- Non proposé comme réponse Mike Agnello lundi 20 août 2012 20:57
-
lundi 20 août 2012 21:05
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
