Message on saving

bionicle

Board Regular
Joined
Apr 23, 2009
Messages
182
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I have the following formula that isn't right.

I'm trying to change the message when you save the file, I need it to ask whether the data has been uploaded, if its a "yes" it will save and close, if its a "no" the it closes the message and opens the worksheet "data input"
This is what I have so far:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Have you uploaded the data?", vbYesNo) = vbYes Then Exit Sub
If MsgBox("upload now?", vbYesNo) = vbYes Then

This is where I'm stuck as I can't for the life of me remember what comes next...

Also, is there a way of stopping the workbook from being closed if there is no change made to the data input tab?

Thanks for you help in advance.
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,636
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Have you uploaded the data?", vbYesNo) = vbNo Then
   Cancel = True
   Sheets("Data Input").Select
Else
   Me.Save
End If
End Sub
 
Upvote 0

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
2,666
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
To open the worksheet data input

VBA Code:
   Worksheets("data input").Activate

However, it's not clear when you want this to happen in the sequence of steps. Before you ask to "upload now"? After you ask? After you ask but only if the answer is Yes?

Also, what does it mean "if there is no change made to the data input tab"? No change made when?

Once this sub is called you can stop the workbook from being closed with

VBA Code:
Cancel = True

but it's not clear what conditions should be checked, or even if those conditions can be checked in this Sub.
 
Upvote 0

bionicle

Board Regular
Joined
Apr 23, 2009
Messages
182
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If MsgBox("Have you uploaded the data?", vbYesNo) = vbNo Then
   Cancel = True
   Sheets("Data Input").Select
Else
   Me.Save
End If
End Sub
Thank you worked a treat.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,636
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,186,707
Messages
5,959,290
Members
438,411
Latest member
bmathew16

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top