Save a file with Instructions on how to name the file

[Dchav5]

I am creating a file and I would like there to be a prompt so that when a person closes the file (will be set as a read only), a message pops up saying how the file should be named - very similar to how you are asked whether or not you are sure you want to save a file before closing if you haven't already saved the file. Thank you very much.

 

[NARAYANK991]

Hi ,


I am not able to understand one point ; you say the file is read-only , which means the user cannot modify the file. What is the purpose of his naming the file while closing it ? Unless , he chooses to use Save As ; but do you want the user to follow a convention even in this ?


Since this will be done through a macro , the file will have to be saved with a .xlsm extension ( not a .xlsx extension ).


You can make use of two events :


1. Workbook_BeforeClose(Cancel As Boolean)


2. Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Any comments ?


Narayan

 

[Dchav5]

Thank you very much for getting back to me. The reason why I want this prompt is because I am creating a sales tracking sheet for my brokers. Each time the open the file and are asked to "save as" I want them to have no question of how to name the file.


In regards to your script above. Do I hit record Marco and enter #1 + #2 above into the description section? Thank you.

 

[Luke M]

Sounds like you want to use the BeforeSave event then.

To install/setup, write click on a sheet tab, view code. Then, in project explorer window, double click on the ThisWorkbook module. Paste this in, modify as desired.

[pre]

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim MyMessage As String
Dim MyHeader As String

'Setup your messages
MyHeader = "How to Name File"

'Use vbCr to create a line break, left aligned.
'Use vbCr if you want the line break to be center aligned
MyMessage = "You need to name the file using" & vbCr & _
"the correct methods."

'Display message
MsgBox MyMessage, vbOKOnly, MyHeader

'If you want to cancel the Save, uncomment this next line
'Cancel = True
End Sub

[/pre]

 

[Dchav5]

This is exactly what I wanted. Thank you!