VBA Express : Excel - An example of how to use modeless userforms

Excel

An example of how to use modeless userforms

Ease of Use

Intermediate

Version tested with

2016

Submitted by:

paulked

Description:

This example uses a modeless userform to add items from a table to an order sheet in an extremely user-friendly way.

Discussion:

Very often we need to add a list of predetermined items to another sheet. Examples could be an invoice, purchase order, sales ledger etc. In this example we are adding items from a table (a list of products) to an order form. The list has 4 columns - Category, Code, Description and Price. There are numerous ways to add these products to a form, this way does not require the user to remember any codes or other stuff as all the information is presented on screen. It is also a very user-friendly way to do it as it takes the minimum of mouse-clicks and keyboard entries.

Code:

instructions for use

			 'Order Sheet module
 
Option Explicit 
 
Private Sub Worksheet_Activate() 
    frmProducts.Show 'Load Userform
    Application.MoveAfterReturnDirection = xlDown 'set return direction when user activates the sheet
End Sub 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim rw As Long, cl As Long 
    cl = Target.Column 'get target column
    If cl = 5 Then Unload frmQuantity 'Check if Qty has been updated and if so, unload reminder form
End Sub 
 
Private Sub Worksheet_Deactivate() 
    Unload frmProducts 'Unload Userform when user leaves the sheet
End Sub 
 
 'Product sheet module
 
Option Explicit 
 
Private Sub Worksheet_Activate() 
    Dim lr As Long 
    Application.MoveAfterReturnDirection = xlToRight 'Set direction after enter
    lr = Cells(Rows.Count, 1).End(xlUp).Row 'find the next available row for adding new products
    Cells(lr + 1, 1).Activate 'move to that row
End Sub 
 
 'Products userform
 
Option Explicit 
 
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) 
    Dim Orw As Long, Prw As Long, cl As Long 
    Orw = ActiveCell.Row 'get the sheet active row
    Prw = ListBox1.ListIndex + 2 'get the product double-clicked
    If Orw < 2 Then Orw = 2 'keep within the upper boundry
    If Orw > 17 Then Orw = 17 'keep within the lower boundry
    For cl = 1 To 4 
        Cells(Orw, cl) = shPrd.Cells(Prw, cl) 'fill the 4 columns from the userform
    Next 
    frmQuantity.Show 'show the reminder userform
    Cells(Orw, 5).Activate 'activate the cell that needs input
    AppActivate Application.Caption 'transfer focus from the userform to the worksheet
End Sub 
 
 'Code module
 
Option Explicit 
 
Sub NewOrder() 
    shOrd.Activate 'goto the order sheet
    Range("A2:E17").ClearContents 'clear previous data
    Range("A2").Activate 'activate a cell on the 1st row
End Sub 
 
Sub EditProd() 
    shPrd.Activate 'goto products
End Sub 
 
Sub Home() 
    shDsh.Activate 'return to the home sheet
End Sub

How to use:

Because of the nature of this example, please download the file and look at its' structure.

Test the code:

Have a play with the example file!

Sample File:

ModelessNotUseless.zip 37.57KB

Approved by Jacob Hilderbrand

This entry has been viewed 136 times.