Workbook_BeforeClose trouble

[Guest]

Hi All,

I am having real trouble with 'Workbook_BeforeClose'. I have read numerous
threads and the help in VB, but nothing I try makes the event work. I am now
so confused I'm stumped.

Can someone give me a step by step guide to what I need to do for this to
work please? I basically want to select a particular sheet in the workbook
before closing it. This sheet will always be in the same position in the
workbook, but will have a different name every time.

I have tried this code to no avail:

Private Sub Workbook_BeforeClose( )
Sheets(3).Select 'select required sheet
ActiveWorkbook.Close 'close on required sheet
End Sub

 

[Guest]

Hi,

Try to use your own code , just a little modify:

Private Sub Workbook_BeforeClose( )
Sheets(3).Select 'select required sheet
Me.Save
End Sub

Or :
Private Sub Workbook_Open( )
Sheets(3).Select 'select required sheet
End Sub

 

[Guest]

Thanks for the response Halim. Unfortunately, the events still do not
activate.

However, I have found a workaround in another part of code later on to do
the same thing - select the required sheet while running rather than at the
end of code in source sheet. So simple it's stupid really...

Cheers.

 

[Mike Fogleman]

The select sheet does work, however, you are next asking that the workbook
close in the Workbook_Before_Close event. That line is not needed. What is
needed is to Save after the sheet has been selected.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets(3).Select 'select required sheet
ActiveWorkbook.Save
End Sub

Cheers, Mike F

 

[Guest]

Hi Mike,

I have tried this by selecting a different sheet then closing the workbook.
When I open the workbook again, it has not saved on sheet(3) as expected?? I
copied the code into the sheet.

This is a frustration now, but no more than that as I have worked around it.
Would still like to understand why it's not happening...??

 

[Guest]

Maybe this one:
Private Sub Workbook_Open( )
Thisworkbook.Sheets(3).Activate
End Sub

 

[Mike Fogleman]

I am not sure either. If you put this code in a new workbook with 3 sheets,
it works. That's what I did to test it. There must be some other code
somewhere that negates this action. You mentioned that the sheet has a new
name every time. I assume that happens when it is re-opened. Perhaps
something there makes it appear as though it didn't close on sheet 3. See
what I mean?
Mike F

 

[Guest]

Hi Mike,

I have just tried this too, but it still doesn't work!! I think I'll just
give up for now - it's Friday after all...

Cheers.