Have Project VBA find a selected cell's absolute row number

[EdLivingston]

I am trying to use MS Project VBA for a macro to delete certain rows after
selecting a specific task. For example, a machine has three options for
configuration. I have a template containing all three options. I want a PM to
be able to delte the un-needed two options with a macro.

 

[John]

I am trying to use MS Project VBA for a macro to delete certain rows after
selecting a specific task. For example, a machine has three options for
configuration. I have a template containing all three options. I want a PM to
be able to delte the un-needed two options with a macro.

EdLivingston,
Well yes it can be done but what you describe certainly doesn't seem
like a very practical or efficient approach. When you talk about a
"task" do you really mean a summary line (i.e. subtasks under a
summary)? How is the macro code to "know" which subtasks to delete? Once
the PM selects a particular row, is it your intent to then have him/her
initiate the macro simply to delete some subsequent rows and do this
over and over as each row is selected?

In my opinion a better approach is to have the PM manually delete task
rows that are not needed. One semi-automated way to do that is to have
the PM go through the template and set a spare flag field for task rows
to be deleted. Then filter on that flag field and do a wholesale delete
of the filter results.

John
Project MVP

 

[EdLivingston]

John,

I understand, particularly if there is not a straightforward method of doing
this. Deleting each task, line by line, however, is asking my PM to delete
10-20 lines per machine for projects that sometimes include 300 machines.
This many deletions will not be the preferred choice.

The macros I am using now delete by relative line. so it requires the PM to
have his wits about him, since there is little or no error checking present,
and that may have to be the solution. I was really hoping for more
capabilities from VBA.

Ed

 

[John]

John,

I understand, particularly if there is not a straightforward method of doing
this. Deleting each task, line by line, however, is asking my PM to delete
10-20 lines per machine for projects that sometimes include 300 machines.
This many deletions will not be the preferred choice.

The macros I am using now delete by relative line. so it requires the PM to
have his wits about him, since there is little or no error checking present,
and that may have to be the solution. I was really hoping for more
capabilities from VBA.

Ed

Ed,
It sounds like the "more capabilities" you're asking from VBA includes
some fuzzy logic to determine which task rows to delete. At least that's
my take since you didn't really answer the questions I asked in my
response.

I still suggest the flag, filter and delete method I described. It
provides a pretty simple approach with an opportunity to go back and
check before actually doing the delete. You know, measure twice, cut
once.

John
Project MVP

 

[Jack Dahlgren MVP]

Ed,

There are some hard limitations in VBA - things like properties that aren't
accessible etc. but it sounds like that is not the issue here.
If the steps you are having the PM's take are repeatable, then it is very
possible that code can be developed which does those things for them.
It might take several steps and configuring your project in a certain way.

For what you are doing I can certainly imagine building a file which codes
the different options in a way that VBA can recognize them.
I can also imagine a VBA procedure which goes through the file, finds each
machine then offers the user a choice of configurations, then deletes the
ones which were not chosen. Or they could make a single choice which
determines multiple configurations.

It would not be too difficult.

 

[EdLivingston]

Jack,

Thanks for responding. Yes, the steps are repeatable. I have included some
screen clips of a Project file typical of what we are using, in fact, it is
the template for one line of our management products. The first clip shows
the summary tasks "exploded" so tasks show, although the PM is more than
likely going to make adjustments with the summary tasks collapsed. The
procedure is this: the PM selects Machine #1 Installation, a summary task. He
will double click to "open" and change the name of the machine to M028788 (or
something appropriate that the customer has designated). Upon deciding
(somethng PMs do) what PLC Machine #3 requires, he will one of three possible
configurations, shown in the second clip, by selecting appropriate the
toolbar button, shown in the third clip...

This was all assuming that I could insert clips from OneNote, which I don't
seem to be able to do!

How can I forward the screen clips to you?

Ed Livingston

 

[Jack Dahlgren MVP]

Ed,

What you are describing is certainly possible, but I'd guess it would take a
few days of development to put it all together.

The basic code would be a form which would start the process. The form could
have some fields for required information in case there is data that the PM
is required to enter or which is used several times in the process.

Then the user could click a button to start the configuration process
This would start a procedure which goes through all the tasks and builds a
list of tasks which have not been configured yet.
The user could select one of the tasks and then you could read the
information about the possible configurations and display it.
The user could then pick the one they want and continue - which would have
the other configurations deleted.
The summary would be marked in someway (a custom field or some other method)
and then the list of tasks would be rebuilt with only the remaining
unconfigured tasks.
It would require the use of a few custom fields - one for whether the
summary needs to be configured, one to determine which group of subtasks is
which and perhaps another, but you may be able to get away with just those
two.

When all of the required configuration is done, the form could state that
none are left to configure and the user could close it.

-Jack Dahlgren

 

[EdLivingston]

Jack,

In perusing the messages under "...Developer" I came across a reference to
UniqueID numbers, and did additional research. My code writers tell that if
the UniqueIDs are sequencial then a macro to delete specific summary tasks
would not be difficult and could be made somewhat "bullet proof." I tried
modifying the template I set up and it appears fairly easy to make these
numbers sequencial in the template.

I am to write this myself, but it looks like I will have ample support from
our softare developers. Thanks for your input and support, and I may be back
in touch after a few more attempts!

Ed