Customer Query Interface using Userform

Home More Excel (VBA) Training Videos

We'll create a customer 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 usefule.
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:

Watch this video on YouTube
Home More Excel (VBA) Training Videos