Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Create a prompt to recalculate sheet before closing

Copper Contributor

I'm doing things in what is no doubt an inefficient manner, but I'm not a programmer...

 

I end up with large spreadsheets (>35000 rows x ~250 columns. Each cell has aformula with nested if then else formulas for comparing columns and reporting back matching content or a term describing discrepancies. I'm a little short on RAM, so to speed up my cut/paste/sort compare analyses I turned off the preview before pasting option and don't do automatic recalculations, doing them manually instead. Based on performance patterns, I think excel recalculates every time it autosaves, which I have set for every 10 minutes. First off, is this correct or is there another likely reason things bog down? If the former, I want to turn off the calculate on saving option, but am afraid I will forget to do that manually when I close a workbook, complicating my life when I go back to that sheet later .

 

If my interpretations are correct, is there a way to set up a prompt when closing a sheet or workbook to ask if I want to recalculate before closing?

 

Thanks.

2 Replies

@PeeJay365 

You can create a prompt to ask if you want to recalculate before closing a workbook in Excel. You can use a Workbook event in VBA (Visual Basic for Applications) to achieve this.

Here are the steps:

  1. Open the Visual Basic for Applications Editor:
    • Press Alt + F11 to open the VBA editor.
  2. Insert a Module:
    • In the VBA editor, right-click on any item in the Project Explorer on the left.
    • Choose Insert -> Module to insert a new module.
  3. Paste the Following Code:
    • Copy and paste the following VBA code into the module:

Vba code (is untested):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim answer As VbMsgBoxResult
    ' Display a message box prompting to recalculate before closing
    answer = MsgBox("Do you want to recalculate before closing?", vbQuestion + vbYesNo, "Recalculate Before Closing")
    
    ' Check the user's response
    If answer = vbYes Then
        ' Recalculate the workbook
        Application.Calculate
    End If
End Sub

4. Close the VBA Editor:

    • Close the VBA editor.

Now, when you attempt to close the workbook, Excel will prompt you with a message asking if you want to recalculate before closing. If you choose "Yes," it will recalculate the workbook before closing.

Keep in mind that enabling macros is necessary for this to work. Additionally, saving your workbook with macros as a macro-enabled workbook (.xlsm) is required.

The text and steps were edited with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@NikolinoDE Thank you so much. I'm going to have to look up what it is and how to use VBA (I'm an AARP member) but I think I can probably handle that.

 

I appreciate the help!