TRIM toolbar icon

  • Thread starter Thread starter Kathy
  • Start date Start date
K

Kathy

I don't suppose there is an icon that can be placed in a
toolbar, for the TRIM function, is there? Or a way to make
such an icon? I don't know to quit while I'm ahead, yes?!
I checked all the commands under all the categories, and
don't find a nice, convenient icon for TRIM.
 
Kathy,

Are you looking for a facility to trim a selected or selection of cells? If
so, create this simple proc
Sub TrimAll()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = Trim(cell.Value)
End If
Next
End Sub

Add a button to a toolbar, and assign it to that macro.
 
Kathy

There is no button for the TRIM function.

Sounds like you have much TRIMming to do. Perhaps a macro would serve you
better. That way you don't have to TRIM each cell individually using a helper
cell and the TRIM function.

David McRitchie's TRIMALL macro can be assigned to a button on your Toolbar.
One click does all cells at a whack.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

See David's site for "getting started with Macros" to get instructions on
how/where to store and use macros. As your needs and expertise progress in
Excel you will want to use Macros to make your repetitive tasks easier. Jump
in and get your feet wet now.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP - XL97 SR2 & XL2002
 
These were prior files I'm editing. From now on, no more
double spaces with that particular project. I'll check out
creating macros. Thanks!
-----Original Message-----
Kathy

There is no button for the TRIM function.

Sounds like you have much TRIMming to do. Perhaps a macro would serve you
better. That way you don't have to TRIM each cell individually using a helper
cell and the TRIM function.

David McRitchie's TRIMALL macro can be assigned to a button on your Toolbar.
One click does all cells at a whack.

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

See David's site for "getting started with Macros" to get instructions on
how/where to store and use macros. As your needs and expertise progress in
Excel you will want to use Macros to make your repetitive tasks easier. Jump
in and get your feet wet now.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP - XL97 SR2 & XL2002
Click to expand...
 
Hi Bob,
I was changing double spaces to single space in a long
column in several files. I was shown how to make the TRIM
function work, and it made it alot easier than editing
individually. I'll have to check into this using formulas
and creating macros. Thanks for the tip!
 
Hi Kathy,
As far as the toolbar icon goes, for such things as you would
not be doing so often, I would use a menu. You can see
an arrangement of some of my subroutines arranged in a
menu at
http://www.mvps.org/dmcritchie/excel/barhopper.htm

Some day I will make it so it generates a spreadsheet such
as John Walkenbach uses as input into his Menu Maker
(see related area in the above).

If you want to put a macro in a toolbar Icon that is simple
but it occupies more real estate, some examples in
http://www.mvps.org/dmcritchie/excel/toolbars.htm

Those macros that you have available will still be available
in ALT+F8 (tools macro, macros) regardless of putting
them on your toolbars or into menus. Which you can tread
as one big alphabetical menu.

BTW, is my TRIMALL macro from a menu.
 
Back
Top