+ Reply to Thread
Results 1 to 16 of 16

Copied VB macro code has taken out functions in Excel

  1. #1
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Copied VB macro code has taken out functions in Excel

    Hi Guys,

    I'm a complete and total idiot and have broken the first rule and wondering if anyone can help. I'm a total amateur with VB and was looking for a way to protect the cut, copy and paste functions on a workbook; I found a VB code and cut and pasted it straight in. It worked and I was happy but now the cut, paste and copy functions are blocked in the whole of excel not just that workbook. I deleted the code and macros etc and reset using

    application.commandbars("cell").reset

    in the immediate pane, this has cured right click and keyboard functions but not the edit menu.

    Added to this the workbook says that the macro is still there (although I cant find it on VB editor or by unhiding) and it wont let me unprotect the workbook (which was originally protected but then unprotected when I pasted in the VB code).

    *Edit* Just noticed I can't drag and copy either. And also its still warning me about macros even though they are all deleted, I've tried the solutions listed elsewhere but to no avail.

    All signs point to a dodgy code but i've not the first clue what to do now. If anyone can help however little it would be much appreciated.

    Just to clarify, this is happening on my entire excel - not just this workbook. The macro warning is just this particular workbook but everything else is a problem on all.

    Yours incredibly stupidly,

    Ben
    Last edited by benniuk; 08-03-2011 at 01:49 PM. Reason: Request thread name change (typo)

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: I'm an idiot

    Hi benniuk and welcome to the forum. Please take a moment to read the forum rules (located in my signature) and amend your title as per Rule #1. Once you do that, someone here will be able to help.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: I'm an idiot

    Apologies - will do

  4. #4
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: I'm an idiot

    I guess i really am - how do I go about changing the title - new thread post?

  5. #5
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copied VB macro code has taken out functions in Excel

    Done, apologies

  6. #6
    Registered User
    Join Date
    07-27-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Copied VB macro code has taken out functions in Excel

    Hi Ben,

    Can you show me the original code used to disable cut & paste? You should be able to remove the macro warnings by deleting any modules added to the workbook.

    Cheers,
    Dom

  7. #7
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copied VB macro code has taken out functions in Excel

    Hi Dom,

    This was the original code I used;

    Option Explicit

    Sub EnableControl(Id As Integer, Enabled As Boolean)
    Dim CB As CommandBar
    Dim C As CommandBarControl
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(Id:=Id, recursive:=True)
    If Not C Is Nothing Then C.Enabled = Enabled
    Next
    End Sub

    Private Sub Workbook_Activate()
    EnableControl 21, False ' cut
    EnableControl 19, False ' copy
    EnableControl 22, False ' paste
    EnableControl 755, False ' pastespecial
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.OnKey "+{DEL}", ""
    Application.OnKey "+{INSERT}", ""
    Application.CellDragAndDrop = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EnableControl 21, True ' cut
    EnableControl 19, True ' copy
    EnableControl 22, True ' paste
    EnableControl 755, True ' pastespecial
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "+{DEL}"
    Application.OnKey "+{INSERT}"
    Application.CellDragAndDrop = True
    End Sub

    Private Sub Workbook_Deactivate()
    EnableControl 21, True ' cut
    EnableControl 19, True ' copy
    EnableControl 22, True ' paste
    EnableControl 755, True ' pastespecial
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "+{DEL}"
    Application.OnKey "+{INSERT}"
    Application.CellDragAndDrop = True
    End Sub

    Private Sub Workbook_Open()
    EnableControl 21, False ' cut
    EnableControl 19, False ' copy
    EnableControl 22, False ' paste
    EnableControl 755, False ' pastespecial
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.OnKey "+{DEL}", ""
    Application.OnKey "+{INSERT}", ""
    Application.CellDragAndDrop = False
    End Sub


    Its possible that it wasnt the code - I think the problem exists between keyboard and chair. No modules are showing on the VB editor and I'm unsure as to how this has effected all of excel, even new books. I can attach the workbook if that helps?

    Many Thanks

  8. #8
    Registered User
    Join Date
    07-27-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Copied VB macro code has taken out functions in Excel

    Hi Ben,

    The code is actually already there to reset it - it is triggered when the workbook is closed or deactivated. If you paste the following code into a module in the workbook and run it everything should be back to normal:
    Please Login or Register  to view this content.

    To do this, press Alt+F11, double click on Sheet1 on the right hand side, paste the code, place the cursor anywhere between "public sub reset" and "end sub", and click on the Run Macro button (green arrow).

    Regarding the macro warning, I'd imagine the code provided was pasted into the ThisWorkbook module? If so I'd double check that there's nothing in there.

    Cheers,
    Dom

  9. #9
    Registered User
    Join Date
    07-27-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Copied VB macro code has taken out functions in Excel

    Oops sorry - forgot to mention you need to paste this bit too:
    Please Login or Register  to view this content.

    Cheers,
    Dom

  10. #10
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copied VB macro code has taken out functions in Excel

    Hi Dom,

    Thanks for that however on inputting that into the VB editor I get an error message saying Compile Error: Ambiguous name detected : reset

    Do I need to have the original code still in place before I use the one that you have given, as I have already deleted all information that I had put in. As I said before I am a complete amateur (serves me right for trying to be clever!) so its possible i've goosed something. Is there anyway to reset all settings throughout excel or am I looking at a de-install re-install.

    Many Thanks again

  11. #11
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copied VB macro code has taken out functions in Excel

    Wait....

    You're a genius mate - thats worked for the cut copy paste etc thanks so much!! Still getting the Macro warning on this workbook though, not on others any ideas?

    Sorry to be monopolising your time

  12. #12
    Registered User
    Join Date
    07-27-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Copied VB macro code has taken out functions in Excel

    Hi no worries - although I'm also cooking a chilli so I might not be too quick to respond! Can you attach the workbook? It sounds like there's something hidden in there...

  13. #13
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copied VB macro code has taken out functions in Excel

    Wow, multitasking too. Squeeze a lime into your chilli for a nice fresh kick as well as both dried and fresh red chilli. (What I lack in excel knowledge I make up for in cooking). I've attached an exact copy of the workbook - had to copy it to take out sensitive info but the problem is still in there somewhere.

    Really appreciating your help with this
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-27-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Copied VB macro code has taken out functions in Excel

    Lime sounds like a good idea - I might give that a go!

    You have some code in the "ThisWorkbook" and the "Sheet3" modules. If you go back into the editor (Alt+F11) and double click where it says each of these you should see some code - delete all of that and save and you should be sorted.

  15. #15
    Registered User
    Join Date
    08-03-2011
    Location
    Southport, England
    MS-Off Ver
    Excel 2003
    Posts
    9

    Re: Copied VB macro code has taken out functions in Excel

    Jesus don't I feel like a prize idiot? Thats worked now mate thank you so much. I thought i'd deleted everything but I'd obvoiusly missed them.

    In return all I can offer is the secret to "Five Alarm Chilli": when cooking add fresh chilli at every stage i.e add onions and garlic to pan and chilli, once slightly softened add meat and chilli, once browned add tomatoes or passata more onions and chilli, once warmed through add beans and chilli, and simmer, before serving a add a squeeze of lime, some chopped coriander and chilli and stir through. It can be quite hot depending on the quantity and type of chillies you use but you'll get a nice rounded heat and you will taste five different chilli tastes as they are at different stages of cooking. And if you fancy another change use chickpeas instead of kidney beans. And thats my trick.

    Many thanks for your help, and enjoy your chilli

  16. #16
    Registered User
    Join Date
    07-27-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Copied VB macro code has taken out functions in Excel

    Haha thanks Ben. I've actually written that down to try it out.

+ 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