Useful Excel Customizations (Excel 2000/97)

Topic

New Reply

I’m curious as to what the Excel experts out there do to their own systems.
What are the customizations that you make to your installations of Excel? For instance I think, the following VBA for a PasteList Button (saw it in an earlier post), is the cat’s meow.

Public Sub PasteLink()
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
End Sub

Once it is placed in the Personal.xls in the XLStart directory I attach a button to it use it frequently.

Any other favourites?

TC

Reply | Quote

Replies

I have this code attached to a button on my tool bar to put the Date AND Time into the selected cell:

Sub InsertDateTime()
    ActiveCell.Value = Now()
    ActiveCell.Offset(0, 1).Select
End Sub

Reply | Quote

How about this?
Public Sub AutofillRowOrColumn()
Selection.DataSeries Type:=xlAutoFill
End Sub
Simple, but satisfying. 1-2-3 has a similar button, but not Excel.

Cheers

Reply | Quote

I work with multiple sheets a lot, and have the following replacements, courtesy of Legare and others:

This code is attached to the “freeze panes” button & emulates the standard behavior but with all selected sheets:

Sub FreezePaneAcrossSheets()
Application.ScreenUpdating = False
Dim oSheet As Worksheet
Dim oActive As Worksheet
Set oActive = ActiveSheet
For Each oSheet In ActiveWindow.SelectedSheets
oSheet.Activate
If ActiveWindow.FreezePanes Then
ActiveWindow.FreezePanes = False
ElseIf ActiveCell.Address = “$A$1” Then
Beep
Exit Sub
Else
ActiveWindow.FreezePanes = True
End If
Next oSheet
oActive.Activate
Application.ScreenUpdating = True
End Sub

This code is attached to the “select print area” button & emulates the standard behavior but with all selected sheets:

Sub SetPrintAreaAcrossSheets()
Application.ScreenUpdating = False
Dim oSheet As Worksheet
Dim sPrintRange As String
sPrintRange = Selection.Address
For Each oSheet In ActiveWindow.SelectedSheets
On Error Resume Next
With oSheet.PageSetup
.PrintArea = sPrintRange
End With
If Err.Number = 1004 Then Exit Sub
Next oSheet
Application.ScreenUpdating = True
End Sub

This code has nothing to do with multiple sheets, it’s attached to the “center across selection” button & emulates the old Excel 5/95 behavior which -doesn’t- merge the cells:

Sub CenterAcrossSelection()
‘ emulates Excel 5 center-across-cells toggle
With Selection
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
Else
.HorizontalAlignment = xlCenterAcrossSelection
End If
.MergeCells = False
End With
End Sub

Reply | Quote

Hey – great Idea – I’m going to steal that immediately. They never should have attached Merge and Center to the same button as Center across Selection.

mutter, mutter. What were they thinking?
Nevermind I’ll fix it with your code.

Cheers

Reply | Quote

I deeply detest the use of merge cells because of it’s interference in selecting rows or columns which contain them.

Reply | Quote

Me too!

Reply | Quote

Great Stuff – Thanks

TC

Reply | Quote

If you work with multiple sheets a lot, maybe these two are a nice addition:

Option Explicit

Dim oSheets As Object

Sub Remembergroup()
Set oSheets = ActiveWindow.SelectedSheets
ActiveSheet.Select
End Sub

Sub GroupAgain()
oSheets.Select
End Sub

The first sets an object to the sheetgroup you have selected and then ungroups them.
The second regroups the sheets you had grouped.

Very handy when you want to quickly switch between group mode and single mode and vice versa.

Reply | Quote

1. Where were you when I needed you, and
2. Why didn’t I think of that?

These will fit perfectly with some of my projects. Thanks!

Reply | Quote

Hi John,

<>

Over at the Office forum of Compuserve:

http://go.compuserve.com/MSOfficeForum

<>

Dunno, Forest, trees? .

Reply | Quote

Next time you’re there, Jan Karel, talk Bill Manville into coming over here to the Lounge! He was the other guy along with Legare who helped me understand (to the limited extent that I think I understand) the Sheets collection object.

Regards,

Reply | Quote

One that I’ve put on my 3 machines, and it doesn’t involve any VBA, is removing the check on the “Move Selection After Enter” option. Since I don’t know which way I want to move and I may want some formatting for the cell in which I’ve just put data, I’d rather stay in the cell or use arrow keys to move.

fred

Reply | Quote

Good point on the non-VBA customizations. My toolbars have been loaded with a few things for so long I don’t remember what the defaults are. Some that I add are Set Print Area, Paste Values, Freeze Panes, Trace Dependents, Trace Precedents, Remove All Arrows (from the traces), Insert Columns, Insert Rows, Delete Columns, Delete Rows (hey, -one- click wherever possible). I also have an accounting underline macro with a custom button face, attached; the macro is very simple:

Sub AccountingUnderline()
Selection.Font.Underline = xlUnderlineStyleSingleAccounting
End Sub

I’m not sure I remember correctly, but I think the Print Preview button is in the default standard toolbar; I remove it since you can preview with shift-clicking the Print button. (I hafta make room for the added buttons.)

Reply | Quote

You probably know this but, I like Ctrl + plus key to add rows (have the row where the row is to go selected first) and Ctrl + minus key to delete rows.

Just trying to save room on that toolbar!

Cheers!

Reply | Quote

Well, darn, I didn’t know that. Starting today I’m gonna learn that one. I went back into keyboard shortcuts in Help and don’t see it documented.

(Of course, I suffer from shelf-blindness, which is, when faced with huge number of choices, I can’t see the one I’m looking for, even when it’s right in front of me. One problem with being largely self taught is that sometimes you don’t pick up these really basic tricks.)

Thanks! Got any more like that?

Reply | Quote

How about Ctrl + ~. Toggles the view to show formulas.

Cheers

Reply | Quote

With in-cell editing and a formula bar, I rarely use that one.

Reply | Quote

But if it works how I think it does, it’s great for getting a printout of your formulas …

Reply | Quote

exactly what I use it for!

Reply | Quote

I don’t know if this is a sign of my indolence or proficiency but I haven’t needed to print out formulas for years; I find with the reference color coding in edit mode, and the tracing capabilities in Excel, I don’t have a need. Interesting how we have different styles.

Reply | Quote

This feature is also helpful for making sure your formulas are consistent. Easy to see where any formula mistakes are.

Reply | Quote

How about using ctrl while dragging the corners of a 3-D chart to display the series in outline during the drag?

Reply | Quote

Keyboard shortcuts are mentioned at various points in the Excel help system, but for some reason Microsoft does not provide a consolidated list of them (or at least if they do, I’ve never found it). A few years ago I created the attached spreadsheet that shows all shortcut keys. Hope it’s helpful. In addition to the other keys mentioned, I find Ctrl+1 very useful (that’s the 1 above the letter Q).

** Added later **
To get the list in Excel 2000
.Go into Help; click the “Contents” tab
.Expand the “Using Shortcut Keys” topic (third one down on my system)
.There’s shortcut keys organised by topic (27 categories); there’s instructions there for printing the entire list.

To get a list in Excel97:
.Go into Help, click the

Reply | Quote

Many of them are found in XL97 Help in a series of “hypertext boxes” if you enter “key” in the Help “Index” dialog and select “Use shortcut keys in MS Excel”. However, I still haven’t found Ctrl+ and Ctrl- there (insert at selection, delete selection), so I look forward to reviewing your list.

Reply | Quote

The row/column delete/insert items were the first to go onto my cells shortcut menu! Of course, at this rate I’m going to have more items on that menu than I have on the main menu bars!

Reply | Quote

And here’s two more I didn’t know:

Shift+Space Select the entire row(s) of the selected cell(s)
Ctrl+Space Select the entire column(s) of the selected cell(s)

which complement WebGenii’s Ctrl+”+” and Ctrl+”-“. All the above soon to be tattoed on my LEFT hand. (Is Legare reading?)

Reply | Quote

Dude! That’s like two whole clicks! I don’t have that long to live!

Reply | Quote

yeah, but you can select one cell on each of multiple rows and it deletes the rows without prompting if you want cells or rows deleted, so that saves two more clicks! (of course I have to find the right menu items of the 26 – and counting – on my shortcut menu!)

Reply | Quote

The Old shortcuts I learned in Access:
Ctrl + ; Current date
Ctrl + : Current time
Ctrl + ‘ Duplicate the preceding entry

Reply | Quote

This is why these old threads are sooo useful. I’d forgotten all about your date shortcut. Much better than writing a macro.

Reply | Quote

Is there a button premade for GoTo Special?

TIA
TC

Reply | Quote

I’m using XL97. I can’t find one, and there doesn’t seem to be a gotoSpecial dialog box, though there’s a Goto Formulas dialog “Application.Dialogs(xlFormulaGoTo).Show”. I think you have to use the various Selection..Select methods to use the options in the Goto Special Dialog box , but maybe one of the gurus here knows better.

Reply | Quote

I can’t get that to work in XL2K.

Cheers

Reply | Quote

I don’t have XL2k to test on, but you can view all the dialogs in Help for XL VB 2k to see if it’s still there. In 97 it brings up the standard GoTo dialog with the “Special” button grayed (greyed!) out. See Rory’s and Jan Karel’s response for the Goto Special dialog.

Regards,

Reply | Quote

Application.Dialogs(xldialogselectspecial).show should do it.

Reply | Quote

How about:

Application.Dialogs(xlDialogSelectSpecial).Show

Reply | Quote

Up to XL95 the Merge Panes button toggled merging/unmerging panes, this was so convenient that MS discontinued it from XL97 on. I used to use the following macro, assigned to the Merge Cells button, to restore the toggling function:
Sub MergeToggle()

With Selection
If .MergeCells Then
.MergeCells = False
Else
.MergeCells = True
.HorizontalAlignment = xlCenter

End If
End With
End Sub

Then I came across this super neat bit of code for toggling:

Sub MergeToggleNew()

With Selection
.MergeCells = Not .MergeCells

End With
End Sub

If you Freeze/Unfreeze Panes a lot:

Sub FreezePanes()

ActiveWindow.FreezePanes = Not ActiveWindow.FreezePanes

End Sub

Here are a couple more that I use constantly:

Sub ReplaceWithValues()

Reply | Quote

Thanks, Jan Karel and Rory. More dialogs than I can poke a stick at.

Reply | Quote

If you know exactly where you are going – the button of choice for GoTo is…
F5
Nice and simple.

Reply | Quote

True. But ThreeCats was looking for a way to directly “GoTo Special”. I wrote the following for myself, your mileage may vary:

Sub GoToSpecial()
‘ put this code in your personal.xls
Application.Dialogs(xlDialogSelectSpecial).Show
End Sub

Sub addGoToSpecialButton()
‘ run this code once
With Application.CommandBars(3).Controls.Add(1, , , 13, 0)
.BeginGroup = True
.FaceId = 29
.OnAction = “GoToSpecial”
.TooltipText = “GoTo Special Selection”
.Enabled = True
End With
End Sub

I hope you like my choice of a “GoTo” Icon.

Reply | Quote

Attach this macro to a custom macro button on a toolbar. It will activate the Go To Special Dialog!

Sub GoToSpecial()
Application.Dialogs(xlDialogSelectSpecial).Show
End Sub

For some extra dialog customisations…see post 428767

Reply | Quote

Here is something I use a lot. It shades alternate lines and is usefull with long lists of data for readability. Even if a row is deleted, the effect is maintained.

Sub AlternateLineShade3()
    With ActiveSheet.UsedRange
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, _
            Formula1:="=MOD(ROW(),2)=1"
        .FormatConditions(1).Interior.ColorIndex = 35
    End With
End Sub

It does however remove any existing conditional formatting.

Andrew C

Reply | Quote

I often have to copy a selection from a sheet where some rows are hidden. The ‘select visible cells’ button does the trick.
To put it on a menu-bar:
right click an existing toolbar, select ‘Customize…’ (last entry).
click the ‘Command’ tab, then ‘Edit’ in the ‘Categories’ window
in the ‘Commands’ window scroll down to the second-last item (Select Visible Cells)
grab the icon and move it to a toolbar location of your choice.
Hope this is useful.

Reply | Quote

Hey:
Slow down a little. I can’t even type my questions fast enough to keep up with your Posts. All this makes me feel like I’m on the outside looking in. So, here goes (you guys asked for it by Posting all this stuff):
1) what is the “Move Selection After Enter” option and where do I find it so I can remove the check?
2) Where do you put this code and how do you use it:
Public Sub AutofillRowOrColumn()
Selection.DataSeries Type:=xlAutoFill
End Sub
3) How do you use this “PasteList Button ” code?
Thats all for now. I know I will have more questions when I can read through some of the other suggestions.
Thanks
Stephen

Reply | Quote

Re 1) In XL97, Tools, Options, Edit, 4th checkbox down.

Re 2) you would put in your personal.xls (or whatever resides in your …xlstart folder) and attach it to a button.

Re 3) (& I think that’s PasteLINKS Button), it’s useful if you are linking two workbooks and you want to link from the source to the target. As above, attach it to a button.

I don’t use it: I had it for a while and removed it. I usually have the two workbooks open and use point&click from the target to the source. I.e. with cell A1 in sheet 1 in workbook “Target” selected, press “=” and Ctrl-Tab to Sheet 1 in workbook “Source”, select the cell you want to link, say cell A1, press Enter. Cell A1 in workbook “Target” now contains “=[Source]Sheet1!A1”.

Paste Links lets you work the other way; from Sheet1 in Workbook “Source” copy the link, then select workbook “Target”, then paste the link, using the code. Same result as previous paragraph.

So the difference is about whether you work from target to source or source to target when you are linking workbooks. Hope this makes sense!

Reply | Quote

Opposites…saves icons on the toolbar. Not sure where I ever learned this piece of trivia, but…it’s interesting. Never did find any documentation supporting it.

Left-click and hold a toolbar button and press the shift key. If the button has an opposite, it will appear. Release the shift key and the original button image is displayed. Toolbar buttons with opposites that I’ve found include:

Open – Save
Print – Print Preview
Sort Ascending – Sort Descending
Underline – Double Underline
Left Align – Right Align
Center Align – Merge and Center
Increase Decimal – Decrease Decimal
Increase Indent – Decrease Indent

Other buttons that have opposites include:

Insert Rows – Delete Rows
Insert Columns – Delete Columns
Paste Formatting – Paste Values
Clear Contents – Clear Formatting
Trace Dependents – Remove Dependent Arrows
Angle Text Upward – Angle Text Downward

Reply | Quote

File that under head scratching weirdness. I wonder what use that would be?

Reply | Quote

(Edited by JohnBF on 23-Aug-05 11:35. Coded calls to other functions into the subs, fixed typos.)

I use the Shift-Print as Print Preview and remove the default Print Preview Icon, so I can stuff more Icons on the TB. Sadly, Word doesn’t have the same feature without a little macro (which I haven’t written ). And I frequently use the Sort Ascending/Descending Toggle. FWTAW, here are three Word-like behaviors I code into my Excel sessions:

Sub Auto_Open()
Application.OnKey “^+>”, “FontPtIncr” ‘ increase fontsize in selection with ‘Ctrl-Shift->’ – warning*
Application.OnKey “^+<", "FontPtDecr" ' decrease fontsize in selection with 'Ctrl-Shift-<' – warning*
Application.OnKey "^e", "CenterFormatCell" ' center text in selection toggle with 'Ctrl-e'
End Sub
* slow on large areas

Sub FontPtIncr()
Dim rngCell As Range
If Worksheets(ActiveSheet.Name).ProtectContents = True _
Or TypeName(Selection) “Range” Then
Beep
Else
Application.ScreenUpdating = False
For Each rngCell In Intersect(ActiveSheet.UsedRange, Selection)
rngCell.Font.Size = rngCell.Font.Size + 1
Next rngCell
Application.ScreenUpdating = True
End If
End Sub

Sub FontPtDecr()
Dim rngCell As Range
If Worksheets(ActiveSheet.Name).ProtectContents = True _
Or TypeName(Selection) “Range” Then
Beep
Else
Application.ScreenUpdating = False
For Each rngCell In Intersect(ActiveSheet.UsedRange, Selection)
If rngCell.Font.Size > 1 Then _
rngCell.Font.Size = rngCell.Font.Size – 1
Next rngCell
Application.ScreenUpdating = True
End If
End Sub

Sub CenterFormatCell()
Dim rngCell As Range
If funcShtFail Then
Beep
Else
Application.ScreenUpdating = False
With Intersect(ActiveSheet.UsedRange, Selection)
If .HorizontalAlignment xlCenter Then
.HorizontalAlignment = xlCenter
Else
.HorizontalAlignment = xlGeneral
End If
End With
Application.ScreenUpdating = True
End If
End Sub

Reply | Quote

You know, this has to be one of my all-time favourite threads. So here is a new little shortcut I picked up the other day.
Imagine that you have a table like so:

A	B	C	D	E	F
Jan	Feb	Mar	Apr	May	Jun
stuff	stuff	stuff	stuff	stuff	stuff

You wish to insert an empty column in between each month. Normally if you select the range A-F the new columns will be inserted in a group in front of column A. However, if you use the Ctrl key and click on each column heading individually, the new columns will be inserted in between the selected columns.
Nifty!
Now, all I need is a macro to do this.

Reply | Quote

Here are two macros to insert columns between the columns of the selection, and rows between the rows of the selection:

Sub InsertColumnsBetween()
Dim i As Long
For i = Selection.Column + Selection.Columns.Count _
To Selection.Column + 1 Step -1
ActiveSheet.Columns(i).Insert
Next i
End Sub

Sub InsertRowsBetween()
Dim i As Long
For i = Selection.Row + Selection.Rows.Count _
To Selection.Row + 1 Step -1
ActiveSheet.Rows(i).Insert
Next i
End Sub

Reply | Quote

Hey! What’s this “i as Long” stuff? “i” is for Integers, you’ll confuse us all!

Reply | Quote

(Edited by WebGenii on 03-Oct-06 10:34. keeping up with Hans)

I hadn’t even gotten off the phone to try this when I see that you’ve posted an answer!

May I suggest the following variation?
Sub MultiInsert()
Dim strSelectedAddress As String
Dim i As Long

If Selection.Areas.Count = 1 Then
strSelectedAddress = Left(ActiveWindow.RangeSelection.Address(ReferenceStyle:=xlR1C1), 1)
If strSelectedAddress = “C” Then
For i = Selection.Column + Selection.Columns.Count _
To Selection.Column + 1 Step -1
ActiveSheet.Columns(i).Insert
Next i
End If
If strSelectedAddress = “R” Then
For i = Selection.Row + Selection.Rows.Count _
To Selection.Row + 1 Step -1
ActiveSheet.Rows(i).Insert
Next i
End If
Else
MsgBox (“This only works on a single area selection”)
End If
End Sub

Reply | Quote

Heck, I had just created the code and he beat me to it!

Reply | Quote

Fine!

Reply | Quote

Trying to get ahead of you both! Test this further for multiple Areas, seems to work, but I have to run:

Sub MultiBlankInsert()
Dim rngArea As Range
Dim strSelectedAddress As String
Dim lngX As Long

With Selection
For Each rngArea In .Areas
With rngArea
strSelectedAddress = Left(.Address(ReferenceStyle:=xlR1C1), 1)
If strSelectedAddress = “C” Then
For lngX = .Columns.Count + 1 To 2 Step -1
.Columns(lngX).Insert
Next lngX
End If
If strSelectedAddress = “R” Then
For lngX = .Rows.Count + 1 To 2 Step -1
.Rows(lngX).Insert
Next lngX
End If
End With
Next rngArea
End With
End Sub

Reply | Quote

Hey this works better and even works on rows and columns selected at the same time (though currently I can’t think of why I’d do that).

Reply | Quote

>though currently I can’t think of why I’d do that

Because some crazy boss wants it done ASAP!

Reply | Quote

Catherine,

It looks like the same CTRL+select works for things other than inserting cols. For example (using your A-F) table, if I select all the cols at once and format them with vertical borders, I can format the selection either as
– just the outer borders (left of A and right of F), or
– all col borders (by using the left, right, middle cols in the border dialog)

But if I want to format every other border (eg, left of A and right of B; left of B and right of D) to give me 2 Excel columns per my logical column, I can select 2 cols at a time and use CTRL to select additional groups of 2 cols. Then go to the format cells | borders dialog and select just left and right borders.

Similarly, if I have a label in Col A, C, and E that I wanted centered across A+B, C+D, and E+F, I select 2 at a time using CTRL to add groups of 2. Then to the format cells | alignment | center across selection and I have what I want.

I actually needed to do this today so your tip came in handy.

Just as an FYI: what I used to do was select 2 cols and do what I wanted (eg, format outer borders). Then select another 2 and use CTRL+y to repeat the action.

Fred

Reply | Quote

Although I did know about the opposite functionalilty, I never knew the icon changed when you pressed the shift whilst holding the click, devious!

Reply | Quote

There are hundreds of ways to select things in Excel, but this is one of my favorites:
In a long list with several columns starting with column A I want to select all rows and columns from row 35 to the bottom of the list:
Select cell A35
Rest your mouse on the right border of the selected cell, hold Shift and double click. This will select all contigous columns across row 35
Rest your mouse on the bottom border of the selected range, hold Shift and double click again and this will take you to the bottom.

Carla

Reply | Quote

Another nice one:
CTRL+SHIFT+* (asterisk)
Select the current region around the active cell (the data area enclosed by blank rows and blank columns). In a PivotTable report, select the entire PivotTable report.

Reply | Quote

If anyone needs a fast way to add sequential numbers to a large amount of rows in a list or data base the marco below can be a real time saver over custom fill.

Select an empty column next to the data highlight the rows you want and run the Maco. Will work in both directions, either selecting using the up direction or selecting using a down direction.

Having the numbers is great for restoring data to its original order after custom sorting, or other types of data movements.

TD

Sub NumFil()
‘
‘ NumFil Macro

‘Macro will allow a user to select cells made up
‘of a single column that will then be filled with
‘sequential numbers
‘
Dim rCount As Integer
Dim r As Integer

rCount = Selection.Rows.Count
Selection.Cells(1, 1).Activate
Selection.Cells(1, 1).Value = 1
For r = 1 To rCount – 1
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = _
ActiveCell.Offset(-1, 0).Value + 1
Next
‘
End Sub

Reply | Quote