|
|
New! Excel User Group Now Open @
www.excelusergroup.org,
Check It Out!
|
|
|
| Opening Excel how YOU want
it! |
|
Excel ships with a relatively customisable interface, particularly via
the Tools>Options... menu and
the Tools>Customize...
menu. Most things in these two areas is persistent despite the workbook
opened and, of course, most settings you make in a particular workbook
are saved and re-opened with it. However, you will often find yourself
making many settings regularly to suit your own purposes. If you would
like these to always be the settings you need to set up two
Book.xlt - This should be a workbook, saved as an Excel
template called book.xlt, with all the settings you want made to it,
number of sheets, page headers/footers, styles, etc. This
should be saved to a special folder, (which may be hidden), called
XLStart.
This folder is set up during Excel installation
and anything in here is started when Excel is. The book.xlt
file will start, instead of the 'built-in' version with Excel's
default settings.
Sheet.xlt - This should be a single-sheeted
workbook, saved in the same way and to the same folder, but with the
name sheet.xlt. This should have all the changes made to your
book.xlt. This will ensure that any sheets added to a workbook
will have the same look and feel as the main template.
|
| Resetting Excel's 'UsedRange' |
|
All versions of Excel prior to XP have an 'issue'
with resetting the area it believes it is using on any sheet
(UsedRange) (XP can be reset by simply saving the workbook). This
is brought about by say, entering data in columns to the right of the
actual data or rows below it. To test where Excel believes it's
furthest row or column is, simply move the scroll bars fully to their
right or bottom. Alternatively, you can use the keyboard shortcut Ctrl +
End. If this is further than you anticipated, then you need to reset it.
(for this example we will pretend the actual data is in A1:Z100, but
Excel thinks it has data in A1:AD300)
-
Select the entire rows from 300 to
101 and either, right-click on any selected row label and select
Delete or go to Edit>Delete. It is important you use delete,
not clear or pressing the delete key on the keyboard. Excel
appears to do little, but it has actually deleted the rows 101 to
300.
-
Select the entire columns from AA
to AD and either, right-click on the selected column label and
select Delete or go to Edit>Delete. Again, it is important you use
delete, not clear or pressing the delete key on the
keyboard. Excel will delete the columns between AA and AD.
-
Save the workbook. This part is very important,
nothing will happen until you have done this. In versions prior to
97 you will also have to close Excel and re-open it.
If you run the scroll bar or Ctrl + End test again,
you should see Excel now stops at the actual final row/column. |
|
Removing the macro security warning. |
|
Many times after macros have
been recorded or written users want to remove
them as they are no longer required, but they
cannot get rid of the 'macro warning' pop-up
that appears each time the workbook is open,
(see below).
(You should only follow these instructions if
you have no further need for the macros or
UserForms!).
Excel has the ability to put
code in three places, (see
here). If the code is in a standard module
the key to stopping the dialog is to remove the
entire module(s), not simply delete the code in
it. To
do this:
-
Open the VBE (Alt+F11)
-
In the
project explorer
look for the name of the workbook.
Below this you should see a folder called
'Modules'. Right-click on any modules below
this folder and select 'Remove [Name of
module]...'
-
Unless you specifically
want to save the code contained within the
module, answer 'no' to the dialog offering
export of the module
-
Once this is done with
all modules, you can close the VBE and save
the workbook.
-
You may also have a
folder called 'Forms'. If this is the
case follow the same instructions as above
on any forms contained in this folder.
-
Close and re-open and all
should be well
Unless...
You may also have code
written behind 'objects' in the workbook
providing 'event' code. (see
here), or possibly UserForm(s) with controls
(buttons, combo boxes, etc), that can also
contain code. If this is the case, you simply
need to delete the code as you cannot delete the
module(s) that contains them. To do this:
-
Open the VBE (Alt+F11)
-
In the
project explorer,
locate the name of the workbook and below
this you will find the objects in the
workbook, (e.g. Sheet1, Sheet2,
ThisWorkbook, etc).
-
Right-click on each
object in turn and select 'View code'
-
A window will open.
Any code lines in here should be highlighted
and deleted.
This should be a route to
eliminating 99% of areas where code may hide and
trigger the macro warning. |
|
Showing Excel's built in dialogs in VBA |
|
Certain things in Excel are made easier by
wizards and dialogs. Most of the things
accomplished by these wizards and dialogs can be
fulfilled in other ways in VBA. If your
desire however is simply to point the user in
the direction of the dialog or wizard, you can
show these very simply by using the 'Show'
method of the 'Dialogs' property of
Excel's Application object. For example to
display the 'Paste Special...' dialog you would
use the following code. Sub ShowPasteSpecialDialog()
Dim bReturn As Boolean
'Displays Paste Special... dialog
'Dialog varies dependant on contents of
clipboard
'Show the dialog and return True if user clicked
OK and False if Cancel
bReturn =
Application.Dialogs(xlDialogPasteSpecial).Show
'if user cancels display message box
If bReturn = False Then
MsgBox "Why did you cancel me?", vbOKOnly +
vbExclamation
End If
'If user doesn't cancel, dialog operation is
completed
End Sub
For a list of dialogs that are available go
to 'dialogs' in Excel's VBA help (Alt+F11>Help). |
|
Forcing Excel to show numbers that appear as
text. |
|
Quite often, particularly when importing from
external programs as text data, Excel will have
a problem recognising numbers and dates.
This is because it has decided they are text and
set their formatting as so. When this
happens it is impossible to do calculations on
them and perform functions such as grouping
dates in a pivot table or using them in LOOKUP
type formulae. You will notice when this occurs
that you can normally select a single cell and
click in the formula bar (press F2) and simply
enter and Excel appears to then recognise it.
Obviously on an import you will have many cells
and selecting each one is impractical. To
correct this en-masse, try this.
Enter a 1 in a blank cell and copy it. Select
your data. If this data has formulae or
other special data, you may wish to try the menu
options, Edit>Go To...>Special...>Constants and
select 'Numbers', this will select only the
numeric type data although in severe cases this
too will not recognise the numbers. (In this
instance you will need to select the
columns/rows of data manually).
Go to
Edit>Paste Special...>Values+Multiply.
This will try to multiply your 'text'
numbers/dates by 1, which of course does nothing
except Excel will now recognise them as
numbers/dates. If you have dates or times in the
data, these will now be showing their underlying
'serial' number and will need to be re-formatted
as desired.
NB: Using a zero and
Edit>Paste Special...>Values+Add should work
much the same. |
|
Error:
Cannot Shift Objects Off Sheet |
Excel is poor at resetting it's UsedRange. This
is the area on the grid that is being used by
data. e.g if you have an entry in A1 and D100,
the used range would be A1:D100, in other words,
all the cells forming this block.
If you have had data or objects such as charts,
shapes, etc on the sheet and then proceed to do
lots of inserting of rows or columns, you are
always moving this 'data' closer to the
extremities of the worksheet (Row 65536 or
column IV in versions up to 2003, further in
2007 up). When Excel believes this 'data' is
going to be pushed off the sheet, it throws the
error and stops you doing it.
To correct you need to manually reset Excel's
UsedRange. To do this highlight the last row in
your worksheet. Do this by clicking the row
label to select the entire row. (See Image) once
you have done this, scroll up to you last row of
actual data and, whilst holding the shift key
down, click on the label of the first blank row
after this data. Now, right click any of the
selected rows and on the context menu, select
'Delete'. this appears to do nothing, but it
will have done.
Select the whole row,
by clicking the row
If the columns have been extended in the same
way, repeat the steps above (as rows).
NOW SAVE THE FILE! This is most important, the
UsedRange will not reset until you have. (In
very early editions of excel >97, you also need
to close and open Excel, but this step is not
needed in later versions).
Your issue should now be fixed. |
Top |
|
Copyright Nick Hodge 2008. All Rights Reserved. |
|
|
|
