|
New! Excel User Group Now Open @
www.excelusergroup.org,
Check It Out!
|
Paste Multiple Columns To One |
| Pasting multiple columns to one
appending them to the bottom of the first
column, deleting the old. This code takes the number of
columns and copies each, pasting the values and
appending them to the bottom of the first
column, deleting the old columns. (Written in
XL2003 but should work in all versions post 97.
It demonstrates the use of the Range object and
one way of ascertaining safely the last row or
column in a worksheet by going to the
bottom/right, up/left. This route is
generally more reliable than some other methods
which may struggle with non-contiguous ranges or
files where Excel has not has it's UsedRange
property manually or programmatically reset. |
|
Download File |
|
Using a Pop-Up Calendar |
| Demonstrates the use of the Microsoft
Calendar Control which will appear when the
sheet is double-clicked and then drop the value
selected from the calendar into the cell that
was double-clicked. (To run this code you must
have set a reference to the Microsoft Calendar
Control in the VBE (See
here) |
|
Download file |
|
Scroll all worksheets to top & left |
|
If you have a tidy mind, this
code is for you! This demonstration shows how
you can implement code to scroll all worksheets
in a workbook to their top left position. It
works on worksheets only (Not chart or macro
sheets) and does not save the workbook.
Scrolling and changing the selection in a
workbook does not mark the workbook as 'dirty'
and therefore requires no saving. If you
wish this as a feature, simply add...
ActiveWorkbook.Save
As the last line of code
before 'End Sub' |
|
Download file |
|
Display workbook built-in and custom properties |
|
Every workbook contains
properties that are created by Excel (built-in),
such as author, size, creation date, etc and
those that can be user entered such as title,
manager, subject, company. This workbook
demonstrates the reading of these onto a newly
inserted worksheet in the workbook. It is fired
as the workbook is opened by the workbook_open()
event. |
|
Download file |
|
Write worksheet comments to text file |
|
This file uses the VBA output
routines to take comments in a workbook and
write them out to a text file. It appends in the
text file the sheet and address from whence they
came. There is also a routine to copy comments
in one sheet to the same cells in another. |
|
Download file |
|
Insert Tick on Click (Worksheet_SelectionChange()
Event) |
|
This file demonstrates the
use of the worksheet_SelectionChange() event. It
fires each time the selection changes in a
worksheet, letting you know the new cell
selected. This routine sets a tick mark in cells
D or E and then moves the selection to column G
in the same row as selected. Clicking on a cell
that already has a tick removes it. To find how
to implement or view this code
click here. |
|
Download file |
|
Create Table of Worksheet Names (with
hyperlinks) |
|
This file creates an 'Index
Sheet' and then lists the sheets in the
workbook, including number, name and type
(Worksheet, Chart Sheet, Etc.). It also add a
hyperlink to the sheets providing they are
worksheets, xl4MacroSheets or
xl4IntlMacroSheets. You cannot hyperlink
to a chart sheet or dialog sheet and the code
will tell you this below the index in red text
if either a chart or dialog sheet exists.
Re-running the code re-builds the index |
|
Download file |
|
