Conditionally Formatting Command Buttons

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.

cell made to look like button next to forms toolbar button

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.

Posted in Uncategorized

11 thoughts on “Conditionally Formatting Command Buttons

  1. When I first started writing Excel Apps, I used Command Buttons.
    After a couple of solutions, I identified a growing problem. I realised that the Command Buttons would be in a different layout each App. That meant that users would need to become familiar with the placement of buttons for each App – something I was not satisfied with and I imagine would frustrate users during that crucial first-impressions stage.

    I settled on CommandBar Buttons (Toolbars)

    I set up Workbook Activate and Deactivate events to build / kill the toolbar so that it’s only available when the workbook is active.
    They can be rebuilt or killed when context changes (such as workbook deactivate or on_selection_change) and are always available, even when the selection is not visible.

  2. Using all of those shapes is just asking for trouble.

    If there’s any way that you get use a single button, that’s the best approach I think. In fact, I was going to suggest something similar to what Rob proposed. The only problem, of course, is that it won’t be compatible with Excel 12.

  3. In Excel 12 (at least according to Beta 1), all custom toolbars are displayed in a single row in the ribbon — but only when the Add-Ins tab is selected. Custom toolbars can no longer be “floating.” For example, if you have several apps that display custom toolbars, you may need to scroll the ribbon to see the toolbar button you need. In other words, it’s a MAJOR departure from previous versions. And (IMHO) it’s a significant and unnecessary step backwards. MS had a great concept going with CommandBars. Then they abandoned it because it didn’t fit into the new UI.

    On the one hand, old apps will (officially) be compatible — because the custom toolbars and UI mods will still “sort of” be available. But on the other hand, the compatibility is pretty poor, practically speaking.

    This is what’s being reported in the Microsoft O12 blogs. But, of course, all this may change in the final version. At least I HOPE it will change.

  4. That’s nasty…. it’ll mean significant re-work, for sure.

    Is it possible to set up your own tab – like “Rob’s Tab” which sits next to Add-Ins?

    What do you think is a good replacement? Task Panes?

  5. I just want to echo Rob’s question. Does anybody know if it will be possible to create custom tabs? That would seem to be an acceptable analogy to existing custom menus. Otherwise, I’m going to be upgrade-averse, even with all the other great new features in XL12.

  6. would a hyper link work?, you could write a proc to show/insert a hyper link, which then called your macro. The other thing is colud you do the whole “ok” bit at the end or must it be done after each line? Or maybe a smart tag?

    I wonder is you could set the last cell of each “row” as a member of a range, then have code to put a shape next to the last existed cell of that range on each selection change – the issue here would be that the user has alreadly moved to the next “row”, or if you went of last but one, it would be there before completion of the “row”

  7. You could add a command to the Cells context menu. Many users are always right clicking to see what’s available for the selected item, so why not use this to your advantage?

  8. Dick
    You could program the double click event and apply it to the row that was double clicked.
    If some lines have calcs and some don’t then maybe they are different enough to go on different forms?

    I agree about avoiding too many shapes – that will end in tears

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *