Autorun macros on opening

[craiglittleperth]

HI guys,

I am trying to setup a autorun macro on a workbook I have. I am trying
to stop the rest of the office changing the name of this file, as it
wil l have a knock on effect to other report!


How do I set this up, I have tried numerous examples on this site, but
just can't get them working... I was looking also for the code that
will stop users using the SaveAs function on the master template I am
creating..


Your help is greatly appreciated.


Craigy

 

[Simon Lloyd]

Hi, paste this in to the "This Workbook" module

Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then Cancel = True
End Sub

paste this in to an ordinary module:

Sub Auto_open()
On Error Resume Next
If ThisWorkbook.Name <> "Test" Then
Application.Quit
End If
End Sub

If the workbook name is not EXACTLY like the wording "Test" then you
will not be able to open the workbook again, if you had the workbook
named "test" you still would not be able to open it, it MUST be exact!

Regards,
Simon

 

[craiglittleperth]

Hi Simon,

Thanks for this.

I don't quite understand the workings behind these, I am sorry, I am
only very new to this. Once I have a grasp of how these work, I can
getting them into my template.

What is "This Workbook" and "Ordinary Module"??

Thanks in advance

Thanks

C

 

[Simon Lloyd]

In Excel click on tools, macros, Visual Basic Editor, when it opens yo
will see the worksheet modules and the This Workbook module doulbl
click it to open it!, for an ordinary module right click on Thi
Workbook module choose Insert then choose Module.

Regards,
Simo