VBA Capturing and Preventing a Custom Field Value Change

[Incanus]

Hello:

Since it's been made clear to me at

http://groups.google.com/group/micr.../browse_thread/thread/3c69e3758a2f5f2b?hl=en#

that I can't make a Custom Enterprise Field (or any Project field)
read - only, I'd like to develop a solution through Project
Professional VBA that:

1) Captures the initial (after opening the schedule/gantt) value of a
field
2) Detects if the value is changed by the user and
3) Gives a warning and then changes the field back to it's previous
value

this VBA macro would be installed in the Global.MPT and should be
running once a project is opened.

To work 1) first, I need to identify the FieldID of a certain Custom
Enterprise Field of a given "friendly name" (that uses a pre defined
list of values), and then get it's value to store it on a global
variable.

How to do this?

Thanks in advance,

Sebastián Armas

 

[Jan De Messemaeker]

Hi,

1. Fieldnametofieldconstant ("The friendly name") yields the field ID

2. In a non-Server environment, I often write values to the Global, but in a
server environment that seems more tricky, since your global isn't saved
after the run. But since you are dealing with a temporary value anyway why
not use activeproject.projectsummarytask.text30 or so?

In general, the macro will not "run continuously from when the project is
open" because while a macro runs the user can't get control.
The macro has to be triggered by an application event such as
BeforeTaskChange.
BTW, this event has a Cancel method that will stop the requested change - so
AFAIK you don't even have to record the original value.

HTH

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
Hello:

Since it's been made clear to me at

http://groups.google.com/group/micr.../browse_thread/thread/3c69e3758a2f5f2b?hl=en#

that I can't make a Custom Enterprise Field (or any Project field)
read - only, I'd like to develop a solution through Project
Professional VBA that:

1) Captures the initial (after opening the schedule/gantt) value of a
field
2) Detects if the value is changed by the user and
3) Gives a warning and then changes the field back to it's previous
value

this VBA macro would be installed in the Global.MPT and should be
running once a project is opened.

To work 1) first, I need to identify the FieldID of a certain Custom
Enterprise Field of a given "friendly name" (that uses a pre defined
list of values), and then get it's value to store it on a global
variable.

How to do this?

Thanks in advance,

Sebastián Armas

 

[Incanus]

Jan:

Thankyou for your complete and prompt reply.

My comments and further questions, below:

Hi,

1. Fieldnametofieldconstant ("The friendly name") yields the field ID
Ok...

2. In a non-Server environment, I often write values to the Global, but in a
server environment that seems more tricky, since your global isn't saved
after the run. But since you are dealing with a temporary value anyway why
not use activeproject.projectsummarytask.text30 or so?

Mainly, to keep from altering any other data whatsoever that the user
might have stored... so I'll stick with the global variables, if at
all possible.

In general, the macro will not "run continuously from when the project is
open" because while a macro runs the user can't get control.
The macro has to be triggered by an application event such as
BeforeTaskChange.

Does this event works for Enterprise Custom Field for a Project?
I'm trying to prevent field editing of some of the data displayed with
Projects' Pro menu options Project -> Project Information...

If not, what VBA command could work?

BTW, this event has a Cancel method that will stop the requested change -so
AFAIK you don't even have to record the original value.

That's great... if you can configure it to show _only_ the Cancel
Option, so the user can't edit the field.

Awaiting your kind reply,

Sebastián Armas
Project Manager

 

[Jan De Messemaeker]

Hi,

OK, gave it some further thoughts...

2. My worries are unnecessary. YOu can indeed write to the global; the fact
that it isn't stored is no problem since it is only a temporary value used
to compare during the same run.
BUT... when the PM has More than one project open each one of these may
write to the same global variable: have you thought of that?

By all means, you access a global's field as

vbe.vbprojects(1).vbcomponents(1).vbproperties("Text1")="Whatever"
Note that there are fewer Global custom fields that for tasks and
resources... check in the immediate window first.

3. Can't immediately test in Server environment from here so I'm not sure as
to whether the change to a project level custom field is handled by VBA as a
change to the Project Summary Task (I think so) and as such will trigger
BeforeTaskChange.
I would link your approach to the ProjectBeforeSave Event: the user may
change whatever he wants but cannot save the "wrong data" to the server.

Hope this helps,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf
Jan:

Thankyou for your complete and prompt reply.

My comments and further questions, below:

Hi,

1. Fieldnametofieldconstant ("The friendly name") yields the field ID
Ok...

2. In a non-Server environment, I often write values to the Global, but in
a
server environment that seems more tricky, since your global isn't saved
after the run. But since you are dealing with a temporary value anyway why
not use activeproject.projectsummarytask.text30 or so?

Mainly, to keep from altering any other data whatsoever that the user
might have stored... so I'll stick with the global variables, if at
all possible.

In general, the macro will not "run continuously from when the project is
open" because while a macro runs the user can't get control.
The macro has to be triggered by an application event such as
BeforeTaskChange.

Does this event works for Enterprise Custom Field for a Project?
I'm trying to prevent field editing of some of the data displayed with
Projects' Pro menu options Project -> Project Information...

If not, what VBA command could work?

BTW, this event has a Cancel method that will stop the requested change -
so
AFAIK you don't even have to record the original value.

That's great... if you can configure it to show _only_ the Cancel
Option, so the user can't edit the field.

Awaiting your kind reply,

Sebastián Armas
Project Manager

 

[Jack Dahlgren]

Incanus,

The problem with this sort of solution is that often security settings
prevent macros from running, so all a user needs to do is to set security to
medium or above to defeat this approach. In fact, in many organizations macro
security is set to high by policy and can't be changed.

-Jack

 

[Incanus]

Jack:

Not a problem on our curent implementation, but I won't get into
details ;-)

I do agree I would be a consideration in many organizations, tough.

Thanks for your input,

Sebastián Armas

 

[Incanus]

Jan:

My comments, below.

Hi,

OK, gave it some further thoughts...

2. My worries are unnecessary. YOu can indeed write to the global; the fact
that it isn't stored is no problem since it is only a temporary value used
to compare during the same run.

My thoughts exactly.

BUT... when the PM has More than one project open each one of these may
write to the same global variable: have you thought of that?

No. It's an issue. I could handle it whit an array of variables, tied
to the project's name... but no, I hadn't considered that yet.

One problem at a time ;-)

By all means, you access a global's field as

vbe.vbprojects(1).vbcomponents(1).vbproperties("Text1")="Whatever"
Note that there are fewer Global custom fields that for tasks and
resources... check in the immediate window first.

3. Can't immediately test in Server environment from here so I'm not sureas
to whether the change to a project level custom field is handled by VBA as a
change to the Project Summary Task (I think so) and as such will trigger
BeforeTaskChange.
I would link your approach to the ProjectBeforeSave Event: the user may
change whatever he wants but cannot save the "wrong data" to the server.

I also think that the ProjectBeforeSave Event is the way to go... but
I'm yet to capture the project level Custom Enterprise Field _value_

I've already have the Field Id value through
Application.FieldNameToFieldConstant("Custom Enterprise Field Name")

How do I get it's value within VBA??

Awaiting your kind reply,

Sebastián Armas
Project Manager

 

[Incanus]

Hello:

Comments of my own (literally)...

I've found the Resource.GetField Method and the Task.GetField Method.

Both return the value in a field, even a custom one, according to
Larry's Project Dev Blog:

http://blogs.msdn.com/lduff/archive/2006/08/23/715531.aspx

....but they only do so for Resource and Task fields, respectively. I'm
yet to find a similar function for a Project field, including (I
daresay) the aforementioned Project Custom Enterprise Fields' values
I'm (still) trying to get.

A little clarification: it really doesn't solve this issue, but it may
help others (or me, eventually).

Awaiting your kinds insights,

Sebastián Armas
Project Manager