Just Build the Damn Form on the Sheet
Since Excel userforms are such a pain in the ass, I just build the damn form on worksheet.
The biggest issue that this causes, and the reason I wanted to use the userform in the first place, is that it’s difficult to specify the tab order, meaning when a user is filling out the form and hits the TAB key, the selection should jump to the next cell they would logically fill out.
The solution requires a bit of creative design, but Excel’s functionality can be leveraged to make this possible.
First, the cells to be filled out need to be laid out so that the order they would be filled out flows first from left to right across rows, then from the top down. That means, for example, if there are 2 fields for first and last name, they should be set next to each other on the same row, rather than set vertically. This imposes a constraint on the design, but with some creativity it can be overcome.
Next, select every cell on the worksheet and ensure that they are locked (in the format cells dialog box, on the protection tab, check the box to lock the cell when the worksheet is protected). Once all the cells are locked, every cell that needs to be filled in can be selected and unlocked. Now, when the sheet is protected (with a password of course), only the unprotected cells can be selected or filled in by the user.
Done.
