VBA has a lot of limitations built in to it to make development frustrating, couple that with Sage 300’s closed door policy on any meaningful development support and I end up spending a lot of time figuring out how exactly I can modify the UI so that I can give the customer the solution they want.
This is going to be a multi-part post (I’m not sure how many it will take) to provide general instructions on how to create a custom Sage 300 UI based off of an existing UI.
VBA is the obvious starting point since recording a macro is built into the Sage desktop experience. While it is easy enough to record repeatable actions you may also need to have some sort of UI to accept parameters. How do you leverage an existing screen to provide a consistent experience? Well read on and you will find out.
Getting a Sage 300 UI:
I am going to assume you already have recorded a macro and would like to add a custom form or existing Sage screen to it. First we will simply edit the macro from the Sage 300 desktop (this is the only way to edit a Sage macro). You will immediately notice that Sage’s record function simply generates a bunch of code that is put into the module MainModule. We can leverage this to load a userform, but first we need to add one!
You can open the above menu by right clicking the Project. So now we have a custom form and can execute the recorded macro by pasting the code in MainSub in MainModule into the click event of a button on the form. This may work in the VBA editor since you might be able to force load the form rather than the module, but if you try to run your macro from Sage then the recorded macro will simply run without opening the form. To open the form instead of running the module we need to modify the code for MainSub to:
Sub MainSub()
'
' Sage 300 Macro file: C:\Accounting\ACCPGM\Macros\ARcustRPTnt.avb
' Recorded at: Wed Dec 13 10:17:53 2017
'
UserForm1.Show
On Error GoTo ACCPACErrorHandlerExit Sub ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long
If Errors Is Nothing Then
MsgBox Err.Description
Else
lCount = Errors.Count If lCount = 0 Then
MsgBox Err.Description
Else
For lIndex = 0 To lCount - 1
MsgBox Errors.Item(lIndex)
Next
Errors.Clear
End If
Resume Next End If
End Sub
Awesome, we now have a VBA macro that opens a custom form. However, perhaps we want to customize rather than recreate an existing Sage screen. Well the first thing we need to do is load an existing screen into the VBA form. To do this we need to do a couple of things. Make sure you are viewing your new userform in object view rather than code view then open the toolbox via the View menu if it is not already open.
Now lets add references by opening Tools > References… and add at least the references shown below.
Now we need to add the control, but in order to do so we need to figure out the special code Sage uses to refer to a particular screen. We can easily find this out though by going to the Sage desktop, switching the view to Details and noting down the Details of a particular screen we want to use.
Finally we can use this code to add a control using Tools > Additional Controls… to add the control to the toolbox (Make sure you are on the userform object view with the toolbox open!). To select the right control simply follow the pattern Accpac[sage screen code].ACCPAC[sage screen code]UICtrl, replacing the square brackets and the
text within with the correct Sage screen code.
Well that is all for now. I will get into customizing a Sage screen in a VBA macro in my next post.
