automatic macros

[itsme]

I have a macro in one of my sheets of a 9 sheet file (run by ctrl z). Is there anyway to have it run automatically when I first open the file. If so, how?

Thanks

 

[Anson]

Put the codes in "ThisWorkbook" module in VB Editor as shown below:

Private Sub Workbook_Open()
'-------------Code Contents----------------
End Sub

 

[itsme]

Instead of rewritng the whole code there, cannt I somehow just write to run this macro (ctrl z)? If yes, what exactly do I write?
Thanks

 

[Frank Kabel]

Hi
you could also rename your macro to Autpen()

 

[itsme]

OK Frank you confused me now?

Hi
you could also rename your macro to Autpen()

 

[Frank Kabel]

Hi
sorry. In the first suggestion just use
Public sub workbook_open()
your_macro_name
end sub

 

[itsme]

Where and how am I putting this?

Hi
sorry. In the first suggestion just use
Public sub workbook_open()
your_macro_name
end sub

 

[Frank Kabel]

Hi
as said by Anson: In your workbook module ('ThisWorkbook') in the VBA
editor

 

[itsme]

Im lost when you guys say workbook module?

Hi
as said by Anson: In your workbook module ('ThisWorkbook') in the VBA
editor

 

[hglamy]

thank you Frank Kabel, great contribution !

Best regards,

H.G. Lamy

 

[Guest]

-----Original Message-----
I have a macro in one of my sheets of a 9 sheet file (run

by ctrl z). Is there anyway to have it run automatically
when I first open the file. If so, how?

Thanks
.

itsme,

Select Tools->Macro->Visual Basic Editor from the menu
bar. By default it will open with the "Microsoft Excel
Objects" folder opened to "Sheet1 (Sheet1)" double-click
on "ThisWorkbook". Now copy/paste the following into the
whitespace that opened up when you double-
clicked "ThisWorkbook". Save and reopen your file. Upon
opening your file again, the words "Hello World" will
appear in the R1C1(A1 column/row) of all nine of your
worksheets. Ultimately you may want to purchase a book on
VBA(Visual Basic for Applications) to help you out. Check
out the MSDN page of Microsoft too. There is a ton of
help there.

Kevin K.

Private Sub Workbook_Open()
Range("A1").Select
ActiveCell.FormulaR1C1 = "Hello World"
Sheets("Sheet2").Select
ActiveCell.FormulaR1C1 = "Hello World"
Sheets("Sheet3").Select
ActiveCell.FormulaR1C1 = "Hello World"
Sheets("Sheet4").Select
ActiveCell.FormulaR1C1 = "Hello World"
Sheets("Sheet5").Select
ActiveCell.FormulaR1C1 = "Hello World"
Sheets("Sheet6").Select
ActiveCell.FormulaR1C1 = "Hello World"
Sheets("Sheet7").Select
ActiveCell.FormulaR1C1 = "Hello World"
Sheets("Sheet8").Select
ActiveCell.FormulaR1C1 = "Hello World"
Sheets("Sheet9").Select
ActiveCell.FormulaR1C1 = "Hello World"
Range("A2").Select
End Sub

 

[Frank Kabel]

Hi
try the following to get to your workbook module (this module stores
the workbook event code procedures):
- open your Excel file
- open the VBA editor (hit ALT+F11)
- in the left explorer window should be an item named 'ThisWorkbook'
- double click on this entry
- paste the code in the appearing editor window

To learn more about event procedures take a look at:
http://www.cpearson.com/excel/events.htm

 

[itsme]

I have nothing that says 'ThisWorkbook'?
What do you mean by the left explorer window?

Thanks for all your help!

 

[Frank Kabel]

Hi
what do you see if you open the VBA editor (maybe you have to hit
CTRL+R to open the treeview)

 

[Chip Pearson]

In the VBA Editor, press CTRL+R to open the Project Explorer
window. In this window, you should see a tree-view with a
"folder" icon for each open workbook. Click the Plus sign to
expand your workbook. There, you should see a "folder" called
"Microsoft Excel Objects". Click the Plus sign to expand that,
and you'll see a "file" icon called ThisWorkbook. Double-click
that to open the ThisWorkbook code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[David McRitchie]

Seems to me that everything has been stated a few times.

Project Library is another name for the workbooks, so if your
workbook is named myworkbook.xls bring up the VBE
then Ctrl+R (View, Project Explorer) then for your t project
name and expand everything, you will see ThisWorkBook after
a directory of sheetnames.

A shortcut for getting to ThisWorkBook for installing
Workbook Events is:
right-click on the Excel logo to left of file menu, view code

BTW, when you find it what part was the key that wasn't
stated before.

 

[itsme]

Thanks everone, it finally works!!!!
Great

If I want to have more then one macro run when the file opens do I just add more at the same spot?

Example:

Public sub workbook_open()
Macro18
end sub

Public sub workbook_open()
Macro41
end sub

Seems to me that everything has been stated a few times.

Project Library is another name for the workbooks, so if your
workbook is named myworkbook.xls bring up the VBE
then Ctrl+R (View, Project Explorer) then for your t project
name and expand everything, you will see ThisWorkBook after
a directory of sheetnames.

A shortcut for getting to ThisWorkBook for installing
Workbook Events is:
right-click on the Excel logo to left of file menu, view code

BTW, when you find it what part was the key that wasn't
stated before.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

In the VBA Editor, press CTRL+R to open the Project Explorer
window. In this window, you should see a tree-view with a
"folder" icon for each open workbook. Click the Plus sign to
expand your workbook. There, you should see a "folder" called
"Microsoft Excel Objects". Click the Plus sign to expand that,
and you'll see a "file" icon called ThisWorkbook. Double-click
that to open the ThisWorkbook code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[itsme]

HELP!

I tried what I questioned in my last email and it doesn't work for more then one macro!
What am I doing wrong?

THANKS

Thanks everone, it finally works!!!!
Great

If I want to have more then one macro run when the file opens do I just add more at the same spot?

Example:

Public sub workbook_open()
Macro18
end sub

Public sub workbook_open()
Macro41
end sub

Seems to me that everything has been stated a few times.

Project Library is another name for the workbooks, so if your
workbook is named myworkbook.xls bring up the VBE
then Ctrl+R (View, Project Explorer) then for your t project
name and expand everything, you will see ThisWorkBook after
a directory of sheetnames.

A shortcut for getting to ThisWorkBook for installing
Workbook Events is:
right-click on the Excel logo to left of file menu, view code

BTW, when you find it what part was the key that wasn't
stated before.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

In the VBA Editor, press CTRL+R to open the Project Explorer
window. In this window, you should see a tree-view with a
"folder" icon for each open workbook. Click the Plus sign to
expand your workbook. There, you should see a "folder" called
"Microsoft Excel Objects". Click the Plus sign to expand that,
and you'll see a "file" icon called ThisWorkbook. Double-click
that to open the ThisWorkbook code module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com






I have nothing that says 'ThisWorkbook'?
What do you mean by the left explorer window?

Thanks for all your help!

:

Hi
try the following to get to your workbook module (this module
stores
the workbook event code procedures):
- open your Excel file
- open the VBA editor (hit ALT+F11)
- in the left explorer window should be an item named
'ThisWorkbook'
- double click on this entry
- paste the code in the appearing editor window

To learn more about event procedures take a look at:
http://www.cpearson.com/excel/events.htm


--
Regards
Frank Kabel
Frankfurt, Germany


itsme wrote:
Im lost when you guys say workbook module?

:

Hi
as said by Anson: In your workbook module ('ThisWorkbook')
in the
VBA
editor

--
Regards
Frank Kabel
Frankfurt, Germany


itsme wrote:
Where and how am I putting this?

:

Hi
sorry. In the first suggestion just use
Public sub workbook_open()
your_macro_name
end sub

--
Regards
Frank Kabel
Frankfurt, Germany


itsme wrote:
OK Frank you confused me now?

:

Hi
you could also rename your macro to Autpen()

--
Regards
Frank Kabel
Frankfurt, Germany


itsme wrote:
Instead of rewritng the whole code there, cannt I
somehow just
write to run this macro (ctrl z)? If yes, what
exactly do I
write? Thanks

:

Put the codes in "ThisWorkbook" module in VB Editor
as shown
below:

Private Sub Workbook_Open()
'-------------Code Contents----------------
End Sub

:

I have a macro in one of my sheets of a 9 sheet
file (run by
ctrl z). Is there anyway to have it run
automatically when I
first open the file. If so, how?

Thanks

 

[Berend Botje]

Just do it like this:

Public sub workbook_open()

call Macro18
call Macro41

end su

 

[itsme]

thanks it worked!