Note: these sheets are available in the Startup File in our store.
Controls Sheet
Controls can be confusing as a group and individually. Should I use Form controls or ActiveX controls? How do I create an Option Button group? How is a Combo Box different from a List Box? How can I get back the control’s value? What is Triplestate? MultiSelect?
This sheet is a visual companion to the Controls section in Chapter 3, which explains the two families and gives some detail about each control. Here the six main controls are grouped as Text (List Box and Combo Box), Number (Spin Button, Scroll Bar) and Logic (Check Box, Option Group). To aid familiarization, the two types are shown side by side with comments about the usage and differences.
Themes Sheet
Themes, I think, are under-appreciated. They need some marketing to show them off and explain their benefits. Here they are laid out in a way that as you scroll the Theme Gallery, you see the sheet change and learn the scope of what elements are linked to the Theme.
Themes are composed of selected colors, fonts and effects. Of course, these and all other formats are individually customizable, but these three can also be set as groups. You can select a different color set, font set, or effect set, or a different theme which combines all three. Most users don’t give a care whether they are using a theme-dependent format or not, but as a developer it’s something you should be aware of. Choose elements consciously with the following considerations.
If you stay within the themed elements, they will change if a user selects a different theme. They will maintain appropriate contrast and balance, but may change window size, cell wrapping, etc.
If you want the project to be user-theme-independent, use non-themed elements, or control it in VBA.
Avoid mixing themed and non-themed elements.
If you consider setting theme in code, record it to find the path. I found some theme files in one folder path, and others in a completely different path. If you are distributing the Excel file, consider distributing the theme file with it, and be aware of different versions.
Functions Sheet
Function descriptions and help are good and important ways to get to know what the functions do and how to use them. Sometimes I need to see examples, and I thought of a multiplication table. I can make a grid of x and y values, then pick a function from a list to display the result f(x,y).
I went through all the functions and made a list of interesting ones. Then I realized that several of them have only one operand, so I made two lists; one of single-operand functions, the other of multi-operand functions. My design was to lay out the sheet to show me a range of results of whatever function I select from each list.
The layout is a 20x20 grid, X going down the rows in column B and Y going across the columns in row 4. In column A is the single-operand column. The f(x) range and the f(x,y) range are independent of each other and colored differently for visual separation. Name Manager then defines each input, lookup and output.
The function lists are off to the right of the formula grid. The function name list serves three purposes. First it provides the dropdown menu via Data Validation. Second, using MATCH it gives the selected function an ordinal number, which is then used in CHOOSE to output the selected function, as shown below. Third, it provides function description which is displayed above the grid.
=IFERROR(CHOOSE(MATCH(fxy,fxys,0),FunXY01,FunXY02,FunXY03,...),"")
The range 1 to 20 for X and Y gives a good initial view of how the function works, but I also want to play with inputs, use negatives, or decimals, or multiples of 100 just to see what happens. X and Y each have a multiplier cell, mx and my. Then the 20 actual X values and 20 actual Y values are calculated.
20 X inputs = IF(N(mx),mx,1)*(ROW()-ROW(Y)) 20 Y inputs =IF(N(my),my,1)*(COLUMN()-COLUMN(mx))
I’ll add some code to this sheet for the Worksheet_Change event, which is activated only for the pick list cells and the multiplier cells, collectively named “controls”. Whenever one of these four cells is changed, the columns are autofit. However, this looks weird if the columns end up very different widths. Cell merging is used where the function name or description would cause an autofit column to be too wide. To set a minimum column width, I page down to pick a row and enter =REPT(“_”,8) in all the output display columns. The string of eight underscores defines the minimum autofit column width.
Comentários