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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Post the code you curremtly have in your ThisWorkBook Module
This message was edited by lenze on 2002-10-23 11:31
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top