Message on close

amna77

Active Member
Joined
May 9, 2002
Messages
251
hi, I am trying to create a message when some one close the file then mesasge should come up.
ok if some one opens that file, and make some changes, then when he close the file, I want a message if he did not put his name in cell U1. basically I want to capture a name in cell U1, like who update this file last time.

Thanks in advance
Amna
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Place this or similar code in the ThisWorkbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.Saved = False Then
lastuser = InputBox("You have made changes to the workbook. Please enter your last name")
Range("$U$1").Value = lastuser
End If
End Sub
This message was edited by lenze on 2002-10-23 09:42
 

amna77

Active Member
Joined
May 9, 2002
Messages
251
Thankyou very much for great help. Its working pretty good.
Once again, thanks
 

amna77

Active Member
Joined
May 9, 2002
Messages
251
Right now this function works fine, when I close the workbook without saving it, then message box comes up, but is it possible to show message even after saving the workwork,if they did not update the name in the U1 cell?
so bassically when ever they make changes they have to enter his name in the cell. does not matter if its a same person. becasue once you update the name then I have time stamp in cell x1.
so can you please help me with this problem.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Instead of before close (or along with it), try the before save event. Something like

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
lastuser = InputBox("You have made changes to the workbook. Please enter your last name")
Range("$U$1").Value = lastuser

End Sub
 

amna77

Active Member
Joined
May 9, 2002
Messages
251
I got it, Thanks

I just removed the if statment. Actibe workbook.saved =false.

now it works, does not matter if you saved the work book or not, message box come up.
Thanks
 

amna77

Active Member
Joined
May 9, 2002
Messages
251

ADVERTISEMENT

Is it possible to show message only when I make changes in the work book. What if I just opened it up and did not make any single change then I don't want to show message. Is it possible?
Thanks for all help.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Try adding this line as the first line in your code

If ActiveWorkBook.Saved=True Then Exit Sub

Somthing like this
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkBook.Saved=True Then Exit Sub
lastuser = InputBox("You have made changes to the workbook. Please enter your last name")
Range("$U$1").Value = lastuser

End Sub
 

amna77

Active Member
Joined
May 9, 2002
Messages
251
No, for me its same. I mean I try adding that line, but still if I don't make any changes in the workbook, still give me message, it should not.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
Post the code you curremtly have in your ThisWorkBook Module
This message was edited by lenze on 2002-10-23 11:31
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

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