Vba - Manual thisworkbook open vs Code Audo Open

[Uzma Shaheen]

Hi everyone

I have a workbook that has some code in the workbook open event that does a whole load of things which is fine

I also have this workbook open via code in a different workbook that just updates a cell in this workbook and then saves and closes it

Is it possible that when i open the workbook via code from another workbook- i use the auto open code that just does the above and does not have the workbook open event code fire (ignore it) and vice versa - if its not opened via code and manually then fire off the workbook open event code ignoring the Code open code?

is this possible?

Many thanks

 

[Tom Urtis]

You might achieve your objective by sandwiching the open code in the other workbook by disabling and re-enabling events. Example:

Application.EnableEvents = False
'Your other workbook's auto_open code
Application.EnableEvents = True

 

[Uzma Shaheen]

Hi Tom

I'm not sure exactly what you mean and how to encorporate it

Say in my example

In the This workbook module I have want this

This WORKBOOK MODULE

Private Sub Workbook_Open()
    'Do something when workbook is open manually only - eg run this code
    'do not run Auto_Open Code
End Sub

Private Sub Auto_Open()
    'Run some code if opened by code only - eg Run this code
    'save and close the workbook
    'Do not Run Workbook_Open Code
End Sub

 

[Uzma Shaheen]

Hi Tom

I'm not sure exactly what you mean and how to encorporate it

Say in my example

In the This workbook module I have want this

This WORKBOOK MODULE

Private Sub Workbook_Open()
    'Do something when workbook is open manually only - eg run this code
    'do not run Auto_Open Code
End Sub

Private Sub Auto_Open()
    'Run some code if opened by code only - eg Run this code
    'save and close the workbook
    'Do not Run Workbook_Open Code
End Sub

Bump

 

[Jaafar Tribak]

Bump

How are you opening the other workbook ? Are you opening it in a second instance of excel or in the same one ?

Also, can you re-phrase what you want in a clearer manner as I find your first post a little bit unclear.

EDIT:

What exactly do you mean by "auto open code" ? Are you referring to the Autpen Macro or something else ?

 

[Uzma Shaheen]

Hi

Sorry for being not clear - i will try again

By Autpen I basically want to fire off some code when the workbook is opened

I think its opened in 1 instance

This is what is happening - I want to
have 2 workbook open events (1 to run if the workbook is opened Manually only) and the other to run if the workbook is opened via a macro so a user is not physically opening it

I send out a spreadsheet that users open on their machines which can only be opened manually
so i want to fire off the workbook open manual code

When i open the workbook via the macro - that is only opened by me so i want to run the workbook open code that is opened via code

so i guess the workbook is opened in seperate intances

I hope this makes sense

 

[Jaafar Tribak]

Here is one way :

Code in the ThisWorkbook Module of the workbook being opened:

Option Explicit

Private Sub Workbook_Open()
    MsgBox "Workbook opened manually only."
End Sub

Public Sub AutoOpen()
    MsgBox "Workbook opened via code only."
End Sub

And this is the code in the caller workbook that opens the other workbook via code :

Sub Test()
    On Error GoTo errHandler
    Application.EnableEvents = False
    Workbooks.Open("[B][COLOR=#0000ff]TypeHereThePathAndNameOfTheWorkbookBeingOpened[/COLOR][/B]").AutoOpen
errHandler:
    Application.EnableEvents = True
End Sub

 

[Uzma Shaheen]

Thank you

i will give that a go

thanks