If you want to enter data in an Excel worksheet, while keeping the data sheet hidden, you can create an Excel UserForm.
I’ve updated my sample file, so you can now add new parts, while entering data. It’s almost working the way it should, but I’m stuck on one step, so if you have a solution, please let me know!
[Update: Problem solved with a workaround — see below.]
Select a Part from the ComboBox Drop Down List
In the sample file, you can click the Add Parts Information button, to open the UserForm.
Then, select a Part ID from the combo box drop down list.
The Parts List
On another sheet in the workbook, there are two lists – Location, and Parts. These are dynamic named ranges, and will expand automatically, as new items are added to the lists.
Add a New Part to the List
In the latest version of the sample file, you can add new parts to the list, while you are entering data in the UserForm.
- If the Part ID that you want is not in the list, type it into the Part ID combo box.
- When you press the Tab key, to move to the next control, a Part Description text box will appear.
- Enter the description, then fill in the rest of the data.
- Click the Add This Part button
Select the New Part
After you click the Add This Part button, the new item is added to the Parts List, and the Parts list is sorted A-Z.
The new item now appears in the Part ID combo box drop down list.
My goal was to have the Part Description activated, as soon as it was made visible. However, the VBA code wouldn’t cooperate, so I’ve commented out this line:
If you have a solution for getting that line to work, please share it in the comments, or send me an email. I’d appreciate it!
Update: Thanks to JeanMarc, Jon and Dave, the tab order is working now.You can see their suggestions in the comments below.
- Instead of being hidden, the Parts Description textbox moves to the far right, so it’s not in the visible part of the form, then moves back when needed.
- To keep the tab key from stopping on the “off form” textbox, its position is checked. If it’s at the far right, go to the next control.
Download the Sample File
To see the sample file, and check the Excel VBA code, you can download the file from my Contextures website. On the Sample Excel Files page, in the UserForm section, look for UF0017 – Parts Database with Updateable Comboboxes
The file is available in Excel 2007/2010 or Excel 2003 format, and zipped. It contains macros, so enable those if you want to test the UserForm.