Solved: Saving code into a "normal" template

[samuelwright]

Hi all, hope you are well on this chilly english afternoon!

I have written a little file name saving macro in Word that I haved saved into the normal template by using the Macro Organizer..in this way, I am able to access the macro every time I open Word and the document name is saved each time how I want it.

However, after copying and pasting the same macro into Excell, I cannot find an equivalent "normal" default template that I can use the macro organizer to save the macro in. The result is that when I open Excel each time the macro is not there.

I also have this problem with Power Point.

Is there any way of saving macros into normal/default templates so that they are accessible each time for Excell and PowerPoint?

Thanks!

[Marcster]

Hi Samuel,
In Excel: 'Normal' is called PERSONAL.XLS. Gets created auto when you save a macro to it.
Tools>Macro>Macros
Macros In 'PERSONAL.XLS

HTH,

Marcster.

[mvidas]

For excel, you have the PERSONAL.XLS file to store macros to be readily available.

If you don't have a personal.xls file, it is easy to make one. From excel, go to Tools, then Macro, then Record New Macro. On the 'Record Macro' window, change the "Store macro in" to 'Personal Macro Workbook', click ok, then click the Stop Recording button. Go to your VBA editor, and you should now see the VBAproject for personal.xls, with a blank recorded macro in Module1.

What that does is create a new workbook in your xlstart folder named 'personal.xls', which is set to be hidden. When excel starts, it will also start your hidden personal.xls file, which contains your macros. You can create this yourself there, or just record a new macro as specified above, and you'll have the file available now.

I can't help you with powerpoint, unfortunately. I never use it, so am not sure of how it works there.

Matt

[samuelwright]

Hi Marcster & Matt

Thanks I did that for my project and it works fine, thank you.

So say I have saved the spreadsheet with the macro in it as a template. Now I want to distribute this to lots of people on our network, and basically reduce their work load as much as possible: I really dont want to have to make them do Copy and paste of code etc. How can I distribute it (e.g. via email attachment) to their personal.xls?

Thanks...

[Killian]

The way to do this for Excel and PowerPoint is to create AddIns.
These are "hidden" Files that are loaded when the app starts up so the code is run (normally to build a menu item) - the main difference with Word is that toolbars are (rather usefully) attached to templates - with Excel and PowerPoint, you need to build/delete your menu items with code in your AddIn (using the WorkBook_Open/Close event in Excel and in PPT, marcos named "Auto_Open" and "Auto_Close"

One additional difference in PowerPoint (as if that wasn't enough inconsistency between application to be going on with) is that you need to work on the code with the file as a .PPT then - when you've finished - save as a .PPA. The reason being that while you can see the code (if you do a registry hack) you can't save the changes as a PPA

To run the AddIns on application startup:
Excel: save the addin to your XLSTART directory
PPT: Use Tools>AddIns to load it

I've attached an example of each (once you've had a look at the code in the PPT, you'll need to save ist as a PPA)

[samuelwright]

Hi Killian

Thanks for the advice...unfortunately I cannot open Zip files on my work computer! I think my employers have a morbid fear of them (not sure why they cannot leave it down to the individual's discretion...)

This is the method that I have adopted:

1. I save the spreadsheet/presentation with the macros in them.
2. I create a new button on the menu bar and customize it so that it calls the macro
3. This button is now availabel in other spreadsheets/presentations.

The only drawback to this is that I still have to keep the original spreadsheet/presentation.

Are add-ins very difficult to make? I have never used/done them before?

Thanks

[Killian]

The problem is when you create a menu item in Excel, you add it to your app profile, not the workbook (it is possible to attach toolbars to a workbook, but when you close the workbook, the toolbar remains). This is why to effectively distribute code, it's best to deploy an addin to users that they copy to their XLSTART directory so it will load when Excel starts (or they can place it in their profile's Addins directory and enable it through Tools>AddIns).
You can then use the Workbook Open and close event to build/delete the menu.
Here's the code and a mini-tutorial:
In the VBE, go to the "ThisWorkbook" code pane and select those events using the dropdown menus above it. The basic framework for menu code in the ThisWorkBook module looks like this[VBA]Option Explicit

Private Sub Workbook_Open()
DeleteMenu 'delete any exitsing menus
BuildMenu 'create new menu
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu
End Sub

Private Sub BuildMenu()

Dim cb As CommandBarControl
Dim cbItem As CommandBarButton

With Application.CommandBars("Worksheet Menu Bar") 'with the main menu bar
Set cb = .Controls.Add(msoControlPopup) 'add a menu
With cb 'with the nem menu
.Caption = "New Menu" 'set the caption

Set cbItem = .Controls.Add(msoControlButton) 'add a new item
With cbItem
.Caption = "New Item" 'set its caption
.OnAction = "TestMacro" 'set the marco to run from it
End With

Set cbItem = .Controls.Add(msoControlButton) 'add another item
With cbItem
.Caption = "New Item 2" 'set its caption
.OnAction = "TestMacro2" 'etc

End With
End With
End With

End Sub

Private Sub DeleteMenu()

Dim cb As CommandBarControl
'deletes all the menus in the main menu bar called "New Menu"
For Each cb In Application.CommandBars("Worksheet Menu Bar").Controls
If cb.Caption = "New Menu" Then cb.Delete
Next
End Sub[/VBA]The action commands are the macros you are calling, for this example, add a standard module and insert the following[VBA]Option Explicit

Sub TestMacro()
'add code for action here
MsgBox "This is a test"
End Sub

Sub TestMacro2()
'add code for action here
MsgBox "This is another test"
End Sub[/VBA]Now you can go back to Excel and save the workbook as an AddIn (.XLA). The dialog should go to your default AddIns directory, so save it there. You can now select it in Tools>AddIns to set it to load when Excel starts.

PowerPoint works in a similar way but does not have presentation events (they can be enabled by creating an event class, but thats another story).
So to get code to fire when opening and closing a presentation, create two routines in a standard module named "Auto_Open" and "Auto_Close". The rest of the code is identical so for this example, you have a single standard module that looks like this[VBA]Option Explicit

Sub Auto_Open()
BuildToolBar
End Sub

Sub Auto_Close()
DeleteToolbar
End Sub

Sub BuildToolBar()
Dim cb As CommandBarControl
Dim cbItem As CommandBarButton

With Application.CommandBars("Menu Bar")
Set cb = .Controls.Add(msoControlPopup)
With cb
.Caption = "New Menu"
Set cbItem = .Controls.Add(msoControlButton)
cbItem.Caption = "New Item"
cbItem.OnAction = "TestMacro"
End With
End With
End Sub

Sub DeleteToolbar()
Dim cb As CommandBarControl

For Each cb In Application.CommandBars("Menu Bar").Controls
If cb.Caption = "New Menu" Then cb.Delete
Next
End Sub

Sub TestMacro()
'add code for action here
MsgBox "This is a test"
End Sub[/VBA]Once your happy with the result, save the PPT (this is the original you can work on) then, for distribution save a copy as a .PPA. As with Excel, the dialog should go to your default AddIns directory. You can now select it in Tools>AddIns to set it to load when PowerPoint starts.

That deals with the basics. Hopefully I've explained it clearly - its worth also seaching MSDN for more detail

[samuelwright]

Hi Killian

That gave me quite a few ideas, thank you! Say I set a macro called "Setup" to run when the workbook is opened using the workbook_open event. This is an input box which the user types a value in. The macro then creates a new button on the menu command bar which, when clicked, calls a userform which has that information taken from the input box.

However, I would like to make sure that the initial macro that I call on the open workbook event is only called once, but that the userform remembers the information from the input box. Any ideas? Here is the code I have so far:

[VBA]Private Sub Workbook_Open()' this is in the thisworkbook module


setup 'calls the sub written belowEnd Sub [/VBA]

[VBA] Sub setup()

Set myMenuBar = CommandBars.ActiveMenuBar
Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
newMenu.Caption = "FileNameSave"
newMenu.OnAction = "FOI" ' this is the sub called FOI, involving a userform1, see below
MyValue = InputBox("Enter your Post", "Amend User", "<Post Title>")
newMenu.TooltipText = MyValue

End Sub[/VBA]

[VBA]Sub FOI()
If Application.Workbooks.Count > 0 Then ' i.e. MS Excell is open, but no workbooks open
Load UserForm1
UserForm1.Show
Else
MsgBox "Nothing to save"
End If

End Sub [/VBA]

[VBA] Private Function ValidFileName() As Boolean

'---Function to make sure that fiel names do not have illegal characters in them.
ValidFileName = Not (FileName Like "*[/\:*?""<>|]*&")
End Function
'---Sub that brings up an input box to amend your post title
Private Sub AmendUser_Click()
Dim Message, Title, Default, MyValue

Message = "Amend your Title"
Title = "Amend Title" ' Set title.
Default = UserForm1.Caption ' Sets the default post title as the original set up.
' Display message, title, and default value.
MyValue = InputBox(Message, Title, Default)

If MyValue <> "" Then
UserForm1.Caption = MyValue
Post.Text = MyValue
Call Update_Filename
Set myMenuBar = CommandBars.ActiveMenuBar
For Each cb In myMenuBar.Controls
If cb.Caption = "FileNameSave" Then
cb.TooltipText = MyValue
End If
Next
End If
End Sub
'---This sub opens the Save As box and puts the file path name string into the Save As Box.
Private Sub Save_Click()
Dim dlgSaveAs As FileDialog
Dim Path As String
Path = ActiveWorkbook.Path

If Path = "" Then
Path = Application.DefaultFilePath
End If
Path = Path + "\"
If ValidFileName Then 'i.e. the file path name contains no illegal characters
'Save file with new extension
Set dlgSaveAs = Application.FileDialog(FileDialogType:=msoFileDialogSaveAs)
dlgSaveAs.InitialFileName = Path + Trim(FileName) + ".doc"
dlgSaveAs.Show
dlgSaveAs.Execute
Unload UserForm1
Else
MsgBox ("Invalid Filename - the characters / \ : * "" < > | $ & ' ` ? are not allowed in a Filename")
End If
End Sub
'---This sub compiles all the information that is written on the userform and combines it
'---into a single string.
Private Sub Update_Filename()
If Version.Value = "" Then
FileName = SaveDate + "-" + Left(Class, 1) + "-" + Title + "-" + StatusMenu.Value + "-" + Post
Else
FileName = SaveDate + "-" + Left(Class, 1) + "-" + Title + "-" + StatusMenu.Value + "-" + Post + "-V" + Version
End If
If DescriptorCheck.Value = False Then
FileName = SaveDate + "-" + Left(Class, 1) + "-" + DescriptorMenu.Value + "-" + Title + "-" + StatusMenu.Value + "-" + Post + "-V" + Version
Else
FileName = SaveDate + "-" + Left(Class, 1) + "-" + Title + "-" + StatusMenu.Value + "-" + Post + "-V" + Version
End If
If CaveatCheck.Value = False Then
FileName = SaveDate + "-" + Left(Class, 1) + "-" + CaveatMenu.Value + "-" + Title + "-" + StatusMenu.Value + "-" + Post + "-V" + Version
Else
FileName = SaveDate + "-" + Left(Class, 1) + "-" + Title + "-" + StatusMenu.Value + "-" + Post + "-V" + Version
End If
If DescriptorCheck.Value = False And CaveatCheck.Value = False Then
FileName = SaveDate + "-" + Left(Class, 1) + "-" + DescriptorMenu.Value + "-" + CaveatMenu.Value + "-" + Title + "-" + StatusMenu.Value + "-" + Post + "-V" + Version
End If
End Sub
Private Sub Quit_Click()
Unload UserForm1
End Sub
'---Resets the colour of the text box from red to white when the user enters some text
Private Sub Title_Enter()
If UserForm1.Title.BackColor <> RGB(255, 255, 255) Then
UserForm1.Title.BackColor = RGB(255, 255, 255)
Title = ""
End If
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub Version_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub Title_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub SaveDate_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub CaveatCheck_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub DescriptorCheck_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub StatusMenu_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub Post_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub Class_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub DescriptorMenu_Change()
Call Update_Filename
End Sub
'---This sub updates the file name in the preview window as the user changes the options
Private Sub CaveatMenu_Change()
Call Update_Filename
End Sub
'---The initialize event is used to populate the fields of the user form
Private Sub UserForm_Initialize()

Dim UserPost As String

'---Sets Class menu (i.e. unclassified or restricted)

Class.AddItem "W"
Class.AddItem "T"

'---Sets document status menu

StatusMenu.AddItem "DRAFT"
StatusMenu.AddItem "FINAL"
StatusMenu.AddItem "UNDER REVIEW"

'---Sets Descriptor Menu

DescriptorMenu.AddItem ("APPOINTMENTS")
DescriptorMenu.AddItem ("BUDGET")
DescriptorMenu.AddItem ("COMMERCIAL")
DescriptorMenu.AddItem ("CONTRACTS")
'---Sets the caveat menu
CaveatMenu.AddItem ("ACTION")
CaveatMenu.AddItem ("INFORMATION")
CaveatMenu.AddItem ("PRIVATE")

'---Sets document date

SaveDate.Value = Format(Now(), "YYYYMMDD")

Set myMenuBar = CommandBars.ActiveMenuBar
For Each cb In myMenuBar.Controls
If cb.Caption = "FileNameSave" Then
UserPost = cb.TooltipText
End If
Next

'---Sets the user post value based on the original set up macro result.

If UserPost = "" Then
UserPost = "No user set"
End If
UserForm1.Caption = UserPost
Post.Value = UserPost 'UserForm1.Caption 'UserPost
FileName.Value = ""
'Call getTitle
Call Update_Filename
End Sub
[/VBA]

Hoep this makes sense

[Killian]

There are a few ways to approach holding that value for the userform. The most straight-forward is to declare a global variable, meaning one that all routines, functions and forms can access.
Declare it at the top of a standard module (like the one I would imagine "FOI" is in)[VBA]Public MyValue as String[/VBA]
Now you will be able to retrieve that value in your UserForm code

You need to be careful with allowing variable to have this degree of "visibility" (known as scope). Just as all your routines can read it, they can also change it, which can cause debugging nightmares, but if it's initialized in one place and just read in another, it's managable.

Technically speaking, you already had it available... you set it as the tooltip text on your control, so you could get it from there. The paramater property of a controlbutton is usually used for this - you could also save it in a cell of an AddIn's hidden worksheet, in the registry, etc, etc - but I think a global variable will do the job for you

[samuelwright]

Thanks Killian, I will try that.

Although this is probably the wrong forum for "advertising", can you reccommend a good book for a beginner-improver for VBA? I already have a basic one called VBA for Excel: it has been a good introduction to VBA structure, i.e. subs, functions etc, and logic routines. I am now looking for a bookwhich can help introduce more sophisticated properties/functions etc, particularly to help with MS Office applications.

All your help has been clear and easy to understand, and most importantly, effective, so I would like to thank you for all your help.

[samuelwright]

Gnarr! This is bugging me now!

Hey Killian, I think I am being dumb here. I have read up on Tooltiptexts on Help, I think I get what they do, but I am still not sure why this code wil not do what I want it to (the truth is I have taken over a project off someone who knows VBA well, but is infrequently contactable-therefore I am trying to make something work that I am not sure about). I copied the following code from a Word Project and tried to make it fit in Excel. But first, here is what I want to do, with reasoning:

1. User opens Excel for the first time after the add-in has been installed.
2. User is presented with an Input box (either by using Workbookopen event or by manually running macro) asking them to write their post title in (e.g. Head of Accounts - I thought about accessing the user profile to get their login name, but the login names are done by surname-making it difficult to trace which dept has originated a piece of work). I want this input box to only appear the first time Excel opens, so that they do not have to keep re-typing their post each time they open Excel.
3. I have tried to store this piece of Input information as a tooltiptext, and then subsequently written it to the UserForm Caption and a textbox in the userform. It stores it in Word, but here it does not want to remember.
4. Anyway, after they have entered their post title, a new control appears on the menu bar, which, when clicked, displays a userform which users can fill in details about their documents based on drop down menus etc.
5. They save document, exit excel, reopen excel later and I want them to have the new Menu bar button already there, without having to re-enter their post title.

Like I said, it works in the word project I have, but not in excell. Any chance you could have a look at what I have written and see if I am making errors? (and/or streamline it?)

[Killian]

Well there's an error with the Excel code in that you refer to cbItem, which doesn't exist.
[VBA]cbItem.TooltipText = MyValue
'should be
newMenu.TooltipText = MyValue[/VBA]

That said, I don't really understand the logic behind this code...
here's what's happening at the moment: (assuming this is distributed as an AddIn)
User opens Excel
the AddIn loads
The setup routine builds a new toolbar
The InputBox adds the tooltip

Now, while that session on Excel remains open, the menu and it's tooltip remain (but it's not saved anywhere)
When the user closes Excel, the AddIn unloads
When the user re-starts Excel, we go back to the first list and the user will have to set the variable again.

The reason this works in Word, is that the menu and its settings are saved in the user's Normal.dot, so that value remains. Because Word uses the Normal.dot in the user's profile this work for everyone based on their login.

In Excel, when you distribute an addin, the menu is re-built every time.
If you need to save it between sessions you can save it in the AddIns worksheet (worksheets in AddIns are hidden but accessible to the code)
If the user is given instructions to install the AddIn to their profile's AddIn directory, again it will be user independant.
[VBA]'IN setup
'you wouldn't need a variable to save it
ThisWorkbook.Sheets("Sheet1").Range("A1").Value = _
InputBox("Enter your Post", "Amend User", "<Post Title>")
ThisWorkbook.Save


'IN UserForm_Initialize
'You won't need to loop through the menus to get the tooltip
'The variable doesn't need to be global now
'you can declare it in the UserForm_Initialize routine
UserPost = ThisWorkbook.Sheets("Sheet1").Range("A1").Value[/VBA]
No such luck, however, with PowerPoint - AFAIK an AddIn can't be referred to as a presentation so there's nowhere to save the value.

Looking at this project it does occur to me that you would get the same functionality by saving a user profile in the registry.
The advantage would be that the user would only have to set it once - all the applications could use the same value.
The code could be identical in each application.

[samuelwright]

Hi Killian

Regarding getting user profiles, I have decided to stop trying to make some-one else's project work and just apply some previous advice-in this case yours! I have now referred to a .txt file (a previous bit of advice in another thread from your good self!) that I have written into, say My Documents. This stops all the faffing with input boxes and tootip texts.

However, as a finishing touch, it would be nice to be able to specify where the txt file is saved, by pushing a button in a userform that takes me to Windows Explorer. Then I could browse, and then copy the folder path name directly into the code and "hardwire it in" (or alternatively, copy it into the user profile .txt file like the other information).

Would I use the load property to summon the Explorer Window?

Thanks?

[Killian]

Windows Explorer won't return a string value for the path - for something that works in all apps, you should use the Windows API GetSaveFileName function (which displays a Save As dialog and returns the path - including filename specified).

However, I'm a bit confused as to how this is going to work.
The advantage of using the registry, or the user's homefolder, is that you can refer to the same location in the code for each user.
If you allow the user to navigate to a custom location for their user profile text file, you are going to need to store that location somewhere between application sessions... you might as well store the user's info... which brings us back to the original problem.
And there's no point in putting it in the text file, because it's the path of the text file you need to find when the addin loads.

???

[samuelwright]

How do I write to/read from the Windows Registry (ie the users profile?) is it using the
[VBA] GetSetting("TESTAPP", etc... [/VBA] ? I think you showed me this before? Or is it something else?

Also, would reading from the windows registry only result in the users' logon being returned, rather than a custom profile name?

ty

sam

[Killian]

Your on the right track...
Its up to you to create what ever entries you want to hold there with SaveSetting.
GetSetting allows you to define a default return value if there is nothing there... you can see how to implement that with the code below

GetSetting/SaveSetting deals with the HKEY_CURRENT_USER\Software\VB and VBA Program Settings section of the registry, meaning that its specific to that user for that workstation.
The structure beneath that level is:
KEY (normally your App name, e.g. "FOI")
SECTION (the name of the section, e.g. "UserProfile")
SETTING (the name of the setting, e.g. "Role")
VALUE (the value of the setting, e.g. "Director")

So if you want to add more info to the user profile, like the user name, last used date, etc, then you would add more SETTING's with their VALUE's under the same KEY>SECTION

For the example of the user Role, I would suggest you move back to using the global variable to hold the value - it will be set when the addin loads (if it's not there, the user can then be propmted to enter one) and can then be read when the form is loaded.[VBA]'declared in a standard module
Public strUserRole As String

'#############################
Sub setup()
'the original setup routine
Set myMenuBar = CommandBars.ActiveMenuBar
Set newMenu = myMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
newMenu.Caption = "FileNameSave"
newMenu.OnAction = "FOI"

'initialze the global variable with the reg setting
strUserRole = GetSetting("APPNAME", "UserProfile", "Role", "Nothing")

'If it hasn't been set, show the input box
If strUserRole = "Nothing" Then
SaveUserRole
End If

End Sub

'#############################
Sub SaveUserRole()
'initialze the global variable user prompt
strUserRole = InputBox("Enter your Post", "Amend User", "<Post Title>")
'and save the setting
SaveSetting "APPNAME", "UserProfile", "Role", strUserRole
End Sub[/VBA]

[samuelwright]

Hi Killian, sorry, have been away for a few days. I have tried your code, I think I have almost got it, but I think there is something that I havent done properly with the SaveSetting function. Would you mind having a quick look? Here is the code that is saved in a standard module:

[VBA]
Option Explicit
Public strUserRole As String

Sub Workbook_Open()
DeleteMenu
BuildMenu
End Sub

Sub Workbook_BeforeClose(Cancel As Boolean)
DeleteMenu
End Sub

Sub BuildMenu()

Dim cb As CommandBarControl

With Application.CommandBars("Worksheet Menu Bar") bar
Set cb = .Controls.Add(msoControlPopup, Temporary:=True)
With cb
.Caption = "Save"
.OnAction = "FOI"
End With
End With

'initialze the global variable with the registry setting
strUserRole = GetSetting("FOI", "UserProfile", "Post", "Nothing")

If strUserRole = "Nothing" Then
SaveUserRole
End If

End Sub

Sub SaveUserRole()
'initialize the global variable user prompt
strUserRole = InputBox("Enter your Post", "Amend User", "<Post Title>")
'and save the setting
SaveSetting "FOI", "UserProfile", "Post", strUserRole

SubjectNameForm.Post.Value = strUserRole ' is this part required????

End Sub

Sub DeleteMenu()

Dim cb As CommandBarControl
For Each cb In Application.CommandBars("Worksheet Menu Bar").Controls
If cb.Caption = "Save" Then cb.Delete
Next
End Sub

Sub FOI()
If Application.Workbooks.Count > 0 Then ' i.e. MS Excell is open, but no workbooks open
Load SubjectNameForm
SubjectNameForm.Show
Else
MsgBox "Nothing to save"
End If

End Sub

[/VBA]

The userform (SubjectNameForm) displays a textbox (called Post) which is supposed to have the user post title in it: however, it only displays it once, which leads me to believe that I have written the savesetting function incorectly, or that I have written the getsetting function incorrectly.

There is also a button on the userform which prompts a input box if the user wishes to ammend his/her post title at a later date, see below:

[VBA] Private Sub AmmendAuthor_Click()
strUserRole = InputBox("Enter your Post", "Amend User", "<Post Title>")
'and save the setting
SaveSetting "FOI", "UserProfile", "Post", strUserRole

SubjectNameForm.Post.Value = strUserRole ' is this required??????

End Sub[/VBA]

PS I have been reading up on add-ins, they seem like the best way for my purposes, thank you for the tip.

Sam

[Killian]

Hi Sam,

I think your use of Save/GetSetting is fine - the problem in loading it into the form's text box.
If I understand the situation correctly, the contents of the textbox "Post" is used in the file name - you just want to save it between uses..?

Now that I've revisited this, I think it can be done much more concisely, without any need for prompts or input boxes, with all the code (about a dozen lines) in the userform.
I've attached a sample to demonstate my thinking with lots of comments
let me know if I've missed anything

[mvidas]

FYI, I've split Samohtwerdna's post to Killian into a new thread, so it can get the attention it needs as a separate thread.
http://www.vbaexpress.com/forum/showthread.php?t=6042

Matt

[samuelwright]

Hi Killian

Apologies, but unfortunately I cannot download zip files on my work computer (my only access to the internet). Please would you paste the code in the forum. Thanks, sorry for any convenience...

Sam