Hands-on - Spreadsheets - On the button

Did you ever write a macro on the spur of the moment, give it a short-cut key and then later forget the keystroke combination? Or forget that you can see it listed by pressing Alt+F3? Then why not create a button for it on a toolbar? It's easily done.

In the most recent versions of Lotus 1-2-3, tools are called SmartIcons and comprise two files: a Windows bitmap (BMP) file containing the picture on the button, and the macro to be run. If you haven't prepared a LotusScript macro before, see the box (below). Your new SmartIcon can be added to any of the existing toolbars, or you can create one of your own.

On the File menu, choose User Set-up, SmartIcons Set-up. The SmartIcons Set-up dialogue box appears (Fig 1). Click the Edit Icon button at the bottom. In the Edit SmartIcons box there is a blow-up of a blank SmartIcon (Fig 2). Above it are two drop-down palettes.

Clicking on the blank button adds a pixel of colour. There is no difference in using either mouse button to do this. They just give you a choice of colours. If you assign one mouse button to have the same light-grey as the background, you can use it to erase pixels coloured with the other one.

You can add more colours by saving the file and choosing others. If you want to erase your design, just click the create a New Blank Icon button.

Handily, to the left of the palette buttons, there is a normal-sized icon which will show how your design develops as you go along.

Although you cannot edit existing SmartIcons, you can use one of them as a starting point and edit it to create a new icon. Or, you can start from scratch. When your design is complete, enter an explanation of the associated macro in the Description text box. In Fig 2 we have used, 'Introduction details'. This will appear as a bubble description when you hover the mouse over the completed SmartIcon (Fig 3).

Then click the Save As button and give your new icon a file name. This name will appear at the top of the Edit Smart-Icons dialogue box if you select the icon again. Click the Attach Script button, then select your Script name. In our example (Fig 3) it's Intro.

Click Attach, then Done, then OK.

All you have to do now is add the new SmartIcon to a toolbar. Go back to the SmartIcons Set-up dialogue box. On the right-hand side there are lots of options. You can choose an existing toolbar or make a new one.

When you make your choice, the toolbar will appear at the top of the box. You can determine if this toolbar will be displayed when you have a worksheet, chart, drawing or other environments displayed, or at all times. You can set the size of the icons and whether a bubble-description will appear. After you have made those changes, or have accepted the defaults, drag your new SmartIcon from the Available Icons on the left, to the toolbar at the top. If you wish to remove icons from this toolbar, just drag them up and away and out of the box.

Your new SmartIcon will appear on the same toolbar in other workbooks but if you click it you'll receive an advisory message that the workbook in which you created it also has to be running.

Next month, we'll look at the comparable features which Excel offers.

- Maths and paths

Two readers have sent promotional messages for products. I don't have the time or space to offer comprehensive reviews but I'll mention them.

You can get more details and see demonstrations on their web sites, detailed in the PCW Contacts box, below.

Reader Barry Phillips tells me about the Cristall Math Solver: 'It works on four principles: Identify variables, which can be measured in terms of numbers like income, expenditure, profit, cost, grades of quality and enumerated results. Establish rules, or equations, which relate to the variables.

Decide which variables have known values. Finally, within the normal algebraic rules of solubility, calculate unknowns. To do this, the software uses advanced techniques in equation solving. Cristall comes with a number of standard models including Financial Forecasting, Costing, and Resource Utilisation.

We have case studies including one in which 56 linked spreadsheets, relating to ambulance service management, were brought into one Cristall model.'

And reader Andy Wiggins has sent details of Byg Software's T.E.A. (The Excel Auditor). It is an Add-In which provides an audit map, generates lists of dependent cells, traces circular references, provides ordered lists of sheet and range names, searches for date formulas, and more.

QUESTIONS & ANSWERS

Q. How do you add a symbol to a cell within Excel 97? The example I was given was if someone wanted to add a tick mark within a single cell. Is there a way?

Mark Flynn

A. Probably the easiest way is to format the cell for the font, Monotype Sorts, and then enter a 3 for a light tick, or a 4 for a bolder one.

Q. When we download from our mainframe, dates get passed to Excel as numbers. So, 1st Feb 99 appears in a cell as 10299. Similarly the 10th Sept 99 would appear in our download as the number 100999 - and when you format this as a date it goes to 9/7/76. It's a wretched nuisance for us as we do not want to change all the downloads - it is a nightmare, and I had hoped Excel would be able to hack it.

Peter Blundell

A. I suggested to Peter that he use the Text Import Wizard, but then checked with spreadsheet guru Shane Devenshire and he added: 'If when he imports the file the Text Import Wizard opens up, then at the third stage of the Wizard he will get a Column Data Format option box. On this screen he should select the column with the dates, shown in the Data preview window at the bottom of the screen. Then he should pick the Column data format option of MDY or DMY from the drop-down beside the Date option. (The choice will depend on what 10-09-99 means - October 9, 1999 or September, 10, 1999.) If the file opens directly into Excel without the Text Import Wizard appearing, then he should select the column in Excel with the dates and choose the command Data, Text to Columns and proceed as above. Both of these approaches can be recorded as VBA macros, so if this is a task which re-occurs, he can automate it, assuming that the data arrives in a consistent fashion.'

PCW CONTACTS

Stephen Wells welcomes your comments on the Spreadsheets column. You can contact him via the PCW editorial office (address, p10) or email spreadsheets@pcw.co.uk

Please do not send attached files unless they have been requested.

Cristall 01248 355992 www.cristall.co.uk T.E.A. 07957 266372 www.bygsoftware.com.