VBA - Default Lookup Sort Order Example
The following VBA example shows how you can set a lookup window to open with a default sort order. The reason that this is normally difficult is that the usual methods that people use to set defaults with VBA do not work on lookup windows.
Normally, you could use the Window_AfterOpen() event to set a default value. This is because the Window_AfterOpen() event is usually the last script run when a window is opened and your script will run after the window is populated and just before control is returned to the user.
This does not work for lookups because lookup windows are opened as one step and then populated as a second step. If you used the method above, your VBA code would be overridden when the window is populated. The timing of the execution of the VBA code is wrong.
So, how can we get the timing right.... The last thing that happens when a lookup window is opened and populated is that the focus is set to the Find field. So using the ASILUFindString_AfterGotFocus() event, we can get the timing to run our VBA code after the lookup is populated. To ensure that we only change the Sort order once, we use a boolean OpenFlag variable which set when the window is opened and cleared after the first time we change the Sort order.
You need to add the lookup window to VBA and then the Find field and the Column Button to press to change the sort order to VBA. This example changes the Vendor Lookup to sort by Vendor Name by default. The script below refers to Creditor Name as it was built on an international English system. This will not stop it working for you.
Option Explicit
Dim OpenFlag As Boolean
Private Sub ASILUFindString_AfterGotFocus()
If OpenFlag Then
OpenFlag = False
CreditorName = 1
End If
End Sub
Private Sub Window_AfterOpen()
OpenFlag = True
End Sub
NOTE: If there is no Column Button for the additional sort you want to use, you can also use the SendKeys VBA command to send the key presses to open the View menu and select a sort order.
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.