CODEWITHSUNDEEP
excelvba
DryBSMT
DryBSMT

Reputation: 65

How do I create a VBA Pop Up message based on file name?

I have created a spreadsheet template that I would like the user to save immediately with a new name. I have VBA code that creates the new name and puts it in the save as dialogue box.

I would like to have a pop up that tells the user to 'Save As' the file as soon as the file is opened. I would like it to only show up if the template is opened and not after it is saved with a new name.

Private Sub Workbook_Open()
MsgBox "You MUST use 'Save As' to save this file with a new name before editing"
End Sub

Once the file has been saved with a different name I don't want the Pop up to show up. I was thinking that the sub could read the name of the file and if it matches the predefined template name "Field Time Template.xlxm".

This may be a new question but it would also be nice to have a script that let prevented or notified user if they hit 'Save' instead of 'Save As'

Thanks for the help.

Upvotes: 1

Views: 820

Answers (1)

JvdV
JvdV

Reputation: 75920

You could think about opening a save as prompt upon opening of your template after checking the name of your activeworkbook. Upon canceling or overwriting your template, the workbook would close.

Private Sub Workbook_Open()

NewName = ActiveWorkbook.Sheets(1).Cells(1, 27).Value
If ThisWorkbook.Name = "Field Time Template.xlsm" Then      
    If Application.Dialogs(xlDialogSaveAs).Show(NewName) Then
        If ThisWorkbook.Name = "Field Time Template.xlsm" Then ActiveWorkbook.Close savechanges:=False
    Else
        ActiveWorkbook.Close savechanges:=False
    End If
End If

End Sub

This way I guess your second question would no longer matter.

Upvotes: 1

Related Questions