none
Button to .Close RRS feed

  • Question

  • Hey,

    I have a "Close" button on a worksheet that calls a macro that runs one line: ActiveWorkbook.Close

    Before the workbook closes, I have some code in the 'Sub Workbook_BeforeClose(Cancel As Boolean)' procedure that's supposed to clean up stuff on each worksheet, however, I am unable to activate the other sheets. When the code runs, it sticks on the first worksheet. I can't get it to move.

    I'm using: ActiveWorkbook.Sheets("Sheet1").Activate

    Been stuck on this for a few days so any help appreciated!



    Wednesday, September 9, 2015 9:31 PM

All replies

  • You are absolutely correct. That is exactly what occurs and other worksheets cannot be activated from within the Before Close event. I don't now if it is regarded as a bug or what but the VBA code can be written to alleviate the problem.

    It is almost never necessary to select (or activate) worksheets with VBA code. You can reference the worksheets with the VBA code and perform whatever changes are required and this way it works.

    In the following simple example of attempting to select a worksheet and write to the selected worksheet results in all of the worksheet names getting inserted in cell A1 (one at a time) on the first sheet and the remaining sheets have cell A1 blank. (The MsgBox is simply to stop the code so you can see that the worksheet does not change and the name is written to cell A1 of the first sheet.)

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim ws As Worksheet
        For Each ws In Worksheets
            ws.Select
            Range("A1") = ws.Name
            MsgBox ws.Name
        Next ws
    End Sub

    However, with the following example, without attempting to select each worksheet but referencing the worksheet by the qualified reference, the worksheet name is written to cell A1 of each of the corresponding worksheets.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Dim ws As Worksheet
        For Each ws In Worksheets
            With ws
                .Range("A1") = .Name
            End With
            MsgBox ws.Name
        Next ws
    End Sub

    If you can't convert your code to use the qualified reference in lieu of selecting then post a copy of it and I will attempt to correct it for you.


    Regards, OssieMac

    • Proposed as answer by ryguy72 Saturday, September 12, 2015 12:12 AM
    Thursday, September 10, 2015 3:50 AM
  • I should have included in my previous post that the problem only occurs when VBA code is used to close the workbook as per OP's original post.

    If the workbook is closed from the interactive mode either by clicking the cross top right of the worksheet or selecting File -> Close then the worksheets are selected and the problem does not occur.


    Regards, OssieMac

    Thursday, September 10, 2015 4:02 AM