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.

Default Lookup Sort Order.zip

Comments (2)

  1. patrick roth - developer support says:

    You shouldn’t rely on focus going to a particular spot- in this case the ASILUFindString field.

    Instead if you use the window after activate event it’ll work as it’s always last in the call chain.  As a bonus, the code for any lookup window will be the same (except for the field to sort on) so it’s more reuseable.

Skip to main content