March 18, 2014

Customer Inventory Query Interface using Userform

We’ll create a customer inventory query interface using a Userform and an Excel worksheet.
In a worksheet called query we write the title of our store and then create an image using a rounded rectangle that will be assigned to the following macro and open the user-form when clicked:
Sub DriveQuery()
Load UserForm1
UserForm1.RedOptionButton.Value = True
UserForm1.ListBox1 = “Adata”
End Sub

The user-form that opens will have a list box whose RowSource property will get the data from the Stock Worksheet from the table array A3:C13. The ControlSource of the ListBox will be the cell F2 again from the Stock worksheet. Both these data will be used in the Stock worksheet in a Vlookup function. The userform will have two option buttons to represent the colors of the backup drives inventory. The OK command button will fetch the number of pieces available of a specific manufacturer in a particular color. The Cancel button will hide the userform. Although the Userform may look simple, it can be easily modified and coded in Excel VBA to do more interesting things. The Kiosk thus created for the customer is quite useful.
The code for the OK and the Cancel command buttons is given below:

Private Sub CommandButton1_Click()
If UserForm1.RedOptionButton = True Then
Range(“Stock!F3”).Value = 2
Range(“Stock!F3”).Value = 3
End If

no_of_drives = Range(“Stock!F4”).Value
If no_of_drives > 0 Then
MsgBox “We have ” & no_of_drives & ” drives in stock”


MsgBox “Sorry, that drive is not available at the moment”

End If

End Sub

Private Sub CommandButton2_Click()
End Sub

Watch the training video below to see how an interesting kiosk is created with two worksheets and a simple Userform using Excel VBA:

Further reading:
Using Macros in Excel 2003