+ Reply to Thread
Results 1 to 20 of 20

Force Macro to remove cut, copy and paste options

  1. #1
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Thumbs up Force Macro to remove cut, copy and paste options

    Hi

    Please help! I am new to this forum and a novice to Macro's/VBA. I have been using excel for years but have always managed to resolve issues using formula's etc but now i have had to step into the realms of macro's. I am slowly learning but urgently need help with the below problem which is bound to be a simple solution.

    I have compiled a workbook for users to administrate on a daily basis. (each worksheet is a different day of the month but the same template) There are a few hidden worksheets as the info is extracted from these to run reports.

    The issue/s i have is that administrators use cut, copy and paste to repeat data opposed to retyping, this messes formats, formulas and validation up. I have used a macro from the net to remove this option and it works fine. Problem is that if they do not enable macro's this will cease to be an option.

    I understand that you cannot remove the option of enabling macro's but you can force. Again used a macro from the web that if you don't enable it only shows a welcome page saying that macro's need to be enabled to use. (all others are hidden) if macro's are enabled the welcome page disappears and the worksheets 1-31 reappear. Again this worked fine.

    I tried putting both macro's into the same workbook but kept coming up with errors, each one i resolved led to another.

    So short of it i need a instructions/macro to force macros and remove cut copy and paste.

    Help would be really appreciated as am going around in ever decreasing circles.

    Thank you.
    Last edited by Tictac; 02-25-2010 at 01:39 PM.

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Force Macro to remove cut, copy and paste options

    I have posted code for this before ,try an advanced forum search
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    08-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    149

    Re: Force Macro to remove cut, copy and paste options

    What about making the spreadsheet protected, then having macros on open and close that disable/enable the protection? You could add a startup tab to the workbook which shows a message of "You must enable Macros to enter / edit data" and then have the workbook_close macro set that tab as the active sheet so you can ensure that it's always the first thing seen upon opening. You could even have add to the open macro so it it changes the active sheet to the one the user would normally go to first.

    EDIT:
    This is likely what royuk is referring to... it hides/unhides sheets as opposed to protecting them.
    http://www.excelforum.com/excel-prog...th-saveas.html
    Last edited by masteff; 02-18-2010 at 01:31 PM.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Force Macro to remove cut, copy and paste options

    This earlier post is what I meant

  5. #5
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    They are both similar to waht i have found previously but need instructions or the macro how to have both on same workbook. Each time i try and put both together i keep getting ongoing errors.

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Force Macro to remove cut, copy and paste options

    I don't see why you need a sheet for each day of the week. Try attaching a workbook

  7. #7
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    The excel file is in the form of a workbook. Each day is on a different worksheet within the same workbook. It is something that is used to log data on a daily basis and then the data is linked to create various reports. The 1st problem i have is that administrators tend to copy/cut and paste repeated data. I found a macro that i could use to prevent this and it worked fine. This then created the problem that macro's would need to be enabled for this to work. I then found the macro to force enabling that hid all sheets except a welcome/introduction page if macro's were not enabled, if they were enabled the administated sheets were revealed and introduction page became hidden (all sheets that should be hidden remained hidden) This on its own worked fine. When i tried combining both macro's it kept coming up with various errors. I followed advice to resolve but it just lead to another error.

    So ideally i need a complete macro that will force macro's to be enabled like described as above, once enabled will remove all copy, cut and paste options.

    I know it can be done as i have said have used the 2 seperate macro's i just can't figure out how to use together.

    Thank you in advance.

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Force Macro to remove cut, copy and paste options

    It's not too difficult to combine the codes,see the attached example
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    Royuk

    That appears to work great on my workbook, Thankyou. It does now create another issue it that when you enable macro's it also unhides the worksheets that should be hidden. I have a worksheet for each day of the month which does what it is supposed now but there are other worksheets within the workbook that are used to run reports. These need to remain hidden at all times unless workbook protection is unprotected (usually by me). Almost there though and i do appreciate this. As said earlier i am a novice to vba/macro's but it is something i want to develop. Time and understanding are my enemies at the moment.

    I would be glad of any pointers to where i can go to learn for someone that is a novice.

    I hope you can resolve the issue of the worksheets that should always remain hidden.

    Thank you

  10. #10
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Force Macro to remove cut, copy and paste options

    I can't do that without knowing what the worksheets are

  11. #11
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    Royuk

    Sorry. What do you need to know? I thought there was a way that the macro could be set up so that it does not reveal the sheets that have been set to hidden. This was the case on the macro i tried out but could not join up with the cut and paste macro.

  12. #12
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    All worksheet names are as follows:

    Introduction Sheet (sheet 1) (if enabled this is not seen, if not enabled this is the only sheet seen)
    and 31 sheets numbered 1-31 to represent days of month. (should only be seen if macro enabled)
    The following sheets should remain hidden at all times unless unprotected workbook and then unhidden by me.

    "Cost"
    "Employee Logs"
    "Monthly Figures"
    "Employee Figures"
    "Employee Times"
    "Report"

    I hope that is what you are after, if it is not let me know.

    Thank you
    Last edited by Tictac; 02-25-2010 at 01:35 PM.

  13. #13
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Force Macro to remove cut, copy and paste options

    Try this amended code
    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    Arggghh! I thought we had it.

    Came up with the following
    run_time error '1004'
    Unable to set the visible property of the sheets class
    I clicked on de-bug and the following was highlighted yellow.

    Sheets(Array("Admin", "scorecard", "Airline Figures", "Booked Status Figures", _
    "Pax Numbers By Time", "Pax Volume")).Visible = xlSheetVeryHidden
    This is driving me nuts now... and almost puts me off vba/macro's.

    Please help! I need to issue by the end of the week and there is still ots i need to do.

    Many Thanks

  15. #15
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: Force Macro to remove cut, copy and paste options

    Maybe this
    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    RoyUK

    Thank you again, amended macro and thought it was there as it did most things. Ran into another error on open
    oSht.Visible = xlSheetVeryHidden
    was highlighted yellow on debugger.

    on before close
    : oSht.Visible = xlSheetVeryHidden
    was highlighted yellow on debugger both saying
    run_time error '1004'
    Unable to set the visible property of the sheets class
    The introduction sheet still seems to remain once enbled macro and i don't know how to access the hidden sheets that are always hidden. The unhide sheets is greyed out even when i unprotect workbook.

  17. #17
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    Hi

    I have played around and started to think outside the box. What i have done is removed all the hidden sheets as links take the info straight from the administration sheets. This has now obviously downsized my file which makes sending a lot more efficient. It also removes the problem with trying to hide/ and unhide the always hidden sheets.

    I have used the original macro in the attchment that RoyUK supplied in post 8 of this thread, i even added some code that forces save when closing file which prevents users from saying no when closing file and messing the file up.

    This issue i have got now is that i need to protect the workbook but this prevents the macro working ( i am guessing because it protects the structure and the macro changes the structure - causing a conflict)

    Again i am guessing as still learning but would imagine a code on opening needs to be placed in macro to unprotect workbook prior the code of the original macro and let it do its job, then a code after to reprotect, then all in reverse at the end for closing.

    Please could you help with code and where it should be placed in the macro supplied on post 8.

    Thank you
    Last edited by Tictac; 02-24-2010 at 04:50 AM.

  18. #18
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Question Re: Force Macro to remove cut, copy and paste options

    Please help!... Someone.

  19. #19
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Force Macro to remove cut, copy and paste options

    Seperate request made at below link.

    http://www.excelforum.com/excel-prog...ro-to-run.html

  20. #20
    Registered User
    Join Date
    02-18-2010
    Location
    London, England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Smile Re: Force Macro to remove cut, copy and paste options

    Thank you RoyUK your elp was appreciated.

    SOLVED:

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1