How to Display Stock Details in a UserForm from a Selected Cell using VBA
Question details
The user wants to automatically trigger a UserForm that displays related stock records (item, location, warehouse, closing quantity) from a separate Stock sheet whenever an "Allocated Qty" cell is selected.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Managing inventory where quick access to detailed warehouse records is needed without disrupting the currently filtered view of the allocation sheet.
- Observed behavior
- The goal is to trigger a macro that reads the item code from the active row, searches a structured stock table, and populates a UserForm with matching data regardless of active sheet filters.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer tools and macros in your spreadsheet application settings.
Use Worksheet_SelectionChange Event and a UserForm
Use a VBA event macro tied to the allocation sheet to detect clicks in the "Allocated Qty" column, then pass the item code to a custom UserForm.
By utilizing the Worksheet_SelectionChange event, you can make the application listen for specific cell clicks. When the target cell is selected, the macro fetches data from the backend Stock sheet and displays it dynamically.
Using structured table references ensures that even if your allocation sheet is heavily filtered, the backend lookup on the Stock sheet remains unaffected and accurate.
Press Alt + F11 to open the VBA Editor. Ensure you have created a UserForm (e.g., UserForm1) and added listboxes or textboxes to display the retrieved item details.
In the Project Explorer, double-click the specific Allocation sheet where the clicks will happen. Add a 'Private Sub Worksheet_SelectionChange(ByVal Target As Range)' event to this module.
Use the 'Intersect' function to ensure the macro only runs when a cell within the Allocated Qty column is clicked (e.g., If Not Intersect(Target, Range("AllocatedQtyColumn")) Is Nothing Then).
Read the corresponding item code from the active row using 'Cells(Target.Row, "ItemCodeColumnIndex").Value' and assign it to a variable.
Write a VBA loop or use the 'Find' method to search the Stock sheet table for the item code variable, retrieving the matching location, warehouse, and closing quantity data.
Assign the retrieved stock values to the controls on your UserForm, then execute 'UserForm1.Show' to display the details on the screen.
Manage Inventory and Macros Seamlessly with WPS Spreadsheets
WPS Spreadsheets fully supports VBA macros, making it exceptionally easy to create custom UserForms, automate repetitive inventory lookups, and handle massive stock databases without lag.
- 1. Enable Developer Tools: Open WPS Spreadsheets, navigate to 'Options', select 'Customize Ribbon', and check the 'Developer' tab to make it visible.
- 2. Access the VBA Environment: Go to the newly enabled Developer tab on the ribbon and click 'Visual Basic', or simply press Alt + F11.
- 3. Build Your UserForm: Click 'Insert' > 'UserForm' in the VBA Editor and drag UI controls like ListBoxes and TextBoxes to design your stock display interface.
- 4. Insert Automation Code: Paste your Worksheet_SelectionChange event code into the specific sheet module to automate the data lookup upon cell selection.
Frequently Asked Questions
Why isn't the SelectionChange event triggering when I click a cell?
Macros might be disabled in your workbook. Ensure you have enabled macros in the Trust Center or Developer tab. Additionally, check that 'Application.EnableEvents = True' is active in your VBA environment, as previous interrupted code might have turned events off.
Will filtering the allocation sheet affect the VBA search on the Stock sheet?
No. As long as your VBA code explicitly references the ranges or structured tables (ListObjects) on the backend Stock sheet, filters applied to the active front-end viewing sheet won't interfere with the data lookup.
How can I populate a ListBox with multiple warehouse records for the exact same item?
You can use a 'For...Next' or 'Do While' loop in your VBA code to iterate through the entire Stock table. Whenever a matching item code is found, use 'ListBox.AddItem' to sequentially append the location, warehouse, and closing quantity to the UserForm's list.
How do I clear the UserForm data before loading a new item?
Before running the loop to populate new data, use 'ListBox.Clear' or set 'TextBox.Value = ""' in your VBA script to reset the controls. This prevents records from previous clicks from stacking up in the UserForm.
