Run a Macro on Workbook open 2

[altaratz]

Could someone please lay out in very simple terms how to auto run a macro when the workbook opens up - thanks - it seems simple, but I can't figure it out :-(.

 

[KenWright]

With your workbook open, hit ALT+F11 and this will open the VBE (Visual Basic Editor).
Top left you will hopefully see an explorer style pane. Within this pane you need to search for your workbook's name, and when you find it you may need to click on the + to expand it. Within that you should see something like the following:-

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
ThisWorkbook
Modules
Module1
Module2
etc etc (You may have just 1 or none of these)

If you have named your sheets then those names will appear in the brackets above as opposed to
what you see at the moment in my note.

Double click the ThisWorkbook bit and it will open up a blank white space with two dropdowns above it. The right dropdown will say General - Click it and choose Workbook and the right dropdown should now default to Open, but if it doesn't then go select Open. You will now see the following:-

Private Sub Workbook_Open()

End Sub

Put your code in between, then hit File / Close and return to Microsoft Excel and save the file.

Done.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[Wicca]

Merry Meet

I asked a similar question in the VBA forum and got this answer

It works beautifully with a slight mod to suit my setup.
I am assuming you know how to CREATE a macro


Sub Autpen()
Application.Run "yourfile.xls!Menus"
End Sub
Sub Menus()
With MenuBars("Worksheet")
.Reset
.Menus.Add ("&Macros")
With .Menus("&Macros")
.MenuItems.Add ("yourfile&1"), "yourmacro" ' so you can run manually if needed
End With
End With
Application.Run "yourfile.xls!yourmacro"
End Sub

There is also a 'TIMER' there but be cautious of the continuous loop that may occur....again, mod to suit your requirements

The thread in the VBA Visual Basic for Applications (Microsoft) is

thread 707-801856

The addition of the macro menu is great also it saves time and energy when I am modifying stuff as I continue to tweek the VBA code.

Hope this helps



Merry Part and the Brightest of Blessings

With respect
Wicca
-----
IF you think you can
Or if you think you can't
Then you are probably right
-Henry Ford

 

[fredericofonseca]

Wicca,

Just a small note as you may not be aware of it.

If you put the thread reference without no spaces eg: thread707-801856 it creates a link, and it is possible to click on it to jump to the thread in question, as opposed to what you've done == thread 707-801856 == which is handled as text only.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[veyant76]

Hi,

I have a macro commafile which converts txt file into text to colums. I have added this macro in personal bookbecause i want to run this macro when ever excel is run.

But i want to check that if file name is data.xls then only it should run automatically.

actually, i want if i double click on data.xls then as excel will open this file it should be converted in text to columns automatically. as currently i have to run this macro manually.

I tried hard. i tried to put some code in auto_open but failed. some error comes. _global variable application not found, object not set or blocked, object not found, etc. can any body help me with the code what should i type in auto_open so that i should check file name and if file name is data then it should run commafile macro automaticaly. As i am new to this field so i am not having so much of knowledge on this. pls share your knowledge with me.

thanks '

Vaayu