what do YOU have on your toolbar

[RET79]

Hi.

A light hearted posting now.....

What custom features on your toolbar etc. have you got on your excel that you could not live without? What little macros do you have that you need all the time to make your life easier?

RET79

 

[Barry Katcher]

You're right, dwhj. I mis-read Phylis' request. There doesn't seem to be an equivilent in Excel to Lotus' "Style-Alignment-Center + Across Columns". In Excel, it appears you MUST merge the cells you want to center the text in (and don't use a preposition to end your sentence with). And there is the "Merge and Center" command icon in the "Format" toolbar.

 

[Barry Katcher]

You're right, dwhj. I mis-read Phylis' question. Excel doesn't seem to have the equivilent of Lotus' "Style-Alignment-Center+Across Columns" command. It appears that you MUST merge cells before you can center across them. In that case, there is the "Merge and Center" command icon in the same "Format" toolbar that you can drag up to your edit toolbar.

P.S. Dagnab it. Thought my reply didn't post - keep on forgetting about "page 2".
_________________
...and I always put the seat down.
This message was edited by Barry Katcher on 2002-05-06 08:11

 

[dwhj]

On 2002-05-06 08:03, Barry Katcher wrote:
You're right, dwhj. I mis-read Phylis' request. There doesn't seem to be an equivilent in Excel to Lotus' "Style-Alignment-Center + Across Columns". In Excel, it appears you MUST merge the cells you want to center the text in (and don't use a preposition to end your sentence with). And there is the "Merge and Center" command icon in the "Format" toolbar.

The only way without VBA seems to be by going to Format>Cells>Alignment>Horizontal>CenterAcrossSelection.

As you are apparently aware, ending sentences with prepositions is not something up with which some people put.
I know you really meant to say "merge the cells in which you want to center the text (and don't use a preposition with which to end your sentence)."
Please write it proper in future!

 

[Ricky Morris]

To Phylis:

1) Enter the VBA code below in a new workbook.
2) From the immediate window in VBA enter "thisworkbook.IsAddin = true". If the immediate window isn't visible in VB Editor click View > Immediate Window from the Menu.
3) Save the file as "xzy.xla"
4) From Excel load the addin by clicking Tools > Addins > Browse from the menu. Navigate to location you saved xyz.xla.



Sub Autpen()
Dim btnCenter As CommandBarButton
Set btnCenter = CommandBars(4).FindControl(ID:=402)
With btnCenter
.Visible = True
.Caption = "Center Across Selection"
.OnAction = "CenterAcrossSelection"
End With
End Sub

Sub CenterAcrossSelection()
On Error Resume Next
Selection.HorizontalAlignment = xlCenterAcrossSelection
End Sub

Sub Auto_close()
CommandBars(4).FindControl(ID:=402).Reset
End Sub

 

[shades]

Macro buttons:

FillBlanks
Column Autofit
Print Page 1
Print Page 2
Change competitor names
Change market names
Grids
Clear worksheet (many, many times a day!)

 

[artslave]

The standard buttons I always add to my toolbars with the Customize command:

File category:
- Set Print Area (= File/Print Area/Set Print Area)
- Mail Recipient (sends email with workbook as attachment)

Edit category:
- Paste Values
- Delete Rows & Delete Columns
- Select Visible Cells (= Go To/Special/Visible Cells)
- Select Current Region (actually I usually use CTRL-SHIFT-* for this now)

Insert category:
- Insert Rows & Insert Columns

Format category:
- Cells...
- Merge & Unmerge

Tools category:
- Camera

Drawing category, when dealing with logos/graphics:
- Group & Ungroup (move to main toolbar)
- Bring Forward/Send Back (move to main toolbar)
- Crop
- Reset Picture (incredibly useful!!)

As for custom macro buttons, I also use simple one-line macros to:

- Center Across Selection without merging (this is also available by selecting Format/Cells/Alignment tab/Horizontal drop-down/Center Across Selection.)
(Selection.HorizontalAlignment = xlCenterAcrossSelection)

- Use "Currency" instead of "Accounting" format on my "$" button
(Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)")

- Calculate current selection only -- useful when dealing with my massive manual-calculation-only analysis spreadsheet
(ActiveWindow.Selection.Calculate)

- Paste / Add
(Selection.PasteSpecial Operation:=xlAdd)

- Toggle column headers
(If ActiveWindow.DisplayHeadings = False Then
ActiveWindow.DisplayHeadings = True
Else
End If)


I'd love to hear more suggestions -- shortcuts are a wonderful thing!!

Catherine

 

[artslave]

Oh, and another useful toolbar tip I picked up from (Excel 97) Help:

"When you quit Microsoft Excel, changes you made to the menu bar and built-in toolbars, any custom toolbars you created, and the toolbars currently displayed are saved in a toolbars settings file in your Windows folder. This settings file is saved as username8.xlb, where username is your Windows or network log-in name. If your computer is not connected to a network or not set up with a log-in prompt, the settings file is saved as excel8.xlb. The toolbar configuration saved in this file is used by default each time you start Microsoft Excel."

So if your custom toolbars disappear, you can find them again by re-opening this .xlb file -- has saved me a TON of frustration!!

Catherine

 

[Chris Davison]

Catherine,

the amount of times this crops up as a question....(!)

I'll just bookmark it for future

good one,
Chris

marker xlb

 

[g_erhard]

Hrm, seems I need to create a shortcut to that file and drop-kick it onto my Start menu...

----------------

I haven't don a lot with "custom" commands, but I have reworked my toolbars a tiny bit.

File:
Mail Recipient (lots of folks mailing excel workbooks back and forth here, helps to be able to do it right from the document)

Format:
Style (prepackaged formatting for new worksheets, cool.)
Conditional Formatting (hate having to hunt that up on the menu)

I'm sure there are other cool things I could add or modify, BUT... then I'd be busy messing around with the toolbars all the time, and subseuently not getting much work done (like, say, now.)

 

[Blue Hornet]

Some terrific ideas here. Thanks, all!

I've got another (related) question. One of my 'must have' toolbar buttons is a Protect Sheet / Unprotect Sheet button. How can I get that to toggle 'pressed' or 'not pressed' to SHOW me the protection status of the sheet, without having to do trial and error or show the text of the button? If I leave the button in 'text' mode, the caption does change to show what the next button push will do. But I'd like to save toolbar space by not showing the caption text.

If that's not clear, I'm trying to get the button itself to change its appearance the way the 'Drawing', 'Bold', 'Underline', etc. buttons do. (Which is kind of silly, when you think of it, because when the Drawing button has been pressed YOU GET A NEW TOOLBAR! When the Bold button's pressed you get bold text, etc. So why does one need to know if those buttons are pressed or not?)

Sorry for the rant. Thanks again.

Chris