Auto_Open Quickie

[ribhead]

I have written a macro in my Personal macro file that when Excel opens it adds some command bars/tool bars..etc... What I'm trying to do now is check the activeworkbook's name and add buttons accordingly. Does the Autpen run before it knows the name of the file? If so, then how can I check the name of the file after it has opened and do the nasty things I want it to do automatically. Any help would be appreciated.

For just $19.95 you too can have a beautiful smile.

 

[xlbo]

depends if you are opening excel with the file every time or if excel is already open and then the file is opened

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[ribhead]

Excel is already open.

For just $19.95 you too can have a beautiful smile.

 

[Bowers74]

I'm thinking that you will need a Class Module to do what you want to do so that you can "see" every time a different workbook is Opened, Activated, Deactivated and more. Add the following code into a new Class Module and see what happens when you Open, Activat, Deactivate, Print etc., etc.:

I got this code from Ole P. Erlandsen at

http://www.erlandsendata.no/

and added the Activate & Deactivate as examples.

Enter into a Class Module named

Public WithEvents App As Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    ' your code here
    MsgBox "A new workbook is created!"
End Sub

Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
    MsgBox Wb.Name & " has been deactivated."
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    MsgBox Wb.Name & " has been activated."
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    ' your code here
    MsgBox "A workbook is closed!"
End Sub

Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
    ' your code here
    MsgBox "A workbook is printed!"
End Sub

Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    ' your code here
    MsgBox "A workbook is saved!"
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    ' your code here
    MsgBox "A workbook is opened!"
End Sub

Enter into Workbook_Open event

Dim ApplicationClass As New AppEventClass

Private Sub Workbook_Open()
    Set ApplicationClass.App = Application
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set ApplicationClass.App = Nothing
End Sub

Have Fun! I hope this helps!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[ribhead]

I'll give this a try (tomorrow). Thanks for the help.

For just $19.95 you too can have a beautiful smile.