Display yes/no msx box on exiting Excel

**elf_de_la_nuit** · 04-30-2009, 08:11 AM

Hello,

I am very new to VBA and struggling with a code to do a simple job. I am sure the solution is something very easy, any help on this will be appreciated..

I have developed this spreadsheet in my company which will be updated by more than 3 persons on an ongoing basis. One of the sheets in this workbook contains a simple table where the user documents all changes he has made in his session. The idea is for a new user to know in one glance the last 10 changes made to the workbook. So i would like a msg box to appear when a user clicks on X to close excel once his session is over. This msg box should be a yes no msg box and ask something like "Have you filled in the changes made table on the first sheet?" If the asnwer is Yes then save and close and if no, then go to the first sheet to cell B2.

Any ideas??

**mudraker** · 04-30-2009, 08:18 AM

This will only work if Macros are enabled

Change Sheet1 to suit

'These instructions pre typed & are worded to cater for the novice programmer

'To install macro to correct location

'Copy this macro
'GoTo Excel
'Open VB Editor by pressing Alt + F11
'Just below the menus & toolbars on the left you should see Project - VbaProject window
'In this window you will see ThisWorkbook
'double click on ThisWorkbook - opens ThisWorkbook module sheet
'Paste macro code into big window on right

Code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   If MsgBox("Have you filled in the changes made table on the first sheet?", vbYesNo + vbDefaultButton2 + vbQuestion) = vbNo Then
      Cancel = True
      Sheets("Sheet1").Activate
      Range("b2").Select
      Exit Sub
   End If
End Sub

Thread: Display yes/no msx box on exiting Excel

Thread Tools

Display

Display yes/no msx box on exiting Excel

Re: Display yes/no msx box on exiting Excel

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions