Strange behavior when closing a workbook

Question
  • Work workbook is called Main.xlsm
  • It has two sheets called Sheet1 and Sheet2
  • Sheet1 has a commandbutton called cmd_btn
  • Sheet2 is currently xlVeryHidden

In Module1 I have the following code:

Sub Macro1()
With Workbooks("Main.xlsm").Sheets("Sheet2")
    .Visible = True
    .Copy
    .Visible = xlVeryHidden
End With
End Sub

This code makes Sheet2 visible, copies it as a new workbook (a new workbook pops up), then makes it xlVeryHidden again. At this point, Main.xlsm is behind, newly created workbook is in front.


When I run Macro1 directly:

  • I click the X (close) button of Main.xlsm, the Main.xlsm tries to close as it should be.

When I run Macro1 from cmd_btn:

  • This time the excel window flickers a few times. When I click the X (close) button of Main.xlsm, the newly opened excel workbook (Book#.xlsx) tries to close.

Question: What might be the reason of this?


PS: If this doesn't happen to you when you try the above, try the following and you will experience the same thing:

  • Create a combobox on Sheet1 called cmb_list
  • Populate cmb_list with "Copy Sheet2" and "Copy Sheet3"
  • Create another sheet called Sheet3 and make it xlVeryHidden
  • Select any of the values in cmb_list and run the following code from cmd_btn

Sub Macro3()
With Workbooks("Main.xlsm")
    If cmb_list.Value = "Copy Sheet2" Then
        .Sheets("Sheet2").Visible = True
        .Sheets("Sheet2").Copy
        .Sheets("Sheet2").Visible = xlVeryHidden
    ElseIf cmb_list.Value = "Copy Sheet3" Then
        .Sheets("Sheet3").Visible = True
        .Sheets("Sheet3").Copy
        .Sheets("Sheet3").Visible = xlVeryHidden
    End If
End With
End Sub

Show source
| excel-vba   | vba   | excel   2017-08-08 21:08 1 Answers

Answers to Strange behavior when closing a workbook ( 1 )

  1. 2017-08-09 04:08

    I tried all steps you provided and couldn't duplicate the problem with your code, as is
    (do you have any other code in Workbook_BeforeClose() event?)

    I optimized the code a bit and it still works:

    Sheet1 VBA module:

    Option Explicit
    
    Private Sub cmd_btn_Click()
        Application.ScreenUpdating = False
        CopyWorkSheet Right(cmb_list.Value, 1)
        Application.ScreenUpdating = True
    End Sub
    

    ThisWorkbook VBA module:

    Option Explicit
    
    Private Sub Workbook_Open()
        populateCmb
    End Sub
    

    Module1:

    Option Explicit
    
    Public Sub populateCmb()
        With Workbooks("Main.xlsm").Sheets("Sheet1").cmb_list
            .AddItem "Copy Sheet2"
            .AddItem "Copy Sheet3"
        End With
    End Sub
    
    Public Sub CopyWorkSheet(ByVal wsID As Long)
        With Workbooks("Main.xlsm")
            With .Sheets("Sheet" & wsID)
                .Visible = True
                .Copy
                .Visible = xlVeryHidden
            End With
            .Close False    'Closes Main.xlsm, without saving it
        End With
    End Sub
    

Leave a reply to - Strange behavior when closing a workbook

◀ Go back