Stop people pasting in to a spreadsheet

Try this code

I'm not sure you'll have to experiment. If nee be swith it on before the macro, then off

Instead of blocking a feature the users prefer, you'd better ask why it is that they prefer to copy/paste form the intranet rather than using your program ?
How can you improve your program so that they will refrain from such 'unprofessional' behaviour like copying/pasting data from the intranet.
Did you consider retrieving intranet information into your program automatically ?
Feature blocking isn't the fastest way to enlarge your community of friends.

The workbook has a link to the same database that the intranet uses, for its address book. Well I say a link neither have a proper "Table link" they both use a data dump. The intranet is built in drupal and currently for security etc the data for the staff address book is imported via a csv file. Also in order to have a link direct to the database every machine would require the oracle software installing (we have nearly 6000 staff) so this isn't viable. So the solution is the spreadsheet has another spreadsheet with a direct table link placed on a server which runs on a schedule and then sends a new version of my spreadsheet with up to date information in, to the web team to upload on to the intranet.


Not a perfect solution I know, but for web security the web team have drupals file system completely locked out and it can only be accessed from the front end, meaning I can't simply swap out the old excel file with my new one.


Because of the number of staff and potential for more than 1 person with the same name, we are actively encouraging people to use the intranet for searching for people, this also helps with data clensing where someones details are wrong.

I suppose I could put a search function in as all the data is there, but part of the purpose behind the sheet is to train staff in to using employee numbers as they are far more accurate and much more useful from my point of view for reporting and data analysis.

I had a splash screen to tell people how to activate macros if they aren't activated, however from that link you sent me Roy, I have encorpoated their version of the code as it looked better.

I only want to have the "INPUT" and "OUTPUT" sheets displayed if the splash screen is hidden, I had this in my last code and I think I have edited it to do this now. But I keep getting a problem with the following error (the code I am using follows)

"Compile error in hidden module: ThisWorkbook. This error commonly occurs when code is compatible with the version, platform, or architecture of this application. Click "Help" for information on how to correct this error."

Please Login or Register  to view this content.

MODULE 5 has the related code in it for the copy paste function

Please Login or Register  to view this content.

can you attach the workbook

Please Login or Register  to view this content.

I don't think the macro's name covers the content....

Hi snb,

I got the error 1004, application defined or object- defined error

It didn't highlight a line for debugging

*** Correction it did:

Please Login or Register  to view this content.

The sheet names must be in lower case in the code.

Try this. I've made some alterations

There was no sheet was no welcome page, I thought that you had removed it for uploading. Ttry this amended code

When you go to close excel I am getting a debug after "Then", in:

Please Login or Register  to view this content.

from ...

Please Login or Register  to view this content.

Not sure if this is correct or not?

Please Login or Register  to view this content.

****

NO still getting the macro or welcome screen displaying with the input or output sheet?

Test this. II've incorporated Shg's code for forcing macros to be enabled

Hi I was just wondering, is there a way I can get it so that worksheets "DEPT", "MASTER" and "CALENDAR" are always hidden?

Hi Roy,

I have had a play with the worksheet you gave me and have tried tweaking it so that either the splash screen (macros) displays if macros aren't enabled or that both the "INPUT" and "OUTPUT" sheets will display if macros are enabled. I have also had a go at getting the other sheets to constantly remain hidden "MASTER", "DEPT" and "CALENDAR". I have uploaded my attempt. (Is it best to save as a macro enabled workbook?)

Please could you have a look at what I have done to edit the code to see if I am understanding what I have been told correctly.

Also it seems to work ok, so long as you set the sheet sequence to begin with. For example if I had initially saved it with the splash screen visible only, it would not work properly. It will only work if you initially save it with the "INPUT" and "OUTPUT" sheets visible.

I wonder if there is a way to make it organise its self or do you think it will be robust enough for all maner of staff to use, so long as I have the sheet sequence set before I uploacd it to the intranet?

Thanks for all your help I appreciate it!

PS I think the cut copy is working well, is there a way of testing, or in your opinion, is it robust enough to withstand basic users not following instructions properly? I also wonder if you know how to stop the 429 error occuring on some pcs, due to the cdo in some of my modules? (I think potentially I will have around 100 users) 2 so far have had a 429 issue.

Thanks again, hope you had a good christmas!

Andy