This is one of my favourite examples mainly because it achieves something that most people thought was not possible with VBA. To be honest, it still is not possible with VBA alone. This hybrid code uses the unsupported method of calling Dexterity sanScript from VBA to use the Dexterity commands to select and change colours.
This example was demonstrated at the Microsoft Dynamics GP Technical Airlift 2008 to show how you can have a Microsoft Dynamics GP for Barbie release, see screenshot below:
The Modifier is used to modify the form and create the additional buttons and fields needed. Then VBA is used to add the scripting. I created two VBA functions that do most of the work, SetColour and GetColour. The SetColour function uses the Continuum Integration Library to execute Dexterity sanScript to set the background colours from values passed in.
The GetColour function is a bit more complex. It also uses the Continuum Integration Library to execute Dexterity sanScript to open the colour selection dialog and store the results in the SY_User_Object_Store table. The SY_User_Object_Store table, which is the SY90000 table in the company database, is the table used for the VBA Dynamic User Object Store (DUOS) table. As we are unable to pass parameters back from sanScript when executing it from VBA, we can store data in the DUOS table and then read that data from VBA as another method of returning data. Storing the data in the DUOS serves a double purpose in this example, it allows the passing of the data back from Dexterity to VBA and stores the settings so they will be remembered next login.
The buttons on the Company Options window use these two functions to provide the user interface for the customisation. The final piece of the puzzle is the use of a Toolbar.package. Even though the toolbar form is no longer visible in version 8.00 or later and so cannot be added to VBA, it can be added as a package and used to execute VBA code after login. For our example, it is using the SetColour function to set the background colours for the current company based on the values read from the DUOS.
NOTE: This customisation uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.
Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.
Please see the “Installation Instructions.txt” file in each version’s archive for more information.
WARNING: This posting and example contain International English spelling, use at your own risk.
[Edit] Often this background colour example is wanted to help identify Test or Historical companies. Please see the Setting up Test and Historical Companies post for how to have a dialog pop up during login as well.
[Edit] This feature is now available from Administrator Settings window of the Support Debugging Tool for Microsoft Dynamics GP, have a look at the Support Debugging Tool Build 11 released post for more info.
17-Mar-2009: Added link to Test and Historical Company post.
05-Jun-2009: Added link to Support Debugging Tool Build 11.