I’m designing a data entry form that uses the dynamic table as described in Professional Excel Development. It basically uses conditional formatting to “open” a data entry line when the one above is has something in it.
I want to put a command button on every line so the user can optionally do a special calculation. It will only be used on less than half of the data entry lines, but it has to appear on all of them. Since more than half of the lines will be hidden initially by conditional formatting, I need a way to hide the buttons for those lines. One of the reasons I don’t like using all those buttons is I don’t want to write the code to manage them and writing code to hide them would only add to the problem.
One solution I came up with was to simulate a command button. I sized the column appropriately and formatted the cell to look like a button. I created a custom color to closely match a button from the Forms toolbar and set that as the pattern. I then used another technique from PED by applying white and black borders to give the raised effect. The top one is the fake button, but I’m sure you could tell.
The last step in completing the simulation was changing the cursor and maintaining the proper cell selection. Since my fake button was really just a cell, that cell would be active when I clicked the “button”. Not good. I made a rectangle that fit the cell exactly and placed it over the cell. Then I set the properties of the rectangle to No Line, Transparency=100%, and Move and Size with Cells. I can then assign a macro to that rectangle and simulation is complete.
The benefit of this is that when conditional formatting is applied to that fake-button cell, it disappears. The rectangle is still there and is still clickable, but I hope the rest of the UI design discourages the mouse from wandering down there. Wishful thinking, I’m sure.
In the end, I’m going to have 60 shapes on my sheet all pointing to the same macro. I’m not too keen on having so many shapes. It’s not that I suspect something will go wrong, it’s that I don’t suspect it. That’s a sure fire way to guarantee something goes wrong.
The other options I considered are using a menu item instead of a command button on the sheet. That’s sure easier to manage, but I like the contextualness of the commandbutton, so there’s a trade-off there. Also, I could use one real command button and move it based on the data entry cell the user is in. Other than having the worksheet’s selectionchange event fire continuously, that’s not too bad of an idea. Since most of the rows will be “unavailable”, the user won’t be selecting those cells so the button won’t be moving to unintended rows.
I’m not particularly happy with any of those solutions. If any of you have dealt with the same issue, I’d love to hear about how you solved it.