saving multiple files while

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Saving multiple files while keeping Master unchanged

Saving multiple files while keeping Master unchanged

resolvedResolved · Urgent Priority · Version 2013

Lone has attended:
Excel VBA Intermediate course

Saving multiple files while keeping Master unchanged

Hi. I have a master macro enable workbook named ...._MASTER.xlsm I need a macro that tests the filename status to check if it is the Master or a known/already saved filename with another name other than Master (e.g., version1 or record1 or analysis1).Master acts as the default GUI system. The procedure is as follows:
(1)when the Run sub/Userform is clicked on the Master, the 1st macro is invoked to reset all values to default and pops-up a SaveAs dialog so user can define a file path and filename.
(2) An interface (GUI) then comes up loaded with default values so user can input data that gets sent to excel.

I have a command at the end of data input for user to save changes made together with macros to load back the values entered in GUI controls and saved in excel back to GUI controls. Everything works fine so that I have record1, record2, etc as separate multiple workbooks as well as the unchanged Master, except the changes are not happening when i open record 1, 2, etc. Also, when I open record1,2,etc and click "Run sub/Userform" I do not wish to see the SaveAs dialog since the filename is not the Master.If I continue and save say Recod1 again in its existing location, click yes to replace existing record so i can continue, the GUI loads up with default values and not with the changes made to record1 filename. I have the following code immediately below the code for resetting all values to default on the Master (hopefully to test if filename is Master or not and give appropriate actions such as no SaveAs dialog if its not Master and saving changing to enable multiple files, each with its respective dataset):

Filename = Application.GetSaveAsFilename
MsgBox (Filename)
ThisWorkbook.Saveas (Filename)

Private Sub TestingFileNameStatus ()
Dim Filename As String
If (Filename) <> "....Master.xlsm" Then
GetSaveAsFilename.Enabled = False
Else
GetSaveAsFilename.Enabled = True
End If
End Sub

Will appreciate your guidance

RE: Saving multiple files while keeping Master unchanged

Hi Lone,

Thank you for the forum question.


I have attached a workbook.

To load data to a userform when open the file you can use the userform_initialize event (See example).

I have also changed your code in the module so you only will see the save as dialog box when you want.




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

master.xlsm

RE: Saving multiple files while keeping Master unchanged

Hi Jens,

Thank you for your response. I was able to download the wb and examine it. There are 3 lines of code in module 1. When I click on Run Sub/UserForm under Userform1, it goes into the run mode as expected but nothing happens when i click on the command button "Run" in UserForm1 (I think its because the click event for this command button labelled "Run" and showing as "CommandButton1_Click()" has no code in it.

Kindest regards,

L

RE: Saving multiple files while keeping Master unchanged

Hi Lone,


I have changed the workbook (please find attached workbook). Have a look at the click event. You will find two options. One of the options is commented out. This option will work if the word master is a part of the file name. The non commented out code will work if the file name is exactly master.xlsm.

I hope that I have guided you in the right direction.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

master.xlsm

RE: Saving multiple files while keeping Master unchanged

Hi Jens,

Thank you for your response. The click event is now working by loading a SaveAs dialog as expected (after activating the code by removing the comment marks). However, when I save this workbook by typing a file and defining a path, it doesn't work (nothing is found in the location I saved the file). I have also tried naming the file specifically with as .xlsm at the end, using the word master for case 1 and something else for case 2. On both cases, nothing is saved.

Q1. I am looking to first test if this workbook can be saved and is able to distinguish between master (the default workbook where no changes are made) and any other file names (e.g., ...._v1, ...._v2, etc where changes were made on the workbook) so I can then see how to apply to my work. The file names (...._v1, ..._v2) do not have to have the word master on them since they are not the master but existing/known file names that would have been named and saved as such. In my real macro-enabled workbook (master), the SaveAs dialog appears and I am able to save by defining a new file name (e.g., ..._v1) and path. I am also able to see this saved file in its location, open it but do not want to see the SaveAs dialog again since I have already named the file to distinguish it from the master. The idea is to distribute the macro-enabled workbook (as master) and allow saving of different datasets (dataset 1 to be saved as ..._v1, dataset2 to be saved as ..._v2, etc). Procedure: If the filename is master (containing code to load default values), invoke a saveas dialog first time to allow naming the file as ..._v1 and enter data, close and save changes. If the file name is not the master, bypass the saveas dialog to allow getting to the existing/known file which is loaded with values entered and saved as say ..._v1 (similar to any microsoft products such as excel where user can get directly to a saved workbook or open a blank workbook loaded with default values (sheet1, sheet2, etc).

Q2. On a separate note, how can I dynamically show the name of a person in a textbox within a userform that is an output containing other info. For example, If I have captured the name of a person in a textbox (e.g. textbox1) in one of my multipages during data entry, I wish to display the name of this same person in a separate textbox (textbox2) within an output userform at the end, for the sake of authenticating the output by dynamically/automatically connecting the contents of textbox1 with textbox2 so that the userform will show the contents of texbox1 in textbox 2 rather than show a blank textbox2?

I hope I have provided enough info under Q1 to clarify further and look forward to your guidance.

RE: Saving multiple files while keeping Master unchanged

Hi Lone

All you need to do is to change Application.GetSaveAsFilename to Application.Dialogs(xlDialogSaveAs).Show.


If you want to show the input from one text box in a userform in another text box in a userform you need to do the same as if you want to show it in a cell. First the destination = the source . me.txtName2=me.txtName1.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Saving multiple files while keeping Master unchanged

Hi Jens,

Thank you for your response. Apologies for the late response (couldn't access the forum while travelling). I just managed with the linking of textboxes. I am unable to get the workbook to work as intended by using it as a master and saving different versions based on changes made (will spend some more time to explore further).

On a separate note, how can I ensure that my date and timestamp on a userform textbox works consistently without confusing UK with US time. I have the code:
txtDateTimeStamp....=Format(txt...Value, "dd mmmm yyyy hh:mm")
This was working well last month but is now showing 12 March 2014 instead of 3 December 2014 or 12 January 2014 instead of 1 December 2014.

Also, how can I format the contents of a messagebox? (e.g., increase the font size, change font type, turn text to bold, etc).

Thanks in advance for your guidance.

RE: Saving multiple files while keeping Master unchanged

Hi Lone

Please raise a new forum question when you have new questions. I am not able to answer forum questions the next days. If you raise a new question an available trainer will answer.
Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Saving multiple files while keeping Master unchanged

Ok, noted.

Kindest regards

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Create own ribbon tab - Excel 2010

a. In Excel click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the New Tab button (below the list of tabs on the right hand side of the dialog box)
e. Select the New Tab (Custom) and enter a name, by clicking the Rename button (below the list of tabs on the right hand side of the dialog box)
f. Select the New Group (Custom) and enter a name, by clicking the Rename button (below the list of tabs on the right hand side of the dialog box)
g. Add commands to your tab and group by locating them on the list on the right hand side (remembering that you can change the list using the drop down box at the top of the list of commands) and clicking the Add button between the two panes to add them to your tab and group
h. You can rearrange the commands in your group, the groups on any tab or the tabs, using the up and down arrow buttons beside the list of tabs.
i. Click OK to apply your changes

View all Excel hints and tips


Server loaded in 0.08 secs.