Before Save w Addin
 

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,

Before Save w Addin
 

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,

Before Save w Addin
 

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,

Before Save w Addin
 

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,

Before Save w Addin
 

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,

Before Save w Addin
 

I can not tell without seeing more code... I am gone for the day so I can't
help you until tomorrow...
--
HTH...

Jim Thomlinson


"tjh" wrote:

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,

Before Save w Addin
 

I set up the ThisWorkbook procedures, the Standard Module, and the Class
Module exactly as you stated below.

'''Except in the Class Module in the procedure below I made a small change --

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
' MsgBox "TADA"
UserForm1.Show
End Sub

'''''''''''''From here the Userform1 should appear, but it only runs the
first time I save. The userform code is very basic, all it is doing is
determining if the user wants to add information to the footer of each page.
See Below ''''''''''''''''''

Option Explicit

Private Sub CommandButton1_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
wks.PageSetup.CenterFooter = _
"&8 Something Special"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub CommandButton3_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton4_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.CenterFooter = _
"&8Something Special"
Next wks
Unload UserForm1
End Sub

''''''''''''''''''''As you can see there is not much to it. Just a few
command buttons that asks if the user wants to put a special footer in the
center or right footers. Any thoughts on why it is not running the second
time I try to save? It seems that for this to work I have to open a new
session of excel. However, using your TADA message box it works fine. Is
there something special I need to do for the UserForm?

Thank You,








"Jim Thomlinson" wrote:

I can not tell without seeing more code... I am gone for the day so I can't
help you until tomorrow...
--
HTH...

Jim Thomlinson


"tjh" wrote:

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,

Before Save w Addin
 

Thank You that worked!!!! By simply changing the

End
''''to
Unload Userform1

I have always used the End statement when using a Cancel button on a
userform. That is the reason I used End. What is the difference in End and
Unload to close a Form? I know the Unload removes the object from memory, but
why does the End statement prevent the code from recognizing the Class Module
clsXLEvents in a later occurance?

Thanks for your help!!!





"Jim Thomlinson" wrote:

Without testing it from my end your command button 2 uses an end, which
should probably be unload. Have you placed a break point on the show command
to ensure that the code is executing? Do you toggle events
(application.enableevents = false) anywhere in your code that could be
causing a problem? I see nothing in the userform itself that causes me
concern.
--
HTH...

Jim Thomlinson


"tjh" wrote:

I set up the ThisWorkbook procedures, the Standard Module, and the Class
Module exactly as you stated below.

'''Except in the Class Module in the procedure below I made a small change --

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
' MsgBox "TADA"
UserForm1.Show
End Sub

'''''''''''''From here the Userform1 should appear, but it only runs the
first time I save. The userform code is very basic, all it is doing is
determining if the user wants to add information to the footer of each page.
See Below ''''''''''''''''''

Option Explicit

Private Sub CommandButton1_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
wks.PageSetup.CenterFooter = _
"&8 Something Special"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub CommandButton3_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton4_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.CenterFooter = _
"&8Something Special"
Next wks
Unload UserForm1
End Sub

''''''''''''''''''''As you can see there is not much to it. Just a few
command buttons that asks if the user wants to put a special footer in the
center or right footers. Any thoughts on why it is not running the second
time I try to save? It seems that for this to work I have to open a new
session of excel. However, using your TADA message box it works fine. Is
there something special I need to do for the UserForm?

Thank You,








"Jim Thomlinson" wrote:

I can not tell without seeing more code... I am gone for the day so I can't
help you until tomorrow...
--
HTH...

Jim Thomlinson


"tjh" wrote:

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,

Before Save w Addin
 

The form never really ended. The code just stopped. Unload runs the
de-constructor for the form object (cleaning up memory and such). Using end
the form never truely ended.

For reference I never use End. I have found it to be troublesome. I have
followed one of the golden rues of programming that all subs, functions, ...
have only one entrance and one exit. If a sub or such can end prior to the
End Sub, then you can never be sure how much of the code executes. This
results in some weird side effects when you start modifying your code and
makes debugging much more difficult. All code in has the opprotunity to be
executed. Some is skipped based on selection criteria such as if statements...
--
HTH...

Jim Thomlinson


"tjh" wrote:

Thank You that worked!!!! By simply changing the

End
''''to
Unload Userform1

I have always used the End statement when using a Cancel button on a
userform. That is the reason I used End. What is the difference in End and
Unload to close a Form? I know the Unload removes the object from memory, but
why does the End statement prevent the code from recognizing the Class Module
clsXLEvents in a later occurance?

Thanks for your help!!!





"Jim Thomlinson" wrote:

Without testing it from my end your command button 2 uses an end, which
should probably be unload. Have you placed a break point on the show command
to ensure that the code is executing? Do you toggle events
(application.enableevents = false) anywhere in your code that could be
causing a problem? I see nothing in the userform itself that causes me
concern.
--
HTH...

Jim Thomlinson


"tjh" wrote:

I set up the ThisWorkbook procedures, the Standard Module, and the Class
Module exactly as you stated below.

'''Except in the Class Module in the procedure below I made a small change --

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
' MsgBox "TADA"
UserForm1.Show
End Sub

'''''''''''''From here the Userform1 should appear, but it only runs the
first time I save. The userform code is very basic, all it is doing is
determining if the user wants to add information to the footer of each page.
See Below ''''''''''''''''''

Option Explicit

Private Sub CommandButton1_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
wks.PageSetup.CenterFooter = _
"&8 Something Special"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub CommandButton3_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton4_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.CenterFooter = _
"&8Something Special"
Next wks
Unload UserForm1
End Sub

''''''''''''''''''''As you can see there is not much to it. Just a few
command buttons that asks if the user wants to put a special footer in the
center or right footers. Any thoughts on why it is not running the second
time I try to save? It seems that for this to work I have to open a new
session of excel. However, using your TADA message box it works fine. Is
there something special I need to do for the UserForm?

Thank You,








"Jim Thomlinson" wrote:

I can not tell without seeing more code... I am gone for the day so I can't
help you until tomorrow...
--
HTH...

Jim Thomlinson


"tjh" wrote:

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,

Before Save w Addin
 

Without testing it from my end your command button 2 uses an end, which
should probably be unload. Have you placed a break point on the show command
to ensure that the code is executing? Do you toggle events
(application.enableevents = false) anywhere in your code that could be
causing a problem? I see nothing in the userform itself that causes me
concern.
--
HTH...

Jim Thomlinson


"tjh" wrote:

I set up the ThisWorkbook procedures, the Standard Module, and the Class
Module exactly as you stated below.

'''Except in the Class Module in the procedure below I made a small change --

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
' MsgBox "TADA"
UserForm1.Show
End Sub

'''''''''''''From here the Userform1 should appear, but it only runs the
first time I save. The userform code is very basic, all it is doing is
determining if the user wants to add information to the footer of each page.
See Below ''''''''''''''''''

Option Explicit

Private Sub CommandButton1_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
wks.PageSetup.CenterFooter = _
"&8 Something Special"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub CommandButton3_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton4_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.CenterFooter = _
"&8Something Special"
Next wks
Unload UserForm1
End Sub

''''''''''''''''''''As you can see there is not much to it. Just a few
command buttons that asks if the user wants to put a special footer in the
center or right footers. Any thoughts on why it is not running the second
time I try to save? It seems that for this to work I have to open a new
session of excel. However, using your TADA message box it works fine. Is
there something special I need to do for the UserForm?

Thank You,








"Jim Thomlinson" wrote:

I can not tell without seeing more code... I am gone for the day so I can't
help you until tomorrow...
--
HTH...

Jim Thomlinson


"tjh" wrote:

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,

Before Save w Addin
 

OK, That makes sense. But, if you never use the End Statement, How do you
stop a procedure from running in the middle, if a particular criteria is met
such as within an if statement.

"Jim Thomlinson" wrote:

The form never really ended. The code just stopped. Unload runs the
de-constructor for the form object (cleaning up memory and such). Using end
the form never truely ended.

For reference I never use End. I have found it to be troublesome. I have
followed one of the golden rues of programming that all subs, functions, ...
have only one entrance and one exit. If a sub or such can end prior to the
End Sub, then you can never be sure how much of the code executes. This
results in some weird side effects when you start modifying your code and
makes debugging much more difficult. All code in has the opprotunity to be
executed. Some is skipped based on selection criteria such as if statements...
--
HTH...

Jim Thomlinson


"tjh" wrote:

Thank You that worked!!!! By simply changing the

End
''''to
Unload Userform1

I have always used the End statement when using a Cancel button on a
userform. That is the reason I used End. What is the difference in End and
Unload to close a Form? I know the Unload removes the object from memory, but
why does the End statement prevent the code from recognizing the Class Module
clsXLEvents in a later occurance?

Thanks for your help!!!





"Jim Thomlinson" wrote:

Without testing it from my end your command button 2 uses an end, which
should probably be unload. Have you placed a break point on the show command
to ensure that the code is executing? Do you toggle events
(application.enableevents = false) anywhere in your code that could be
causing a problem? I see nothing in the userform itself that causes me
concern.
--
HTH...

Jim Thomlinson


"tjh" wrote:

I set up the ThisWorkbook procedures, the Standard Module, and the Class
Module exactly as you stated below.

'''Except in the Class Module in the procedure below I made a small change --

Private Sub xlApp_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI _
As Boolean, Cancel As Boolean)
' MsgBox "TADA"
UserForm1.Show
End Sub

'''''''''''''From here the Userform1 should appear, but it only runs the
first time I save. The userform code is very basic, all it is doing is
determining if the user wants to add information to the footer of each page.
See Below ''''''''''''''''''

Option Explicit

Private Sub CommandButton1_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
wks.PageSetup.CenterFooter = _
"&8 Something Special"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton2_Click()
End
End Sub

Private Sub CommandButton3_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.RightFooter = _
"&6 &Z" & Chr(10) & "&F" & Chr(10) & "&D&T"
Next wks
Unload UserForm1
End Sub

Private Sub CommandButton4_Click()
UserForm1.Hide
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.PageSetup.CenterFooter = _
"&8Something Special"
Next wks
Unload UserForm1
End Sub

''''''''''''''''''''As you can see there is not much to it. Just a few
command buttons that asks if the user wants to put a special footer in the
center or right footers. Any thoughts on why it is not running the second
time I try to save? It seems that for this to work I have to open a new
session of excel. However, using your TADA message box it works fine. Is
there something special I need to do for the UserForm?

Thank You,








"Jim Thomlinson" wrote:

I can not tell without seeing more code... I am gone for the day so I can't
help you until tomorrow...
--
HTH...

Jim Thomlinson


"tjh" wrote:

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,