Auto run macro when open worksheet

[ama150]

I want to run a macro when I open a spreadsheet. Can you help?

Thanks

 

[phantom1975]

WELCOME TO THE BOARD!

Piece of cake! Open the Visual Basic Editor by pushing ALT + F11. Then click on the folder on the left side called MICROSOFT EXCEL OBJECTS. Now click the ThisWorkbook module. Enter this code on the right:

Private Sub Workbook_Open()
'Enter your macro here
End Sub

Does this hlep?

 

[JamieDeVry]

sure, simply

1. Go to the Visual Basic Editor by pressing the keys Alt+F11.

2. From the Project Explorer, double-click over the ThisWorkbook object

3. That will execute an event window for you. At the top of the new code window, you will see the word 'General'. Select the drop down where you see general and select 'Workbook'.

4. You will notice that Excel VBA assumes the Open event. That means whatever code you insert in this procedure will execute automatically when the workbook is opened.


Private Sub Workbook_Open()
MsgBox "say hello"
End Sub

 

[ama150]

Worked great!!

Thanks for the help!

 

[vogash]

For me it didn't work, Office 2013
Any clue why?

sure, simply

1. Go to the Visual Basic Editor by pressing the keys Alt+F11.

2. From the Project Explorer, double-click over the ThisWorkbook object

3. That will execute an event window for you. At the top of the new code window, you will see the word 'General'. Select the drop down where you see general and select 'Workbook'.

4. You will notice that Excel VBA assumes the Open event. That means whatever code you insert in this procedure will execute automatically when the workbook is opened.


Private Sub Workbook_Open()
MsgBox "say hello"
End Sub

 

[levsim]

This helped me out: Run a macro - Excel

Basically Workbook_Open() has changed to Autpen().

 

[JoeWolf]

How do you edit the macro file without running it since you've set it to run when you open it? I have created a macro that will automatically send the files through email but I need to edit it without sending the emails.

 

[levsim]

You may disable macros in Excel security so they will not run automatically but rather ask for permission. You can access the settings from a different spreadsheet.

 

[grovkillen]

How do you edit the macro file without running it since you've set it to run when you open it? I have created a macro that will automatically send the files through email but I need to edit it without sending the emails.

I do this by simply check the file name:

Sub Autpen()
CheckNameWorkbook = ActiveWorkbook.Name
FileNameToExecute = "YourFileName.xls"
If CheckNameWorkbook = FileNameToExecute & "m" Then
...Your code here
End If

If I remove the "m" in the file type "xlsm" my macro will not exectue on open. If I then add the "m" again the macro will work as intended upon next open.

 

[Anubhav_g]

Hey !!

I tried doing what is mentioned above, but excel is showing some kind of error, can you look into this and help me ?!

Private Sub Workbook_Open()
Option Explicit
Public dTime As Date


Sub ValueStore()
Dim dTime As Date
Range("I7:I13").Value = Range("C7:C13").Value
Range("J7:J13").Value = Range("G7:G13").Value
Range("K7:K13").Value = Range("H7:H13").Value
Call Hello
End Sub




Sub Hello()
dTime = TimeValue("01:41:00")
Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub


Sub Bye()
On Error Resume Next
Application.OnTime dTime, "ValueStore", Schedule:=False
End Sub
End Sub

This code not working what could be the error ?

sure, simply

1. Go to the Visual Basic Editor by pressing the keys Alt+F11.

2. From the Project Explorer, double-click over the ThisWorkbook object

3. That will execute an event window for you. At the top of the new code window, you will see the word 'General'. Select the drop down where you see general and select 'Workbook'.

4. You will notice that Excel VBA assumes the Open event. That means whatever code you insert in this procedure will execute automatically when the workbook is opened.


Private Sub Workbook_Open()
MsgBox "say hello"
End Sub