Your code works using the Message Box.

The problem I think is with my additional code. Instead of using your
message box, I placed in the section containing the BeforeSave event a

Userform1.Show

This opens a form which will as a couple of questions with various answers,
from here the code will perform an action before the save. I think my
userform is causing the problem.

Should this cause a different outcome than your code?? Or any thoughts as to
why my userform will not appear after the second, third, fourth.... time I
save?

Thank You,




"Jim Thomlinson" wrote:

All of the code that I gave you should be in your addin. It instatiates an
instance of Excel that "Listens" for events. When it detects an event (Before
Save) then it performs it's action. It has nothing to do with how often a
file is saved or such. The class is instanitated only once and the instance
persist for the duration of the Excel session.
--
HTH...

Jim Thomlinson


"tjh" wrote:

Thank you for your response,

This seems to work the first time that I click the save button, but if I
click the save button more than once while a workbook or workbook session is
open, the code does not run.
It appears that the problem is when we --- Set xlApp = New clsXLEvents
which is done when a workbook is opened or the addin is installed.

What would you suggest to be an appropriate event to allow for the setup,
which would allow for multiple saves.

Thank You,


"Jim Thomlinson" wrote:

The problem is that you are not saving the addin so the code does not fire.
To do waht you want is a bit more complicated. Here is some code...

In a new Class module name clsXLEvents
Option Explicit
Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As
Boolean, Cancel As Boolean)
MsgBox "Tada"
End Sub

In a standard module named whatever
Option Explicit

Public xlApp As clsXLEvents

And in ThisWorkbook
Option Explicit

Private Sub Workbook_AddinInstall()
Set xlApp = New clsXLEvents
End Sub

Private Sub Workbook_AddinUninstall()
Set xlApp = Nothing
End Sub

Private Sub Workbook_Open()
Set xlApp = New clsXLEvents
End Sub

That should do what you want it to do...

--
HTH...

Jim Thomlinson


"tjh" wrote:

Hello,

I am trying to use an add-in, which will perform an action before saving to
the workbook. The before save event worked when I was using it as a normal
excel workbook, however when I changed it to an Add-in ---- the event does
not seem to work the same.

I am using an add-in because I would like this code to run anytime I save an
excel file.

Within the add-in I have the code to start the process in the VBAProject
"ThisWorkbook" using the BeforeSave event.

Any suggestions on getting this to run before any excel file is saved.

Thank You,