Sample of codes to use a boolean array to track sheet changes, new
worksheets can be added but the code to monitor these sheets needs to added.

Put this code in a standard module

Public bArray
Sub OpenProc()
Dim iA As Integer
Dim iSheets As Integer
iSheets = ThisWorkbook.Worksheets.Count
ReDim bArray(iSheets)
For iA = 1 To iSheets
bArray(iA) = False
Next
End Sub

In the workbook open event put this

Private Sub Workbook_Open()
OpenProc
End Sub

In each worksheet code put this

Private Sub Worksheet_Change(ByVal Target As Range)
bArray(Me.Index) = True
End Sub

In your workbook code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim c As Range
For Each wS In Worksheets
If bArray(wS.Index) Then
For Each c In wS.UsedRange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
End If
Next wS
ThisWorkbook.Save
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
ReDim Preserve bArray(ActiveWorkbook.Worksheets.Count)
End Sub
--

Regards,
Nigel




"akemeny" wrote in message
...
Ok... so I use (for instance) cell c1 to set the boolean then have the
code
set for cells(1, 3)... correct?

I've never set a boolean in this type of setting, how would I set it to
track if there were any changes made to the spreadsheet?

"Nigel" wrote:

AFAIK changes to any sheet is only known at workbook level.

I have not tried this but use the worksheet change event to set a boolean
or
a cell on the sheet to indicate change

On each worksheet code add this...., (I used cell A1 but it needs to be
somewhere out of user eyesight or all sheet changes stored on another
sheet!)

Private Sub Worksheet_Change(ByVal Target As Range)
Me.Cells(1, 1) = True
End Sub

Then use your code modified as follows....

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim c As Range
For Each wS In Worksheets
If wS.Cells(1, 1) Then
wS.Cells(1, 1) = False
For Each c In wS.UsedRange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
End If
Next wS
ThisWorkbook.save
End Sub

--

Regards,
Nigel




"akemeny" wrote in message
...
Some of the workbooks that I use have anywhere between 5-15
spreadsheets,
but
when its open only a few of those will actually be used or have any
changes
made in them. So... Is there a way to adjust the macro below so that
it
will
only run in the spreadsheets that had changes made while the workbook
was
open?

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wS As Worksheet
Dim c As Range
For Each wS In Worksheets
For Each c In wS.UsedRange
If Not c.HasFormula Then
c.Value = Trim(c.Value)
End If
Next c
Next wS
ThisWorkbook.Save
End Sub