Permanently Affixing Toobar/Macros

N

Navy

Ok, I've written a bunch of macros for an Electronic Engineering Lo
(for my ship) and built a toolbar to allow easy use of the macros.
Unfortunately, every time a user does a "save" the macro reference
switch to the last saved document instead of the master file
created.

Is it possible to "lock" the macro references in the toolbar to on
specific file? If so, what do I need to do? We are currently jus
leaving the previous days saved file in the normal working director
and making a copy of it in the location it needs to go. I would prefe
to leave the master file only in the working directory and have it'
permissions set to "read-only" to ensure no one ever accidentl
over-writes it.

Thanks in advance for any and all assistance! :)

Cheers,
Nav
 
F

Fherrera

Instead of pausing the macro or even going that route, you may try thi
as well:

With a little bit of VBA:

Basically, if you hit ALT-F11 the VB Editor will pop up. Look for th
macro you have assigned the CTRL-X key combination to (the procedur
should be in a Module in the top left (Project Explorer window)


Code
-------------------

Sub Macro3() ' or whatever the macro is called
'
' Macro3 Macro
' Macro recorded 05/04/2004 by ----- -----

code code code

End Sub

-------------------


and replace it with this:

Code
-------------------

Sub Macro3()
'
' Macro3 to save on the e: drive
'
'
On Error GoTo HandleError
Dim fName As String
fName = InputBox("Enter the filename to be saved on E: drive", "Save file on E: drive", "defaultname.xls")

If Not IsNull(fName) Then ' check to see they wrote something as the name

If Right(fName, 4) <> ".xls" Then 'add appropriate extension if missing
fName = fName & ".xls"
End If

fName = "E:\" & fName ' add the drive extension (or directory information)

ActiveWorkbook.SaveAs filename:=fName

MsgBox "File: " & fName & " successfully written to disk"

'else they hit cancel or tried enterring an empty string so do nothing
End If

Exit Sub
HandleError: ' such as the drive was unavailable

MsgBox ("An error was encountered")

End Sub

-------------------


A few notes: It will automatically add the ".xls" extension if it i
missing. If the name is already in use the user will be asked t
overwrite the file if they hit cancel it will just say an erro
occured. If the drive is not available/accessible it will give a
error message and do nothing. Change the "defaultname.xls" to templat
name you might want to have so the user can add a number to it... s
instead of "defaultname.xls" you might want Report-05-17-04 o
something....

HT
 
Top