+ Reply to Thread
Results 1 to 7 of 7

BeforeSave - Name file as cell value - display message box

  1. #1
    Registered User
    Join Date
    01-18-2022
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2010
    Posts
    3

    BeforeSave - Name file as cell value - display message box

    Hi all,

    I am new to Excel, but there are a lot of videos and tutorials available. So it is great to work with!

    For work I'm trying to make a template, which doesn't overwrite, and when someone clicks on Save or Close, it saves all changes in the workbook as a new document(XLS), named as the value in Cell C4.

    After this I'm trying to get a messagebox displaying: "File is saved as: ('C4'.XLS) & File is stored at: (Path specified in macro).

    I can't seem to get it running when I click on save.

    I tried two different approaches:

    Private Sub workbook_beforesave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Sub filename_cellvalue()
    'Update 20141112
    Dim Path As String
    Dim filename1 As String

    Path = "N:\NV-Production\00 Unit Filling & Packaging\03 Performance\01. Problem Solving RCA\2022\"
    Filename = Range("C4")
    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:=Path & Filename & ".xlsx", FileFormat:=xlNormal

    Application.DisplayAlerts = True

    ActiveWorkbook.Close

    End Sub

    And I tried a new approach as available in the appendix Excel. Can someone please help me?
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,777

    Re: BeforeSave - Name file as cell value - display message box

    You won't be able to do that by clicking Save or Saveas. You can run this macro manually or assign it to a button that you insert on your sheet. Also, in your file you have a macro named "MsgBox". Delete that macro or change its name because "MsgBox" is a reserved term in Excel and if you try to run any macro, you will get an error.
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: BeforeSave - Name file as cell value - display message box

    Hi there,

    See if the attached workbook does what you need.

    To save the template itself you need to MANUALLY (using F5) run the "MANUAL_SaveTemplate" routine. Otherwise the normal "Save" operation will save a copy of the file in the specified folder.

    The following code is inserted in the "ThisWorkbook" VBA CodeModule:

    Please Login or Register  to view this content.

    Hope this helps - please let me know how you get on.

    Regards,

    Greg M
    Attached Files Attached Files

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: BeforeSave - Name file as cell value - display message box

    Hi again,

    SORRY!!!

    To save the file with a .xlsx extension, the following code must be used:

    Please Login or Register  to view this content.
    The amended workbook is attached.

    Hope this helps.

    Regards,

    Greg M
    Attached Files Attached Files
    Last edited by Greg M; 01-18-2022 at 09:08 PM. Reason: Correction of original version

  5. #5
    Registered User
    Join Date
    01-18-2022
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2010
    Posts
    3

    Re: BeforeSave - Name file as cell value - display message box

    Woow! This is great. And when I see the script, this is way to complex for me.

    Here in the Netherlands we use "DD-MM-YYYY", but even so it saves the date 19-1-yyyy. Why is this?

    I can't seem to find "MANUAL_SaveTemplate". It only gives me the option of "printing range". So I can't save the template after I made adjustments to it. I put screenshots in a Word-file.

    ----------------------
    Is it by any means possible to save each document with a unique code or number? This can even start at 1. All documents will be saved in the same folder.
    Attached Files Attached Files

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: BeforeSave - Name file as cell value - display message box

    Hi again,

    Thanks for your feedback.



    Here in the Netherlands we use "DD-MM-YYYY", but even so it saves the date 19-1-yyyy. Why is this?

    Filenames which incorporate the date formatted as yyyy-mm-dd will be displayed in chronological sequence when using File Explorer - this is why I usually suggest that format. On my system the copied files correctly display the creation date as yyyy-mm-dd in the filename, but the date attribute of the file is formatted according to the settings specified in whichever file manager I happen to be using.



    I can't seem to find "MANUAL_SaveTemplate"

    Sorry! You need to open the VB Editor (Alt+F11), place the cursor somewhere (anywhere!) inside the code of the "MANUAL_SaveTemplate" routine, and then press F5 - this will run the selected routine.



    Is it by any means possible to save each document with a unique code or number? This can even start at 1

    Do you want to incorporate a serial number in addition to the creation date or instead of it? Apart from the serial number (and possibly the creation date also), will all files have the same filename (i.e. the value entered in Cell C4)?


    In the screenshot you posted, I couldn't really understand what you meant when you referred to the cells which you want to merge.


    Regards,

    Greg M

  7. #7
    Registered User
    Join Date
    01-18-2022
    Location
    Rotterdam, The Netherlands
    MS-Off Ver
    2010
    Posts
    3

    Re: BeforeSave - Name file as cell value - display message box

    Hi Greg,

    Sorry for the late response. I got sick for a bit due to Covid. As the date is mentioned in the file and in File Explorer, the date in the filename can be replaced by an unique number.

    So using the format, saving a document will automatically start the macro, which saves the a new document according to cell value 'C4' + unique number. Even if this means that it starts at number 1 and just counts the amount of saved files. All files will have the same name than.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Macro to automatically save a file and display message of path of file
    By robertguy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2018, 04:40 AM
  2. Display server message to user after POST file upload
    By cows in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2014, 02:28 PM
  3. copy file to new folder based on cell value and display message box
    By kboy1289 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-05-2013, 03:38 PM
  4. Display username in message box that has a file open
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-03-2010, 01:08 PM
  5. [SOLVED] Checking that file exist and if not display a message
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2006, 10:20 AM
  6. [SOLVED] Display a message when a file is opened.
    By Sheila in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 11-15-2005, 01:20 PM
  7. Replies: 4
    Last Post: 11-01-2005, 04:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1