PDA

View Full Version : Combining "Force users to enable macros in a workbook" and "Disable Cut, Copy, Paste"



shinyshoes
07-01-2013, 02:21 PM
Hi all

Complete newbie here in terms of VBA, intermediate in terms of Excel overall. I found the site as I was looking for a way of disabling cut, copy and paste functionality, as well as forcing users to enable Macros. Essentially, I want to provide a number of users with a massive volume of data, but I don't want them being able to copy it.

Ken Puls provided two excellent articles which work perfectly separately as code. Firstly, "Force users to enable macros in a workbook", and then secondly "Disable Cut, Copy, Paste" (I apologise, my post count won't allow me to post links)

However, when I try to use the two Macros together I get the error message "Ambiguous name detected: Workbook_BeforeClose". I gather this error message appears when the same subroutines appear twice or more in the same module (and hence if I was to resolve this error, other recurring subroutines in the module would cause similar issues). My understanding therefore is that the subroutines themselves need to be combined into one. Has anyone come across a macro which combines the above, or alternatively could provide me with some help in doing so? I'm working in Excel 2007, if that makes any difference.

The code for both Macros as written by Ken is as per the attachment.

Thanks in advance.

SamT
07-01-2013, 08:55 PM
Verddy interesting

http://www.vbaexpress.com/forum/showthread.php?t=46704

shinyshoes
07-02-2013, 01:19 AM
Many thanks Sam. So if I understand the other thread correctly, subs cannot be combined and therefore it would appear my request isn't possible. Are you aware of any other method which gives access to spreadsheet/workbook data but protects the integrity and value of that data?

SamT
07-02-2013, 07:04 AM
Is this a homework assignment?

Two very similar post from two people raise one's suspicions.

We have a policy to only help students find there own way to a solution, to not write their code.

What are the parts of a Sub declaration? How are Subs ran/called?

"A rose by any other name would smell just as sweet."

An "Ambiguous Name" can be caused by using a Name that is identical to an existing key word, but Ken doesn't make that mistake.

shinyshoes
07-02-2013, 07:29 AM
Sam, thanks for the response again, and I can understand the suspicion. To confirm, I am a student (MBA), but my studies have absolutely nothing to do with this question which relates to a private piece of research I've been carrying out the past few weeks. This is essentially related to finding a way to make a substantial excel file I've compiled (roughly 20,000 rows and 80 columns) accessible to researchers without them being able cut/copy/paste and edit. The other post, I can assure you, has nothing to do with me, and I'd hazard that can relatively easily be confirmed by checking the respective IP addresses if you have the power to do so.

As such, there's nothing I can do to answer your questions such as "what are the parts of a sub declaration", because as I mentioned, VBA is completely new to me. The reason I found Ken's posts/this forum is precisely because I used search terms in google for "disable cut, copy and paste" and similar, and I subsequently tried to follow Ken's instructions to the letter for each of the macros, which worked perfectly in isolation but not in combination owing to the errors I specified in the OP.

I hope this helps ensure your suspicions aren't warranted. If nothing can be done, then I greatly appreciate your time.

snb
07-02-2013, 09:44 AM
If you want to disable essentials of Excel why using Excel in the first place ?

shinyshoes
07-02-2013, 10:19 AM
snb, it's to ensure that what I create is used as master data for research purposes, and that version control remains to the fullest extent possible with me (because the file will grow and extend over time), but at the same time the data itself can be used for public research (i.e. outside the workplace).

All I can do is apologise for asking a question for which I think Ken indicates might have had a reasonable solution based on his responses in the KB.

SamT
07-02-2013, 11:54 AM
Okay.

There are something like 20,000,000 Excel VBA programmers in the world. That means there are 10,000,000 coding styles and 5,000,000 ways of accomplishing any task.

I know, but, it's really, really, just a tiny bit of hyperbole. :)

First, about Ken Puls. He was accomplishing two methods to use directly in any workbook. His style is similar to mine in that he uses Explicitly declared variables with meaningful names. His ways of accomplishing that is terser and more elegant than mine, as I always try to code so the newest noob can tell exactly what I'm doing and I usually break the code into many small subs each of which only does one thing.

snb's style is the tersest and most elegant I have seen since c programmers were contesting to see who could accomplish a task in the fewest terms. He hardly ever explicitly declares variables and uses the shortest names possible. However, if you learn to decipher his code you will be able to understand any code in any language, and you will truly begin to understand VBA for Excel.

All three of us will use different ways of accomplishing the same thing.

One more thing relevant to the above. You will have noticed that Ken's code started with "Option Explicit" on both pages. This causes VBA to require you to explicitly declare all variables. Never use "Option Explicit" with snb's code until you have declared all his variables. You can use (Office XP) VBA's Menu >> Tools >> Options >> Editor tab to make VBA put it at the top of all code modules for you.

There are 5 basic types of Code Modules:

Worksheet, which is the code pane you get when you Double Click a sheet name in the VBA Project Explorer Pane. This is a special module whose code only applies to its particular sheet They hold all Sheet Event subs
Workbook, seen when you D-click the ThisWorkbook in the PE, which is a special Standard module and can contain anything any Standard module can. Its code applies to every sheet in the workbook and is the only module that can contain Workbook Event subs.
Standard, which you insert by Right Clicking in the PE in the Workbook's area and are auto-named Module1, etc. These are accessible in the PR by expanding the Modules Folder.
Class, which are used to define User created Objects, are inserted like Standard Modules and reside in the Class Modules Folder. They are auto-named Class1, etc
UserForm, Inserted as above and found in the UserForms Folder of the PE. Very useful for data entry.



About Ken's Force users to enable macros in a workbook Code.


The Workbook Event subs, Workbook_BeforeClose, Workbook_BeforeSave, and Workbook_Open, must all be in the ThisWorkbook module. The other could be in any standard module, but they all apply specifically to the workbook and its sheets, so this module is a logical place for them.


The only purpose of all this code is to hide all the sheets except the "Macros" sheet unless the User closes the book and reopens it with Macros enabled.



About Ken's Disable Cut, Copy, Paste Code.



All these subs, except the last 4, (Workbook Event subs,) pertain to the Excel application and must be in a standard module other than the ThisWorkbook module.


The Workbook_Activate, Workbook_BeforeClose, Workbook_Deactivate, and
Workbook_Open event subs must be in the ThisWorkbook module. It is the duplication of the Open and BeforeSave event subs that is giving your "Ambiguous Name" error. You must move the Unique event subs to the ThisWorkbook module and move the contents of the duplicates into their twins' contents before deleting the dupes.





Other Schema



When they open your workbook, immediately force them to Save As another name and let them Copy and Paste as desired
Keep you data sheet xlVeryHidden, place a command button on a cover sheet that lets the User Insert a Visible copy of the Data sheet and let the C&P as desired, but force them to either close the book without saving or Save As another name.
Create a subsidiary workbook that allows the User to Import any desired data from your Data Workbook, but otherwise cannot touch the master data book.
There are 4,999,996 other ways, too. ;)In Closing:


Please let me know the depth of instruction you want from me so I can tailor my responses to your needs.

snb
07-03-2013, 01:08 AM
You can provide copies of your masterfile very easily and won't be disturbed by their messing up those copies.
You can also provide workbooks linked to you materfile that will be synchronised regularly with your materfile. All changes by the user in his/her linked file will be overwritten after synchronisation.
Be very reluctant to change anything in the user's Excel interface: you seldom make friends doing that.
The things you asked for are not related to the workbook but to the application(Excel), so the impact is much broader that the goal you want to achieve. To put it mildly: it doesn't correspond to the principle of proportionality.