Variable user defined field names RRS feed

  • Question

  • using 2007

    I have a user form and associated code that captures metrics.  While most of the values are captured in variables and displayed in the form, some of the results are captured in user defined fields - example, tasks with missing links is captured in Text26 which I title as "Link Stat"

    Since I distribute this form to fellow schedulers and subcontractors, I need a way to allow them to re-designate the fields.  Example, a sub may use Text26 for something else.  So...rather than have him go into the code and change Text26 to Textxx.  I have a tab in the user form that describes the fields used and for what.  I would like a way for him to select the fields he needs to use in this setup tab and have it propagate thru the code automatically. 

    Is it possible to use a constant and then change the constant on the fly?

    any ideas?


    Monday, April 16, 2012 6:38 PM

All replies

  • GMAN1941,

    As far as I know there is no way to modify VBA code with other VBA code. It may be possible with an API or something but that's beyond the scope of this forum.

    However let me offer another approach. Query your fellow schedulers and subcontractors and find out which extra fields they use. Take that information and come up with a common set. You may need to negotiate with some users. Once you have a agreed upon set of "claimed" and available extra fields, then you can modify your code as necessary.

    It's usually very difficult to work in a multi-user environment without a set of ground rules. Make life easier for everyone involved and develop a basic structure.


    Tuesday, April 17, 2012 1:54 AM
  • You could have a form asking for custom field names. So if Text26 is entered for a datum, then something like:


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management

    Tuesday, April 17, 2012 7:45 AM
  • Thanks John.  I have thought about that.  It's too daunting - too many users to deconflict.  That's why I need a way for each scheduler to define the fields for himself - kinda like an INI file that he would set once and continue on.


    Tuesday, April 17, 2012 12:59 PM
  • Rod,

    thanks for the input.  I have your book and looked there first.  Looking at the use of Constants seemed promising, but I couldn't figure out how to dynamically change the Constant - something like:

    Const LinkStat = LinkStatSet  where LinkStatSet could be dynamically set to Textxx by the user with the result that Const LinkStat = LinkStatSet = Text12 say

    the problem is how to run a setup macro at startup that has previously been set

    I'm not sure how I could use


    to allow the user to define his own fields used for data storage.

    I think what I need is an INI file - or something like it - that the user could use to define the data storage fields based on his own setup that would be accessed on startup.

    any thoughts?


    Tuesday, April 17, 2012 1:27 PM
  • GMAN1941,

    If collaboration with other users is not viable, and I understand, sometimes it's not, then you need to take an indirect approach. What you want to do is similar to something I used in a couple of macros I wrote several years ago. The basic structure is this. The module starts out with a basic macro that does some file check tests (e.g. does the file have tasks?, etc.). Then the code calls the userform. In the userform the user is asked for certain pieces of information (i.e. dates, values, etc.). The last thing the userform does before closing is to call the main macro with a string of variables. Those variables are then used in the main macro.

    In your case, you can have the user indicate which fields are desired. Set a variable for those fields and use them in the call to the main macro. In the main macro, you use the GetField or SetField Methods as appropriate to accomplish what you need.

    Hope this helps.


    Wednesday, April 18, 2012 2:39 PM