Workbook open event

[Pawan]

Hi

I have written a code in workbook open event. But it works only if I closes
excel and reopens the file. If I close that particular workbook (without
closing excel) and reopens, then that code doesnt execute. Is it normal
behavior? Is there any way to make the code work whenever that workbook
reopens?

Pawan

 

[Mike H]

Hi,

It sounds like you've put the code in the open event in Personal.xls

Alt+F11 to open VB editor then Ctrl+R to open project explorer.

On the left find VBAProject(your workbook name) and your code goes in there.
Double click "This Workbook' in that project and enter your code on the right.
If you have put it in personal.xls then I suggest you remove it.

Mike

 

[Pawan]

I have put it in Thisworkbook under 'Microsoft Excel Onjects' of the
appropriate workbook...

 

[Barb Reinhardt]

If might help if you show your code.

 

[Pawan]

The code is:

Private Sub Workbook_Open()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In Worksheets

If ws.CodeName <> "Sheet1" And ws.CodeName <> "Sheet2" Then ws.Delete

Next

Application.DisplayAlerts = True


End Sub

 

[Barb Reinhardt]

It looks like it should work if it's really in the ThisWorkbook module. Try
this, in the Immediate window, type this
Application.EnableEvents = TRUE

Close the workbook and reopen and see if it works now.

 

[Barb Reinhardt]

Oops, I just noticed something

Private Sub Workbook_Open()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ThisWorkbook.Worksheets 'Changed this line

If ws.CodeName <> "Sheet1" And ws.CodeName <> "Sheet2" Then ws.Delete

Next

Application.DisplayAlerts = True


End Sub

 

[Pawan]

It still doesnt work..

Oops, I just noticed something

Private Sub Workbook_Open()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ThisWorkbook.Worksheets 'Changed this line

If ws.CodeName <> "Sheet1" And ws.CodeName <> "Sheet2" Then ws.Delete

Next

Application.DisplayAlerts = True


End Sub