Thursday, December 12, 2013

WIP: Using UserForm.TextBox.Tags for process control

I am currently working on a userform that has some subtotals and then a total of some of those fields.  Some of the fields are also used to update a worksheet.  Here's the plan, so far.

  • For each of the TextBox controls on the form, use the Tag field to identify whether the field is used for a subtotal, the grand total, is the result of summing, and is posted to the worksheet.  
    • Each process would have a unique tag, e.g. "HeadCount", "LaborCost", "LaborTime"
    • The role in the calculations would be identified by a prefix or suffix, e.g. "_Total" and "Add_". Prefixes would be used for inputs (the lefthand side of a  calculation) and suffixes for the result. This Prefix/Suffix standard is mainly for purposes of visual identification when reviewing and maintaining the code.
    • The different elements would be separated by semi-colons, e.g. "Add_HeadCount;","Add_LaborCost", "HeadCount_Total;Post", "LaborCost_Total;Post"
  • On a Change event of each TextBoxControl, the same subroutine is called.  That subroutine calls the validation functions and a subroutine to perform downstream processes.
  • The downstream process would execute a loop that uses a function to discern what actions, if any, should be taken.  Based on the identified action, e.g. summing a subtotal, it would call a function to do that.
  • The discernment of what actions to take would be done by a function that finds the next element of the list, using the previous result as a starting point.  
  • Only one summing function is needed.  It will check to see is the format is numeric or fits a format of hours and minutes.
  • The summing function loops through the UserForm's Controls collection, selecting the TextBoxes only then invoking a function to determine the TextBox's role in the calculation. 
  • The Function to determine the textbox's role in calculation simply looks for the action discerned in the Tag field, then picks the role based on the suffix or prefix and returns the object with the role.
  • A conversion function is needed to convert the hours and minutes, which are reported as hours and decimals of hours.