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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Hello hon:

How does disabling macros discourage users from copiing the Master Workbook ?? :confused:
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
I forgot to mention that the master file is on the intranet so they can't copy master file.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top