Page 1 of 5 123 ... LastLast
Results 1 to 15 of 63
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Useful Excel Customizations (Excel 2000/97)

    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

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

    <pre>Sub InsertDateTime()
    ActiveCell.Value = Now()
    ActiveCell.Offset(0, 1).Select
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    Hey - great Idea <img src=/S/clever.gif border=0 alt=clever width=15 height=15> - I'm going to steal that immediately. They never should have attached Merge and Center to the same button as Center across Selection.

    <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15><small>mutter, mutter</small>. What were they thinking?
    Nevermind I'll fix it with your code.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    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
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    I deeply detest the use of merge cells because of it's interference in selecting rows or columns which contain them.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    Me too!

  8. #8
    2 Star Lounger
    Join Date
    Aug 2001
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    Great Stuff - Thanks

    TC

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    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

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    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.)
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    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! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Cheers!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22>
    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. <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    (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? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

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

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    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!
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Star Lounger
    Join Date
    Dec 2000
    Location
    Tacoma, Washington, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Useful Excel Customizations (Excel 2000/97)

    The Old shortcuts I learned in Access:
    Ctrl + ; Current date
    Ctrl + : Current time
    Ctrl + ' Duplicate the preceding entry
    <IMG SRC=http://www.wopr.com/w3tuserpics/DougKlippert_sig.jpg>

Page 1 of 5 123 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •