These two Apps don’t always play nicely together. MYOB has a bad habit of turning off calculation in its Excel files. Here’s how to fix it quickly.
A recent update to Excel has made a slight change to the AutoCalculate feature. You can copy a value by simply clicking on it.
It is common knowledge (or it should be) that running a macro clears the undo list. In general you can’t undo a macro. However some macros also clear the clipboard which can stop you copying and pasting. I have found a workaround for the clipboard problem.
Excel has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula.
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
There are two commands you can insert at the top of your code to speed up your macros.
If you need to know how many characters are in a cell then use the LEN function. What if you wanted to know how many characters were in a range?
When you set calculation to manual in Excel you have turned off calculation. You can press F9 at any time to update the calculations. F9 doesn’t turn on calculation, it just does a recalculation.
Check boxes make selections easy. Excel has a number of useful controls that can be added to sheets to improve the user interface. These include check boxes (tick boxes), option buttons and spinner controls.