Userform & textbox help

[WildWally]

First off forgive me, VBA in MS Project is something new for me. I have
dabbled with it in Excel quite a bit.

This is where I need the help of the experts (you). I have a userform with
five textboxes. I want the user entered values in the textboxes to be
entered into the project file work column as hours. I have tried so many
different things and nothing works. Now this all happens when the user
selects the enter button of course.

Next, if I wanted to do the same thing but change dates (start and/or
finsih)instead of work what would I need to change?

One last thing (for now) I want to have a date from a textbox set the
deadline date for a task how would I go about that.

Thanks in advance for any help.

 

[Jack Dahlgren]

Last things first:

doubleclicking the "Enter" button on the userform will bring up the code for
the button. Inbetween the sub() and end sub lines you need code which sets
the task's deadline value.

for example

task.deadline = textbox1.value

This requires that you have identified which task to set the deadline for.
If you are working with a single task selection then

activeselection.tasks(1).deadline = textbox1.value

would probably work.

Changing start and finish dates is not a good thing to be doing. Those dates
should be set through schedule calculation based on the project logic
(dependencies and durations). One way to handle this would be to set
constraints on the dates. This would involve setting a constraint type and
constraint date for the task, and possibly duration as well. To be
successful at coding this you need to understand how project calculates
schedule dates and what happens when you change them. Playing with a project
file and the macro recorder would be a good start. I'd give advice, but I
have no idea what you are hoping to achieve by doing this.

Debugging the issue with your userform is easiest if you show what the
different things you have tried are. Posting the code is a good start.
Without knowing what you are trying to do and how you are going about it, it
is difficult to debug.

-Jack Dahlgren

 

[WildWally]

Jack, thanks for your help my forehead couldn't handle anymore beatings.

As for the start/finsih dates this userform is being designed for Project
managers to do the initial set up of important delivery dates.

I was extatic to see a line of code actually work and not give me a error.

However, entering hours (value) into the work column for each task is still
giving me a runtime error 1101 "the arguement value is not valid"

Any suggestions as to what that means?

 

[Jack Dahlgren]

Wally,

It just means you have an issue with your declarations, your syntax or the
value that you are passing, or it could be something else

Posting the code here is the only way I can see what is going wrong.

-Jack

 

[WildWally]

Private Sub Hoursenterbutton_Click()
ActiveSelection.Tasks(2).Work = hoursenter.PMhoursTB.Value
ActiveSelection.Tasks(3).Work = hoursenter.SafetyhoursTB.Value
ActiveSelection.Tasks(4).Work = hoursenter.MischoursTB.Value


nothing fancy.
The user plugs the amount of hours associated with the task and hits enter.
Then the code should enter the hours into the Work column for the task.

I'm try the start date right now and it is doing the same thing. I have
even made sure that the format of the date is correct.

Here is what I have so far for the dates.

Private Sub UG_OVHD_continuebutton_Click()

ActiveSelection.Tasks(9).Start = dateenter_UG_OVHD.UG_startdate.Value
ActiveSelection.Tasks(17).Deadline =
dateenter_UG_OVHD.UG_deadlinedate.Value
ActiveSelection.Tasks(19).Start = dateenter_UG_OVHD.OH_startdate.Value
ActiveSelection.Tasks(24).Deadline =
dateenter_UG_OVHD.OH_deadlinedate.Value
dateenter_UG_OVHD.Hide
Unload Me
dateenter_MTR_COND.Show
End Sub

 

[WildWally]

I believe the problem stems from the line of code pointing to the cell. I
stumbled upon this. It enters the values on the line that I have selected
prior to running the userform.

So from what I can tell, I'm back to square one "how do I address a single
cell as if I was address a cell in Excel? (Thats the best way I know how to
describe it)

 

[WildWally]

Sorry for the multiple post, but I have another.

Just to confirm my earlier statement if I select a cell anywhere in the
schedule and run a single line of the code with the Tasks(1) it enters the
deadline, work, and/or start date as I want it to. So my problem does in
fact lie with the addressing of the cell or row locations.

Which leads me to the next tab, and if this is too much for you I
understand. (You are the only sole out there to offer any type of help so
far, thanks) I found that I get an error if the date entered falls on a
weekend, which is good. But I do not want debug errors going off with the
end user. So how might I go about having the date turn red if the date is on
a weekend in the userform? Is this possible? Some way for the user to know
the date needs to be changed.

Once again I thank you. Looks like I need to buy a book on Project VBA, lol.

 

[Jack Dahlgren]

Wally,

Tasks(1) is the first task in whatever task collection you are working with.
It may be activeselection.tasks, activeproject.tasks etc.

Project is not really like excel, so you would do well to stay away from
addressing cells and the like and address the specific tasks. You can look
at my website for working with tasks and task collections.

Go to
http://zo-d.com/blog
and look at articles in the "Programming" category.

I also caution you against directly entering task dates. It is a bad
practice in general. You should probably study up on use of Microsoft
Project before you look into doing a lot of VBA. Understanding how project
works is the hardest part of working with it.

As for trapping non-working time, I'd probably start looking at some of the
calendar methods calendar.weekdays for example:

ActiveProject.Calendar.Period( [the date value of the date from your
box] ).Working

should return true if it is a work day on the standard calendar. You can
trap for that pretty easily and either stop the user or push it forward or
backwards to the closest valid date.

-Jack Dahlgren

 

[John]

Sorry for the multiple post, but I have another.

Just to confirm my earlier statement if I select a cell anywhere in the
schedule and run a single line of the code with the Tasks(1) it enters the
deadline, work, and/or start date as I want it to. So my problem does in
fact lie with the addressing of the cell or row locations.

Which leads me to the next tab, and if this is too much for you I
understand. (You are the only sole out there to offer any type of help so
far, thanks) I found that I get an error if the date entered falls on a
weekend, which is good. But I do not want debug errors going off with the
end user. So how might I go about having the date turn red if the date is on
a weekend in the userform? Is this possible? Some way for the user to know
the date needs to be changed.

Once again I thank you. Looks like I need to buy a book on Project VBA, lol.

WildWally,
I'm surprised that Jack didn't mention this but fellow MVP, Rod Gill has
an excellent book specifically on Project VBA. You can find out more
about it at, http://www.projectvbabook.com.

I do agree with Jack though that you are getting the cart ahead of the
horse if you start dabbling in Project VBA before really understanding
what Project does and how it operates.

John
Project MVP

 

[WildWally]

Thank you for your suggestions, and I really do appreciate them. I
understand that MS Project has many different roles and purposes. I also
understand the concerns about forcing dates and such within Project. This
however is something we do to set days when equipment and such is schedule to
ship (milestone). This process then forms the working parameters for which
our project managers have to manage the job. Imagine it like a bunch of
boulders that can not be moved, therfore you have to work around them.

I also want to acknowledge the fact that I have seen and know about Rod
Gills book. I have considered making the purchase, but do not know if I will
be doing much more in the way of VBA with Project. I do however have a nice
collection of books for VBA Excel, lol. Thanks again

 

[WildWally]

Jack,
Just before I left the office I did figure out what I was doing wrong. It
in fact is the selection.
For future reference of others the line reads like so:
ActiveProject.Tasks(#).Work = what ever.

I have been able to get the userforms to work as I wanted, and now I have a
few more things to figure out.

I have pesterd you enough, and will try to figure out and hunt the solutions
down.

Thanks a bunch.

Wally,

Tasks(1) is the first task in whatever task collection you are working with.
It may be activeselection.tasks, activeproject.tasks etc.

Project is not really like excel, so you would do well to stay away from
addressing cells and the like and address the specific tasks. You can look
at my website for working with tasks and task collections.

Go to
http://zo-d.com/blog
and look at articles in the "Programming" category.

I also caution you against directly entering task dates. It is a bad
practice in general. You should probably study up on use of Microsoft
Project before you look into doing a lot of VBA. Understanding how project
works is the hardest part of working with it.

As for trapping non-working time, I'd probably start looking at some of the
calendar methods calendar.weekdays for example:

ActiveProject.Calendar.Period( [the date value of the date from your
box] ).Working

should return true if it is a work day on the standard calendar. You can
trap for that pretty easily and either stop the user or push it forward or
backwards to the closest valid date.

-Jack Dahlgren

Sorry for the multiple post, but I have another.

Just to confirm my earlier statement if I select a cell anywhere in the
schedule and run a single line of the code with the Tasks(1) it enters the
deadline, work, and/or start date as I want it to. So my problem does in
fact lie with the addressing of the cell or row locations.

Which leads me to the next tab, and if this is too much for you I
understand. (You are the only sole out there to offer any type of help so
far, thanks) I found that I get an error if the date entered falls on a
weekend, which is good. But I do not want debug errors going off with the
end user. So how might I go about having the date turn red if the date is
on
a weekend in the userform? Is this possible? Some way for the user to
know
the date needs to be changed.

Once again I thank you. Looks like I need to buy a book on Project VBA,
lol.