Enhancing Dynamics GP With VBA Customization
There are times in Dynamics GP where you want to change the behavior of a form and VBA customization (Visual Basic for Applications) can be a great tool to use!
For instance, changing the site ID and requested ship date in a sales order header doesn’t revise sales line items that are already in the sales order – changing the site ID and requested ship date in existing line items was a requirement for one of our clients.
In this blog, we will go over how to enhance Dynamics GP using customization via VBA and deployment.
Before making any changes, make sure to copy the DYNAMICS.SET file (name the copy DYNAMICS_DEV.SET or something similar to distinguish it from the original). Open GP using this copied file by dragging the DYNAMICS_DEV.SET file over the Dynamics.exe file and select open. This will allow you to make changes without affecting the Dynamics GP instance already in use.
Add the window to be customized to Visual Basic by opening the Sales Transaction Entry window, open Tools > Customize > Add Current Window to Visual Basic.
Follow that up by adding the fields to be used. We want the custom code we’re writing to be fired off when the user clicks on the Save button so we need to add the Save button to Visual Basic. To do this, we’ll open the Tools > Customize again but this time we’ll select Add Fields to Visual Basic. When you click on Add Fields to Visual Basic, the mouse pointer will change modes to indicate that you are in the Add Fields mode; simply click on whatever field or button you want to add in order to make it available in Visual Basic for coding. I clicked on the Save button and now its object will be available in Visual Basic for coding.
We will be changing the requested ship date in the header and want to capture the date we enter so we click on the arrow next to the Date field which opens up the Sales Date Entry Window:
In the Sales Date Entry, we will capture the date entered by adding the Sales Date Entry window to Visual Basic:
And then adding the fields as well to utilize the requested ship date field and the OK button.
I clicked on the OK button since our coding will need to be run when the user clicks OK and the requested ship date field since I will be utilizing its value.
We’ve now added the windows and fields to VBA that we need. Now we will open Visual Basic Editor:
Once in the VB Editor, you will see the windows we added:
We need to add a module to store coding and variables that would be available to other procedures. To do so, right-click on the Modules folder, click on Insert and then Module:
Name the new module "Globals".
In this case, I wanted to add the ADODB connection to be used within the other objects’ code as well as a global variable, dDate, to capture the new requested ship date. Double-click on the Globals module to open its coding window and then add the public variable and the GetConnection() function:
I added a procedure to the OK button of the SalesDataEntry window by first double-clicking on the SalesDataEntry object and then selecting OK from the fields drop-down:
I selected BeforeUserChanged as the event to code from the right-hand drop-down which caused an empty procedure to be created for this event. I added my code to the procedure that will assign the requested ship date in the Sales Date Entry window to the dDate global variable once the user clicks on the Ok button:
For this project, I created a stored procedure in SQL server to update the site and requested ship date of all the line items in a sales order. Note that it requires the document number, new site ID, and the new date to be passed into it as parameters. This will all be done with our VBA!
I then double-clicked on the SalesTransactionEntry object and added code to the SalesTransactionEntry window’s Save button to assign the new site ID to the sSite local variable and the order number to the sDocNumber local variable (no need for a global variables since these items are in the header of this window and are not coming from another window). I created an adodb.recordset and use that recordset to execute the stored procedure passing the local variables as well as the dDate global variable into the parameters declared in the stored procedure. By executing the stored procedure, SOP10200, which is the GP table that contains the sales order line items, is updated with the new site and new date for all line items in the sales order document number that was passed to the stored procedure.
I changed the requested ship date to 4/12/2017 and the site ID to WAREHOUSE and then saved the order.
When I re-open the order, I see that the line items now show the new site ID and requested ship date:
Once I tested the custom coding and saw that it worked successfully, I needed to deploy the package to GP for general use. Since these windows are classified as “Form with VBA”, the deployment is fairly simple and straight forward.
To create the package, you will select the Microsoft Dynamics GP Tools > Customize > Customization Maintenance:
This opens the Customization Maintenance window. Click on the items necessary for the code to run; in this case, I selected the Globals Module, the ADODB references, and the two forms that were customized, SOP_Entry and SOP_Date_Entry. Once you have selected the necessary items, click on Export:
A window opens where you can specify the name of the package and where you want it stored. Select the name and location and click on Save:
Close Dynamics GP at this point.
If Dynamics.forms is on a network location, you must make sure that everyone else is out of Dynamics GP. If you are installing to a local instance (Dynamics.forms and Dynamics.dlc/Dynamics.vba files are both located on the local machine), you will not have to worry about other users. Open GP using the Run as administrator option:
Click on Microsoft Dynamics GP > Tools > Customize > Customization Maintenance and look to see if any customizations exist already for the forms you have modified. If they do, export them out in case you need to revert back to the prior version.
Select Import from the Customization Maintenance window:
This will open the Import Package File window where you can browse to wherever you stored the package. Select the package and you will see its contents. Click on the OK button and the package will be imported.
The Dynamics.form and Dynamics.vba files are now updated. If this was done locally, no other users will see the modifications in their windows. This same process would have to be done on each computer running Dynamics GP locally. If done on the network, this is not an issue.
This is just one example of how VBA customization can enhance GP’s capabilities!
This article discussed Enhancing Dynamics GP with VBA customization. For other customization needs, please reach out by filling out the form below.