Project VBA, easy script needed 1

[smooveb]

Hi!

I believe I can fairly easily creat a button that brings up a userform, complete with text entry field and button for executing a macro. I'd like a little help with the code I need to solve the following problem:

In the userform, I'd like the user to enter a number from 0-5. This number would act as a weight, and each task on the plan would have its duration multiplied by this number.

Can someone provide me with a snippet of code that might help get this done?

Thanks in advance!
Barrett

 

[SkipVought]

smootheb,

why not use a Spinner control that incriments a Label th the Spinner1.Value.

Set the Min/Max to 0/5 respectively.

then on the Spinner1_Click event
Private Sub Spinner1_Click()
Label1.Caption = Spinner1.Value
End Sub
Then on whatever button click event, use the Spinner.Value for your weight factor.

;-)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smooveb]

thanks!

that will get me started. however, I am enough of a newbie that I'm also looking for a snippet of code that will multiple each row's Duration by the spinner1.value

any thoughts?
Thanks!
Barrett

 

[SkipVought]

Private Sub Spinner1_Click()
   Label1.Caption = Spinner1.Value * SomeCell.Value
End Sub

or do you want to do this...

Private Sub Spinner1_Click()
   SomeCell.Value = Spinner1.Value * SomeCell.Value
End Sub

then you're gonna need a spinner associated with each cell value you want to display/change.

you mentioned a userform and you mentioned rows. Tell us what you are trying to do conceptually.

???

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smooveb]

Ok, here goes

I want to have a template listing all the documentation needed for a typical IT project. Let's say there are three things:

Requirements Document 3 Days (Duration)
Software Design Spec 4 Days
Test Summary Rpt 3 Days

If I set the spinner to 10 (if 10 is the max), I'm giving the project a weight of 10, compared to the 1 weighting of the default. I'd want the durations to extend to 30, 40, and 30 days.

So I'm thinking that I have just about everything, except I'd need a method of saying something like this:

For all Rows in Project
Duration.Value = Spinner1.value * Duration.Value
Next Row

Will these lines of code do the trick?
Thanks!
Barrett

 

[SkipVought]

Wouldn't it be better to have a Weight column and a Weighted Duration column that is calculated =Dur * Weight?


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smooveb]

I'd like to change the actual duration of the tasks though - so someone could say, weight this project at around a 5, their tasks would lengthen in effort accordingly, and they could use the schedule as a decent start to their project work breakdown structure. So not sure if a weighted duration column would accomplish what I need. Do you think the code I typed above would work?
B

 

[SkipVought]

You could have a SINGLE spinner on the sheet that would change its Visible, Top, Left properties based on the Worksheet_SelectionChange event and would modify the DurationWeight value for that row.

I still think that you need to have a column containing the ORIGINAL DURATION, so maybe the formula in the Duration column is =OrigDur * DurWeight.


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smooveb]

Ok, I've got my userform with spinner, lable and an execute button.

Couple questions then:
1. what is the proper way to implement an OrigDur button? I was using Text1 or Number1, but not sure if that helps.

2. I have the spinner control working, however, my execute button, based on the following code, does not seem to be doing anything:

Public Sub CommandButton1_Click()
DurWeight = SpinButton1.Value
Duration = OrigDur * DurWeight
UserForm2.Top = UserForm2.Top + 1
End Sub

Wish I could apply the spinner to all durations at once (loop through), but I'm definitely willing to try moving the userform downwards row by row as I apply different weights by row. Actually, that give more control so would be worthwhile. Should I be trying row.duration?

B

 

[SkipVought]

So you have a contiguous range in which is the Duration Column AND you want to apply the SAME Weight to ALL Durations?

BTW, the reason I argued for an OrigDur column is to be able to reverse the process (the product of OrigDur and a Spinner value of 1 -- otherwise, you could ONLY increase Duration)

The process would then be

1. Set the Spinner Value
2. Run UpdateDuration Procedure from Button click event

sub UpdateDuration()
iColOrig = [OrigDur].Column 'assuming that the Duration range is named "Duration" and OrigDir is named "OrigDur"
  for each d in [Duration]
    with d
      .Value = Spinner1.Value * Cells(.Row, iColOrig).Value
    end with
  next
end sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smooveb]

thanks - pretty soon I'll have learned enough to go about this myself hopefully!

I customized a field named OrigDur (Duration1) based on a task field (as opposed to a duration one). I put in a formula making it equal to duration, although I can see why this might need to be changed, and since this is a template I have no problem manually entering OrigDur to start. However, when compiling my code I keep getting an 'external name not defined' error. Any ideas?

B

 

[SkipVought]

Not having MS Project, I cannot answer to the specifics.

But why base a NUMERIC field on a TEXT field. You can't have a FORMULA making it equal to Duration, because you are going to be CHANGING duration, I beleive. Your code should initialize OrigDur by setting it equal to Duration.

Do you get the opportunity to debug when the error occurrs or does the complier select a place in your code?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[smooveb]

Cool, definitely appreciate your help skip! I'm gonna tell you what I have at this point. Got a small userform that I bring up with a spinner (label goes from 1 to 10 starting at 1, could start at 5 later perhaps) and an execute button. The code for the button is as follows:

Public Sub CommandButton1_Click()
Dim OrigDur As Long
OrigDur = Duration

iColOrig = OrigDur.Column 'assuming that the Duration range is named "Duration" and OrigDir is named "OrigDur"
For Each d In Duration
With d
.Value = Spinner1.Value * Application.Cells(.Row, iColOrig).Value
End With
Next
End Sub

Not sure if I should be initializing origdur within the loop or not. But besides that, the error I'm getting currently is that OrigDur is an invalid qualifier. Hmmm...

B

 

[SkipVought]

Looks to me like

iColOrig = OrigDur.Column 'assuming that the Duration range is named "Duration" and OrigDir is named "OrigDur"

is a problem

SINCE OrigDur is Long and NOT an OBJECT and you're using it like an object in this statement.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com