Excel VBA Macro Request

[yetixhunting]

Hi. I am working on a [shared? is that redundant?] network, using a shared Excel 2003 document. I would like for Excel to email notify me ONLY when a text/value change has been made in that document AND that change has been saved.

So, if someone on the network (accidentally) alters a cell, but the change is NOT saved before quitting, I do not need to know about it. I only want to know about permanent changes to the document.

Your Help is Much Appreciated! Thanks in Advance!

 

[Insane_ai]

This may be of use:
http://msdn.microsoft.com/en-us/lib...ts_event.workbookbeforesave(v=office.11).aspx

The event Workbook BeforeSave Trigger may be what you are looking for.

 

[yetixhunting]

I don't understand what an "AppEvent" is.

Here is the code I have so far:
******************BEGIN*******************************

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim answer As String
Dim newval As String
newval = Target.Address
answer = MsgBox("Would you like to keep the changes you made?", vbYesNo, "You are about to update this worksheet.")
If answer = vbNo Then Cancel = True
If answer = vbYes Then
'open outlook type stuff
Set OutlookApp = CreateObject("Outlook.Application")
Set OlObjects = OutlookApp.GetNamespace("MAPI")
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Name Here")
newmsg.Recipients.Add ("email address here")
'add subject
newmsg.Subject = "Worksheet " & Target.Worksheet.Name & " has been updated"
'add body
newmsg.Body = "Cell(s) " & newval & " have been updated in the Worksheet " & Target.Worksheet.Name & "."
newmsg.Display 'display
newmsg.Send 'send message
'give conformation of sent message
Dim confirmation As String
confirmation = MsgBox("You have updated this spreadsheet.", , "Changes Made")

End If

'save the document
'Me.Worksheets.Save
End Sub

******************************END********************

Right now, If I alter a cell, then save, nothing happens.
If I alter a cell, hit close, and it asks me to save, and I save, nothing happens.

Help? Thanks!

 

[DCrake]

To me this is a no hoper, at least I would not like to be tasked with this problem. Just imagine if a user changes hundredsof cells on various worksheets, how are you hoping to gather this information together in such a way that it is meaningful to the reader. And don't forget that if some cells are formula cells they are also changed. Do you not think that this is a bit of an overkill?

If is so important that you know what is changed then I would think of some sort of security device to put in place to contend with the issue.

 

[yetixhunting]

DCrake, you have killed my dreams. Either you misunderstood my request, overanalyzed it, or Excel's capabilities are truly way more limited than I imagined.

Here is the process I had hoped to engage:

User enters Excel --> User changes ONE SINGLE CELL --> Excel recognizes a change is made (is that asking too much?) --> User wishes to save document with change made --> Excel realizes that document being saved is not the same thing as document that was opened --> Excel sends an email to someone (whomever), letting them know that X document has been altered in cell $X$X.

I'm not asking about hundredsof cells being changed simultaneously. Just one. One cell. Any cell. It could be a formula. It could not be a formula. And Excel can't execute my request?

 

[DCrake]

Lets create a senerio.

User opens a workbook that contains at least one worksheet. The cursor is in the cell A1. The use then decides to widen the column to read the contents on column A. They then decie to close the workbook without making a change to any cell. Excel will still ask them if they want to save their changes even though no cells have changed.

Your task is to somehow decide if the changes they made were infact due to the contents of the cell being changed or something else.

Second Senerio

User makes a change to a Cell then chooses to undo the changes and exit the workbook. Excel will still ask about saving new version.


The issue is to determine what is a permenant change and what is not. Then decide if one or more cells have been affected. Now what happens if the workbook has links to other wookbooks which are subsequently affected what happens then.

Again I go back to my origninal statement, this is a none starter.

 

[MarkK]

Here's code that does some of what you're looking for. The SheetChange event seems to only fire when a value in a cell is changed. It ignores cell formatting and columnwidth changes.

Private m_cellsChanged As String

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If m_cellsChanged <> "" Then 
      MsgBox m_cellsChanged
      m_cellsChanged = ""
   End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   m_cellsChanged = m_cellsChanged & Sh.Name & Target.Address & vbCrLf
End Sub

This code records the addresses of cells that are changed between saves and displays those in the BeforeSave handler. You can send mail from there if you want.
But as DCrake says, you may end up sending way more emails that you expect. If that is a reasonable cost to you then this may help.
Cheers,