Excel97- Automatically run macro when workbook is opened 2

[MrsTFB]

Hey guys,
I need help. I have always assigned my macros to a button on the worksheet. Now, some smart guy wants to not have to push the button to run my macro. He wants the macro to run automatically when the workbook is opened. I played around with this at least a year ago and was not successful. Any ideas?

It is run from a network, so the macro has to be within the workbook.

Thanks for any direction,

MrsTFB in Tennessee

:-{}

 

[rmikesmith]

Mrs TFB,

Call your macro from withing the Workbbok_Open event procedure, located in the Thisworkbook code module. Example

Private Sub Workbook_Open()
   myMacro
End Sub

HTH
M. Smith

 

[MrsTFB]

Help,
I must not be doing something right. I have added this

Private Sub Workbook_Open()

Application.Run("TEST&quot

End Sub

and

Private Sub Workbook_Open()

TEST

End Sub

I can't make either work. Sorry for the ignorance on my part. What do you think?

Thanks
Mrstfb in tennessee

 

[kevinclark]

Is your TEST routine/function is defined as Private in a seperate Module you would need to change it to Public.

Public Sub TEST()
.....
End Sub

 

[rmikesmith]

MrsTFB,

Are you getting an error or is nothing happening? Are you sure the Workbook_Open procedure is in the Thisworkbook code module. The easiest way to ensure this is: From the VB editor, double-click on the ThisWorkbook icon in the project explorer window. In the code pane, click on the left dropdown, and select Workbook. This will create the Workbook_Open procedure shell. Just drop your code or procedure call in.

M. Smith

 

[MrsTFB]

The TEST is the macro that I want to run when the workbook is opened.

Thanks

 

[MrsTFB]

That was it!!! I have never used This Workbook before. What is the difference of that and putting the code in Module 1?

You guys are great. Thanks!!!

Giving you a star!!
MrsTFB in Tennessee GO TITANS!

 

[rmikesmith]

MrsTFB,

You can think of the code module behind Thisworkbook as a container for workbook-level events, such as Workbook_Open, Workbook_Activate, etc. These procedures are fired when their associated events occur (e.g., when the current workbook is activated by switching to it from another workbook, the Workbook_Activate event procedure runs). If you have code inside this, or make a call to one of your procedures (e.g. "TEST&quot, this will be executed every time the workbook is activated. In the same fashion, there are code modules behind the Sheet objects in your workbook, such as Worksheet_Change, for a worksheet, that fires whenever a cell on that worksheet changes. On the other hand, code placed in a standard Code Module (e.g., Module1) is not tied to events, even if you duplicate the procedure declaration; i.e. placing

Private Sub Workbook_Open()
...
End Sub

in a standard code module will not run when the workbook is opened.

I hope this helps to clear things up.

Regards,
M. Smith

 

[MrsTFB]

Thanks so much M.Smith, Just one more small question. Can I remove that from the This Workbook progrmatically if I were to us the Save As and change the name of the workbook. Once the workbook is opened the first time, then saved as a new name, I no longer want the macro to be activated upon opening.

Is that too much to ask? After the save as, I could program, Sub whatever.delete ???

What do you think?

Thanks again,
MrsTFB in Tennessee

 

[rmikesmith]

MrsTFB,

The following will run your procedure one time and one time only; i.e. the first time the workbook is opened. This code should be placed in the ThisWorbook module, and in fact, should replace what you have there now (read notes after the code):

' TAG: Run Msg


Private Sub Workbook_Open()

If ThisWorkbook.VBProject.VBComponents("ThisWorkbook").codemodule.Lines(2, 1) = "' TAG: Run Msg" Then
  ' Replace MsgBox line with the procedure you want to run only once.
  MsgBox "Testing...", vbOKOnly, ""
  ThisWorkbook.VBProject.VBComponents("ThisWorkbook").codemodule.ReplaceLine Line:=2, String:="' TAG: Disable Msg"
End If

End Sub

Notes: The "comment"

' TAG: Run Msg

must be located on the second line with the single quote symbol in column 1 (the code pane toolbar will show ln 2, Col 1). Replace the MsgBox line with the name of the procedure you want to run once. If you want to test this code out without running your macro, leave the MsgBox line, Save & Close the workbook, then Open. To restore the functionality of running your procedure (say, after the previously mentioned test), replace the altered comment

' TAG: Disable Msg

with the original,

' TAG: Run Msg

. Warning: You should be aware that depending on where you deploy this workbook you could run into the problem I had. Namely, because this is self-modifying code, McAfee Viruscan believes it has found a virus (x97M/Generic) when trying to save the workbook after adding the code. You can get around this by temporarily disabling Viruscan. I'm not sure if once saved if this is still a problem (i.e., when a user opens the workbook and the code runs). I'll experiment and let you know.

Regards,
Mike

 

[andrew299]

Just a quick update to this

I have tried this in my program that needs to run on 2000 and NT. 2000 Works fine but the problem lies with NT
Upon startup of the macro it seems to insert an extra line in the workbook section where this code needs to be placed. To overcome this simply place the ' TAG: Run Msg right at the top. The extra line will be inserted by NT.

Has anyone else noticed this?

 

[blom0344]

WARNING:

The code Mike offers:

' TAG: Run Msg


Private Sub Workbook_Open()

If ThisWorkbook.VBProject.VBComponents("ThisWorkbook&quot.codemodule.Lines(2, 1) = "' TAG: Run Msg" Then
' Replace MsgBox line with the procedure you want to run only once.
MsgBox "Testing...", vbOKOnly, ""
ThisWorkbook.VBProject.VBComponents("ThisWorkbook&quot.codemodule.ReplaceLine Line:=2, String:="' TAG: Disable Msg"
End If

End Sub


triggers McAfee virus scan to detect it as a X97M/Generic
virus, which in my case resulted in loss of about 2 hours formatting on an Excel file.

I have no idea how the triggering works, but on 3 PC's the results were the same..........!!!!!!!!!!!!!!!!! T. Blom
Information analyst
tbl@shimano-eu.com

 

[rmikesmith]

To T. Blom (and Others):

Sorry to hear you lost time/effort. Please note that in my last reply to this thread on July 10, 2002 I identified the problem with McAfee Viruscan. Not sure if you had Viruscan set up to automatically take action in response to "virus infected" files. I do not, and can run my test workbook containing the code without loss of data, etc. I do, of course, get the alarming/annoying alert. I believe this type of alert will be raised whenever your workbook is running self-modifying code, which is what my routine does. To a virus scanner, this probably looks like malicious code and it rightly flags its presence. You will want to consider this before deploying such a solution.

Regards,
Mike

 

[jebry]

Hi!

Late post I know, but if you only have one macro that you want to run when the workbook is opened then call it autoexec and it will.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[rmikesmith]

Jeff,

A couple of points: The original question posted was indeed straightforward and after some fits & starts was solved using the Workbook_Open event procedure. The other related method is to create a Autpen sub in a standard code module. This is for backward compatibility with pre-97 versions of Excel. To my knowledge, a SUB named Autoexec will not run automatically (didn't run in my test).

The current replies are related to a secondary question, added by the originator. The issue is self-modifying code and its triggering of McAfee Viruscan.


Regards,
Mike

 

[RobBroekhuis]

I can work with self-modifying code just fine, but it won't send through Outlook - it's caught by some virus-checker and the attachment is removed. Even when I rename the file from .xls, zip it, or otherwise try to camouflage it. Big Brother is watching me!
Rob

 

[jebry]

Hi Mike!

Mea Culpa!

I made an assumption (always a bad idea) that since you could do it that way in Access and Word that you could in Excel also. Should have tested it first.

I had realized that the post had continued to a different subject though. That is why I labeled it as a late post!

Thanks for the info!
Jeff Bridgham
bridgham@purdue.edu

 

[rmikesmith]

Hi Jeff,

The Autoexec thing just points out that while MS Office has come a long way in commonizing its programming environments, there are still enough differences to be quite confounding.


Regards,
Mike

 

[blom0344]

Sorry Mike,

I was in an awful hurry, so I copied the code and did not read on on what you already mentioned about the issue with the Mcafee virus-scan. Should have paid a bit more attention....!!!!

Apologies.. T. Blom
Information analyst
tbl@shimano-eu.com