The Playcentre Excel Cashbook

Installation & Usage NotesAs at 7thSeptember ’14

Contents

Overview of set up procedure…

Help and Support

Versions of the Cashbook for Windows /Vista /7 PCs

Versions of the Cashbook for Macs

Installing on Windows /Vista PCs

Installing on Macs

Windows Vista & Windows 7 Settings

Settings required for the Spreadsheet Software

Other Issues that might arise

Overview of set up procedure…

The Cashbook is installed by running a setup file (or on Macs, it’s a packed file). Choose the setup file appropriate for your computer type, operating system and spreadsheet software. Once the Cashbook is installed, you will need to set some options in the spreadsheet software to ensure that the Cashbook works correctly (pgs 3 onwards).

  1. Check what operating system and spreadsheet software are on the computer…

Use this info to select the right version of the cashbook to be installed. Refer to Versions of the Cashbook info further below.

Computer type:Windows PC? or Mac?

Operating system:Windows Vista? Windows 7 or 8? Mac OS?

Find the Windows version on the Control Panel under the System area.

Spreadsheet software:The eCashbook is designed for Microsoft Excel and works best in Excel.

Excel 2003? Excel 2007? Excel 2008 (on Macs)? Excel 2010? Excel 2013?

OpenOffice.org Calc 2.4.3 or prior?

OpenOffice.org Calc in 3.0 range or 4.0 range? (Hangs on formulas between workbooks! So not recommended)

Check the Excel version by viewing ‘About Microsoft Excel’ info.

  • In Excel 2007 & up, this is under Office Button (top left), then \Excel Optionsbutton at bottom \Resources, then About Microsoft Excel.
  • For older versions, select \Help menu tab \About Microsoft Excel.

Check the OpenOffice.org version by

  • select \Help menu tab \About OpenOffice.
  1. Get the setup file for the Playcentre Excel Cashbook…

Either…

  • Download from the Playcentre Download page on The Setup file can be copied to media (CD or memory stick) for transfer to another computer, and run there.

OR

  • Locate the file on the CD that you have requested be sent out.
  1. Install

Follow instructions provided on page 3 and then check that the options for the spreadsheet software are set as needed (refer pg 3 & 4).

Start using the Playcentre Excel Cashbook by opening the _Menu workbook.

_README_InstallationAndUse.docPage 1

Help and Support

There is helpful information provided in each of the workbooks. Please take the time to read it.

The following documentation is also provided…

  • The Guide to the Playcentre Excel Cashbook gives an overview of the main workbooks.
  • The Notes printed on the manual books provided by the NZ Playcentre Federation are available as a word document within the Excel Playcentre Cashbook.

For support on a particular issue:

  • Check that you have set the options in the spreadsheet software as detailed on pg 3 & 4.
  • Check the list of other issues on page 5.
  • Visit the Support pages at info and /or workaround.
  • Email us at (attach the workbook if possible).
  • Or phone 03-384 8779 (we can call you back, as we have unlimited toll calls).

_README_InstallationAndUse.docPage 1

Versions of the Cashbook for Windows

/Vista /7/Windows 8 PCs

Excel Version: 2000thru to 2014

The install file is setupCashbook2015.exe.

We aren’t using the standard 2007 format (.xlsx) as we cannot save workbooks that contain macros in this format. We aren’t using the macro-enabled2007 format(.xlsm) either, as this isn’t very stable.Plus we have users on older versions of Excel.

Save the files in the original format. E.g. be sure to save in Excel 97-2003 format, otherwise the files will be renamed to .xlsm and successive workbooks won’t link correctly.

OpenOffice Calc spreadsheet

The install file is setupCashbook2015.exe.

Thestandard version of the cashbook can also be used within the Calc spreadsheet software from OpenOffice.org. There are still problems within OpenOffice.org 3 and 3.1 linking information from one excel workbook to another, so you must use OpenOffice.org 2.4.3or earlier version to ensure that the Cashbook works ok. We are waiting for OpenOffice to resolve this problem in a new release (first reported it in 2009!).

Note that the short-cut keys (macros) that speed up data entry in the Cashbook, will not work in OpenOffice. Refer to the ‘Issues with Shortcut keys’ word document for a workaround.

We can send the free OpenOffice.org 2.4.3 software to you on CD or you can download from the web

Versions of the Cashbook for Macs

Excel Version: 2000 to 2014

The install file is Cashbook2015.zip.

Microsoft didnot enable macros /short-cut keys to be used in Excel 2008 for Macs. The Cashbook will still be useful to you, but will require a bit more data entry from you. Refer to the ‘Issues with Shortcut keys’ word document for a workaround.

OpenOffice Calc spreadsheet

The install file is Cashbook2015.zip.

The standard version of the cashbook can also be used within the Calc spreadsheet software from OpenOffice.org. There are problems within OpenOffice.org 3 and 3.1 linking information from one excel workbook to another at the moment, so you must use OpenOffice.org 2.4.3 or earlier version to ensure that the Cashbook works ok.

Note that the short-cut keys (macros) that speed up data entry in the Cashbook, will not work in OpenOffice. Refer to the ‘Issues with Shortcut keys’ word document for a workaround.

We can send the free OpenOffice.org 2.4.1for Macs software to you on CD or you can download from the web

_README_InstallationAndUse.docPage 1

_README_InstallationAndUse.docPage 1

Installing on Windows PCs

How to Install…

Run the installation file and step thru the install screens.

You can override the normal location of C:\Cashbk15\ during the setup process. Wherever you install the Cashbook, make sure it is in it’s own separate folder e.g. \Desktop\Cashbk15\.

A desktop icon is created to provide a shortcut to the menu for the Cashbook files.

Run Excel and check the settings needed by the Playcentre Excel Cashbook, described further below.

Opening the files…

Select the workbook that you want to work on from the Menu workbook or from list of files in the Cashbook folder (use the desktop icon to view). Alternatively, start Microsoft Excel or OpenOffice Calc and open the workbook.

Saving the files…

Save the files in the original format. E.g. be sure to save in Excel 97-2003 .xls format, otherwise the files will be renamed to .xlsm and successive workbooks won’t find them to link to correctly.

Installing on Macs

How to Install…

Unzip the compressed .zip file into a folder of your choice (e.g. c:\Cashbk15\).

Run Excel and check the settings needed by the Playcentre Excel Cashbook, described below.

Opening the files…

Select the workbook that you want to work on from the Menu workbook or from the list of files in the Cashbook folder. Alternatively, start Microsoft Excel or OpenOffice Calc and open the workbook.

_README_InstallationAndUse.docPage 1

Windows Vista & Windows 7 /8 Settings

Some folders automatically have tighter restrictions than others on a Vista computer. Depending on where the Cashbook is installed, the files may be Read Only. Alter the permissions for the folder as follows…

Locate the Cashbook folder (normally in c:\Cashbk15\), right-click and select…

\Properties \Security \Edit. Highlight the Users Group/Name and tick 'full control'.

Settingsrequired for the Spreadsheet Software

_README_InstallationAndUse.docPage 1

Excel 2010 onwards:

The functionality and menu ribbon structure of Excel 2010 closely resembles Excel 2007, so follow the Settings instructions for that. One difference to note is that the Office Button in Excel 2007 has been replaced with the original File tab in Excel 2010.

Excel 2008 (for Macs):

The functionality and menu structure of Excel 2008 closely resembles Excel 2003, so follow the Settings instructions for that.

Excel 2007:

  1. To allow links between workbooks…

It is important that the workbooks always bring in the latest information e.g. pull in last month’s figures or the name of the Centre.

\Office Button*(top left corner of screen) \Excel Options \Advanced, scroll to ‘When calculating this workbook’ and ensure that ’Update Links to Other Documents’and ‘Save external link values’.

  1. To define accuracy level for calculations…

\Office Button* \Excel Options \Advanced, scroll to ‘When calculating this workbook’ (as above) and ensure that ‘Set precision as displayed’ is ticked.

  1. To allow macros for short cut keys to be run…

The Cashbook does use macros to provide various shortcut keys e.g. the ctrl q to display help information for a Receipt category column. To allow the macros to be run…

\Office Button* \Excel Options \Trust Center \Trust Centre Settings \Trusted Locations and selectthe ‘Add new location’ button at the bottom. Browse for the Cashbook folder and enter OK to add it to the list of trusted locations. Make sure you add the folder, not the icon on the desktop!

  1. Compatibility Mode

In Excel 2007, when you open a workbook that was created in an earlier version, it is automatically opened in Compatibility Mode, and you will see ‘[Compatibility Mode]’ next to the file name in the Excel title bar. In Compatibility Mode, any new or enhanced Excel 2007 features are not available and Excel will give you a warning about this when you Save the workbook. Don’t worry about any warnings highlighted by the Compatibility Checker. Trust me, there won’t be (as I haven’t used any of the new features and all the links between the workbooks are within the constraints listed in the warning box), so just select ‘Continue’.

To turn the Compatibility Checker off , remove the tick on ‘Check Compatibility when saving this workbook’ when you save each workbook. Then Excel won’t ask you again about compatibility of this workbook.

* Office Button is the circle Office logo at top left hand corner of the Excel screen.

Excel 2003:

  1. To allow links between workbooks…

It is important that the workbooks always bring in the latest information e.g. pull in last month’s figures or the name of the Centre.

\Tools \Options \Edit and clear the ‘Ask to Update Automatic Links’ check box.

  1. To define accuracy level for calculations…

\Tools \Options \Calculate, and tick‘Set precision as displayed’

  1. To allow macros for short cut keys to be run…

The Cashbook does use macros to provide various shortcut keys e.g. the ctrl q to display help information for a Receipt category column.

Use \Tool \Macros \Security option to change the security level from High to Medium, to allow the macros to be run.

  1. To ensure the Date function is available…

\Tools \Add Ins and check Analsysis Toolpak to ensure that the EOMONTH function is available.

Excel ’97:

  1. To allow links between workbooks…

It is important that the workbooks always bring in the latest information e.g. pull in last month’s figures or the name of the Centre.

\Tools \Options \Edit and clear the ‘Ask to Update Automatic Links’ check box.

  1. To define accuracy level for calculations…

\Tools \Options \Calculate, and tick ‘Precision as displayed’

  1. To allow macros for short cut keys to be run…

The Cashbook does use macros to provide various shortcut keys e.g. the ctrl i to insert the cheque value into the payment category column.

When you open the workbook, click on ‘Enable Macros’.

  1. To ensure the Date function is available…

\Tools \Add Ins and check ‘Analsysis Toolpak’ to ensure that the EOMONTH function is available.

OpenOffice Calc:

  1. To allow links between workbooks…

It is important that the workbooks always bring in the latest information e.g. pull in last month’s figures or the name of the Centre.

\Tools \Options\OpenOffice Calc \General and under Updating select ‘Always’ for‘Update Links when opening’.

  1. To define accuracy level for calculations

\Tools \Options \OpenOffice.org Calc \Calculate, and tick ‘Precision as shown’.

  1. Excel Macros in OpenOffice.org…

The Cashbook uses macros to provide various shortcut keys e.g. the ctrl i to insert the cheque value in the payment category column.. Unfortunately these macros are not available in OpenOffice Calc but they are still held in the workbooks (in case you start using the workbooks in Excel). Refer to the ‘Issues with ShortCut keys’ document for a manual workaround for calculating GST and GST exclusive figures.

Select \Tools \Options \Load/Save \Microsoft Office and tick under [L] and [S] for Excel to OpenOffice.org Calc.

To retain the macros…

Select \Tools \Options \Load/Save \VBA Properties and tick beside Load Basic code to edit and Save original Basic code again.

To avoid getting the Macros are disable message…

Use \Tool \OpenOffice.org \Security under Macro Security select the Macro Security button and then the \Security Level tab. Change the security level from High to Medium, to allow the macros to be run.

Then in the same area, choose the \Trusted Locations tab and Add your cashbook folder to the Trusted Locations.

  1. Save workbooks in Excel format …

On your first save, click ‘Yes’ to save in Microsoft Excel 97/2000/XP file format. And tick the ‘Do not show this warning again’ box.

Other Issues that might arise

Setup file disappears

Some anti-virus software mistakenly identifies the setupcashbook.exe files as holding a generic Adware virus and may quarantine the file. Temporarily snooze your Anti-virus software, so that you can run the Setup file.

#REF appearing in cells

#REF appears in a cell when Excel cannot find another cell that is being referred to within a formula. This may have arisen because the required cell has been deleted. Or perhaps a link to another file is broken. Check that all your files are named correctly – check against the release list. No files should be called ‘Copy of …’. Best to send an email to us, attaching the file to it. We will take a look and send back.

Err:508 or other number appearing in cells in OpenOffice Calc spreadsheet

These errors normally appear in a cell when OpenOffice cannot find another cell that is being referred to within a formula. This may have arisen because the required cell has been deleted. Or perhaps a link to another file is broken. Check that all your files are named correctly – check against the release list. No files should be called ‘Copy of …’. Check that you have set up the options as detailed. Or the error may clear if you open the workbook that the cell is referring to at the same time e.g. have the Budget workbook open while you work on Mth02_Feb.xls. If you can’t resolve the problem, best to send an email to us, attaching the file to it. We will take a look and send back.

Dates wrong or displaying as ######

If your dates aren’t displaying correctly, go into your Control Panel, choose Regional Settings and choose date format of dd/mm/yy or d/mm/yy.

Values displaying as ######

The value is too large to be displayed in the cell. Reduce the size of the font being used to display the amount by selecting the cell, then …

Excel 2007 & up:Resize by \Home tab \Font and selecting smaller size e.g. 8 if the font size was 9

Prior to Excel 2007:Resize by \Format \Cells \Font and selecting smaller size e.g. 8 if the font size was 9

Copying cells (values and formulas) without copying the formatting

Sometimes you may want to copy a formula from one cell to another. Select the cell and use the standard copy (ctrl-c) command (or \Edit \Copy), then select the next cell to paste the value or formula into (ctrl-v). This will also copy the formatting of the cell eg. The font and border settings. If you don’t want the formatting copied, you can use the Paste Special (alt ctrl v) command to copy in just the formula.

(Not Responding) message from OpenOffice.org

Just be patient. OpenOffice can take a while to load all the formulas and links.

_README_InstallationAndUse.docPage 1