• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Closing and reopening a workbook

Brijesh

Member
Hi,
I have build an excel model. I have developed a macro which generates some results for scenario number 1 to 10. To display these results, macro creates 10 sheets (sheet names: 1, 2, 3, ....., 10). Name of the macro is "Test".

Once the macro is run and I have to rerun the macro, I have to delete above sheets (so that these sheets could be created again). For this purpose I have included the codes to delete the sheets in the beginning of the above macro (so that the sheets generated in the previous run of macro is first deleted and then the scenario are run and fresh sheets are regenerated). When I rerun the macro, Sheets generated in previous run of macro are deleted but the fresh sheets with same name (1, 2, 3, ..., 10) are not created. I understand that for this I have to close and reopen the workbook. I tried following code (written within the same workbook) to close, reopen and run the "Test" macro.

Code:
Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "Test"
ThisWorkbook.Close False
End Sub
But above code is not opening my workbook. Instead it is opening a new blank workbook. Any help will be great.

Thanks
Brijesh
 
Last edited by a moderator:
Hi !

Instead of your procedure CloseMe as the code workbook must still be
opened to run its procedure with OnTime,
just clear worksheets using .UsedRange.Clear
 
The code I have written is working now. But if I want to provide a button on one of the worksheet in the workbook and assign the above macro to this button (so that a user can run the macro by hitting this button), it's not working properly. When I provide a button and assign the macro to it and then click the button, the macro starts behaving very strange. Rather than opening the same workbook again, it opens a new blank workbook.

Request you to try the simple code below in any workbook

Code:
Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
ThisWorkbook.Close False
End Sub
Sub OpenMe()
MsgBox "I'm Back!"
End Sub

Put the above code in a module and then provide a button on one of any of the sheet in the workbook and assign the macro "CloseMe" to this button. Now if you click on the button, you will see unexpected behavior of this macro. And if you directly run macro "CloseMe" by opening the macros window and clicking on run, the results are as per expectation. Is there any other way to produce any button on any of the sheet for this macro?
 
Last edited by a moderator:
This works fine for me with a Form button (or an ActiveX one) in 2010:
Code:
Sub CloseMe()
Application.OnTime Now + TimeValue("00:00:10"), "OpenMe"
ThisWorkbook.Close False
End Sub
Sub OpenMe()
MsgBox "I'm Back!"
ThisWorkbook.Activate
End Sub
 
Back
Top