I have a file with a Workbook_Open macro (see below) but there is another
macro which enables the user to save the most worksheets in the file with a
new name. The new file retains the Workbook_Open macro which I don't want
not least because the Worksheet "Menu" referred to has been deleted so keps
coming up with an error.

I've tried including code to remove the Workbook_Open macro before saving as
a new file without success but thought this may be an easier way.

Is it possible to make the Workbook_Open macro conditional by adding code
like

If this file is called myfilename.xls
Then
(add Workbook_Open macro code here)

Else
End Sub

If so, what is the correct syntax

Thanks in anticipation

Keith

-----------
Private Sub Workbook_Open()
ThisWorkbook.Activate
Application.Calculation = xlCalculationManual
Sheets("Menu").Select
End Sub

Just test it in the open event

Private Sub Workbook_Open()
If ThisWorkbook.Name ="myfilename.xls" Then
ThisWorkbook.Activate
Application.Calculation = xlCalculationManual
Sheets("Menu").Select
End If
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"nospaminlich" wrote in message
...
I have a file with a Workbook_Open macro (see below) but there is another
macro which enables the user to save the most worksheets in the file with
a
new name. The new file retains the Workbook_Open macro which I don't want
not least because the Worksheet "Menu" referred to has been deleted so
keps
coming up with an error.

I've tried including code to remove the Workbook_Open macro before saving
as
a new file without success but thought this may be an easier way.

Is it possible to make the Workbook_Open macro conditional by adding code
like

If this file is called myfilename.xls
Then
(add Workbook_Open macro code here)

Else
End Sub

If so, what is the correct syntax

Thanks in anticipation

Keith

-----------
Private Sub Workbook_Open()
ThisWorkbook.Activate
Application.Calculation = xlCalculationManual
Sheets("Menu").Select
End Sub

Nothing happens to trigger your code before the Workbook_Open event; you
would need to approach it like this:

Sub Workbook_Open()

If ThisWorkbook.Name = "MyFileName.xls" Then
' Call your procedure here
Else
' existing Workbook_Open code here
End If

End Sub

"nospaminlich" wrote:

I have a file with a Workbook_Open macro (see below) but there is another
macro which enables the user to save the most worksheets in the file with a
new name. The new file retains the Workbook_Open macro which I don't want
not least because the Worksheet "Menu" referred to has been deleted so keps
coming up with an error.

I've tried including code to remove the Workbook_Open macro before saving as
a new file without success but thought this may be an easier way.

Is it possible to make the Workbook_Open macro conditional by adding code
like

If this file is called myfilename.xls
Then
(add Workbook_Open macro code here)

Else
End Sub

If so, what is the correct syntax

Thanks in anticipation

Keith

-----------
Private Sub Workbook_Open()
ThisWorkbook.Activate
Application.Calculation = xlCalculationManual
Sheets("Menu").Select
End Sub

Oops, got it backwards, but hope you get the idea...

"K Dales" wrote:

Nothing happens to trigger your code before the Workbook_Open event; you
would need to approach it like this:

Sub Workbook_Open()

If ThisWorkbook.Name = "MyFileName.xls" Then
' Call your procedure here
Else
' existing Workbook_Open code here
End If

End Sub

"nospaminlich" wrote:

I have a file with a Workbook_Open macro (see below) but there is another
macro which enables the user to save the most worksheets in the file with a
new name. The new file retains the Workbook_Open macro which I don't want
not least because the Worksheet "Menu" referred to has been deleted so keps
coming up with an error.

I've tried including code to remove the Workbook_Open macro before saving as
a new file without success but thought this may be an easier way.

Is it possible to make the Workbook_Open macro conditional by adding code
like

If this file is called myfilename.xls
Then
(add Workbook_Open macro code here)

Else
End Sub

If so, what is the correct syntax

Thanks in anticipation

Keith

-----------
Private Sub Workbook_Open()
ThisWorkbook.Activate
Application.Calculation = xlCalculationManual
Sheets("Menu").Select
End Sub