Project Navigation
This is part two in building our Excel Fabric calculator, today we are going to work on our first user form. This post is going to be very long, and you may notice a slight change in formatting of the post itself that will make it easier to follow. Also some of the snips that are provided are not the full block of code, or have been edited to only show what is unique and relevant. If you haven’t already you will need to add at least one user form for your Excel VBA project.
For easier navigation of this post and to pick up where you left off in the project use these links to jump to the corresponding section.
Lets get started! Below is a snip of what our user form will be, each numbered item has a section with instructions on how to build out the functionality for each piece.
- Heading for the user form, the title on a tab for an application or web browser this is the first thing that should always been considered because it gives the user information as to what that tab or application is for or doing. To edit this use the property window on the user form and update the Caption property.
2. Adding a Label
Add a label form the tool box to inform the user what is being asked with the dropdown box, use the Caption property again to edit the displayed text.
If the tool box isn’t visible when editing the user form select the wrench and hammer icon from the file tray.
3. Dropdown List to Select garment
A simple way to populate a dropdown box with the options we want to provide the user would look something like this
However we are going to take it a step farther in the user selection by having the drop down box used for only selecting the base garment and create radio buttons for the different options and our dropdown box will only be for the base garment.
To populate the dropdown box we need to add the base items, this will be added to the sub routine UserForm_Initialize() to create the items once the user form is starting up.
Notice each item we add also has its index value defined. List box indexes much like arrays start counting at 0.
To simplify the user form and present a less cluttered face we will add some logic to control when the option buttons are visible. This will also help avoid things like trying to add sleeves to pants, which would possibly break the macro or simply not output a result to the user.
Within the Initialization form use a combination of the property selectors “Enabled” and “Visible” and set the value to “False” to remove the buttons we just added. To target each button on the user form we can use “Me” within the with loop. However for macros that are outside of the scope of the user form we are targeting use the user forms name for example “Frm_CalcFab”
To enable and make visible the proper buttons once the user has selected a base garment use the sub routine “ddl_pickproject_Change()” this works similar to the event listener On Change. Within this sub use a select case to get the index value of the selected item from the dropdown list, then enable the proper options. This will end up being quite a large select case statement so lets break it out a little further.
- The select case based on the index value of the dropdown list
2. Starting from 0 checking the index value – this can also be done within an equally lengthly if else statement, however select cases usually run a little bit faster.
3. Call function to disable any prior selections, this function should be placed in the functions module so that we can add to it later and reuse it outside of this user form. This function will use the value, enabled, and visible properties to uncheck, disable and remove view buttons that do not correspond to the current selection.
4. Set visible and enabled properties to true for the current selection. For buttons that are not on the first row of the user form we will also use the “Top” property to move the now visible buttons to the top so everything on the user form lines up.
5. Call the functions that we build in the next step to clear previous results from the result list box.
4. Results Print out, there are a few different ways to go about displaying results to the user, for this example we are going to use a list box and reference a sheet where the results will be held. To do this first set up a second sheet on the excel document, and create a name for it, then add the base fabric widths to one column and name the second column results.
Within the functions moduel create a new function “manualUpdateList” to update the listbox with these two columns. Within this function we will clear the prior list values, reset the settings, get and display the values populated on the result sheet.
To clear results between garment changes we will create the “clearResults” function targeting the result range values and setting the value to empty.
5. Commit/Submit
Add a button to get user selections and return the results. To do this use the button name and “_Click()” event handler.
- Assign variable to list index of selected drop down list item
- Check that user has made a selection, if the user has not made a selection display a message box prompting user input.
- Select case statement to select corresponding enabled options.
- Get options from input using an intermediate if statement. An intermediate if statement uses the following conditions IIf ( condition, if true do this, if false do this)
- Call function “retFabric” passing variable selections.
With the button click event handler we can set up the “retFabric” function to output values on the results sheet which will populate the results list box to the user. This function will check which garment was selected using intermediate ifs to get the options selected and then update the values of the result column on the results page. Finally the function will then call the update function to update the list displayed to the user.
6. **Optional – Email results
This is an option step that we can explore in another post. If you are following along in building this fabric calculator I challenge you to take this and the automated excel email post to see if you can build the function to make this button work on your own!
