Is it possible to disable all macros when saving as a copy?

hon

Board Regular
Joined
Oct 10, 2005
Messages
73
I am trying to discourage users to make copy of the master file by disabling all the macros. Is it possible?
 

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
Hello hon:

How does disabling macros discourage users from copiing the Master Workbook ?? :confused:
 

hon

Board Regular
Joined
Oct 10, 2005
Messages
73
If they can't use the macros, they can't do certain things with the report hence, forcing them to use the master copy rather duplicating it.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
You cannot do either. Security (such as it is) is handled at the local (computer) level, and if someone wants to make a copy of the workbook they will, in Explorer or other ways that are easily available totally outside of the workbook author's control.
 

hon

Board Regular
Joined
Oct 10, 2005
Messages
73
I forgot to mention that the master file is on the intranet so they can't copy master file.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
Unfortunately, that won't matter either, I wish it did but really, there are no guarantees against piracy, intranet or not, and especially not possible to control the macro security settings of one computer from another. Rather you know than have a false sense of security about security, which in the Excel platform is quite flimsy.
 

hon

Board Regular
Joined
Oct 10, 2005
Messages
73
I take it that there is no way to save a copy without the macros.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
Yes there is but that is not what you asked. You asked if it was possible to disable (not the same as deleting) the macros, and to have the file not be copy-able, based on this you wrote:

"I am trying to discourage users to make copy of the master file by disabling all the macros."

"...the master file is on the intranet so they can't copy master file"



If you want to save a copy of the workbook without any code, then just remove all the code and make a copy. If this is a one-time thing then that's it. If it's a common thing, then to make a macro for it, create a new workbook, and insert a macro in that new workbook that points to the subject workbook, opens it, and (because it is on your intranet so you have full VBE access presumably) run this code I think from Chip Pearson:


Sub CopyWithoutCode
Dim VBComp, VBComps
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
ActiveWorkbook.SaveAs "Your\File\Path\YourFileName.xls"
End Sub


Requires established ref to MS Extensibility on the VBE.
 

hon

Board Regular
Joined
Oct 10, 2005
Messages
73
Sorry for the confusion. I should have mentioned removing macros as an alternative option.

Now back to the code, I need a macro to remove all other macros at the time of fileSaveAs (that would mean removing the macro itself too). Is it possible?
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,197
Please re-read what I posted. I wrote:

"create a new workbook, and insert a macro in that new workbook that points to the subject workbook"

That means, you don't need to delete the macro that is being run because you placed it into a new workbook for the purpose of deleting all code out of a subject workbook and saving that subject workbook as a new file name.
 

Forum statistics

Threads
1,078,373
Messages
5,339,814
Members
399,333
Latest member
Grace Ford

Some videos you may like

This Week's Hot Topics

Top