+ Reply to Thread
Results 1 to 17 of 17

VBA Code no longer working

  1. #1
    Registered User
    Join Date
    10-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    10

    VBA Code no longer working

    A few weeks ago, I had an issue which was resolved here. I was given a bit of code that worked perfectly for me. I had noticed when I tried to save my project, I got a message indicating that my project wasn't macro-enabled, or some such. Like a fool I just disregarded it and thought I'd sort that out some time later. Each time I opened my project, I copy/pasted the code in. I know - not really the best way to deal with it. Eventually, I tried to save my file as macro-enabled with "Save as". It didn't seem to help. I was busy with many other things at the time, and it wasn't a high priority for me, so I kinda just blew it off.

    Since then, I upgraded from Vista to Windows 7. Today, I loaded my old project, and (as expected) the code wasn't working. I saved it as macro-enabled, and still nothing. I opened the VBA page, and the code is there. But I can't get it to work. The code is unchanged from the original time I had applied it. I think the only change from when it worked is that the file is now saved as .xlsm (and that I use Win7). What do I have to do to have the code work again, and what did I do wrong to mess it up begin with? Should I or shouldn't I save my project as macro-enabled, and do I do that before or after I apply the code?

    Thanks for any help.

    PS: can anybody recommend a good resource to learn more about macros and VBA coding? A book or website? I can't seem to find much through Google.
    Last edited by jdbel; 11-16-2011 at 08:08 PM.

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: VBA Code no longer working

    What do you mean the code doesn't work? Does it produce an error? If so, what is it? What is supposed to trigger the code? Can you post the part of the code that doesn't work, or a sample workbook?
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,227

    Re: VBA Code no longer working

    Dave's just asked the questions I was about to ask, so I'll just settle for pointing you at the Excel Tips section in this forum as a starter with regard to books.

    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    10-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA Code no longer working

    Quote Originally Posted by davegugg View Post
    What do you mean the code doesn't work? Does it produce an error? If so, what is it? What is supposed to trigger the code? Can you post the part of the code that doesn't work, or a sample workbook?
    Basically, nothing happens. The code just allowed me to select and copy a specific range. Here is the code:

    Please Login or Register  to view this content.

    When it did work, when I selected any cell in row 4, it would also automatically select all the cells down to cell 50. When I select the proper cell now, it is selected as thought the code doesn't exist (just the single cell). I guess the workbook is not recognizing the code. As I had said, I have not altered the code or the workbook since it had worked last, except by saving it as .xlsm. So I don't understand why it did work a couple weeks ago, and it doesn't now. Unless it is either the format I saved it as (xlsm), or Win7.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,227

    Re: VBA Code no longer working

    What level is your macro security set at?

    Do other workbooks/macros work?

    Regards

  6. #6
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: VBA Code no longer working

    Can you post the workbook, or a slimmed down version of it?

    Did you put the code on the right sheet's code page? Each worksheet has it's own code page.

  7. #7
    Registered User
    Join Date
    10-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA Code no longer working

    I do have multiple sheets in the workbook, and I have tried entering the code on multiple sheets. I have no other macros or code in the project. I have attached my project. What I am trying to do is select an item (row 6), and all the data below it to row 38, and input that data to one of several places to be calculated and/or compared to other data.

    The first 6 sheets are the only ones I have been working specifically with at this point.
    Attached Files Attached Files

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,227

    Re: VBA Code no longer working

    You earlier said row 4 down to row 50. Now you say row 6 down to row 38.

    Could that be why the code's not working? Mixed messages??

    Regards

  9. #9
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: VBA Code no longer working

    I tried it on the Jewellery Data sheet, and it seemed to work fine. It copied everything from row 6 to 38 of the column I clicked, column E. What else are you expecting?

  10. #10
    Registered User
    Join Date
    10-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA Code no longer working

    Quote Originally Posted by TMShucks View Post
    You earlier said row 4 down to row 50. Now you say row 6 down to row 38.

    Could that be why the code's not working? Mixed messages??

    Regards

    I apologize. The code I originally posted here was the original code that I was given from here. I meant to explain that I did change it to row six, the number of target cells, and the change to Copy instead of Select. But it had worked back when I wasn't having the problem.

  11. #11
    Registered User
    Join Date
    10-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA Code no longer working

    Quote Originally Posted by davegugg View Post
    I tried it on the Jewellery Data sheet, and it seemed to work fine. It copied everything from row 6 to 38 of the column I clicked, column E. What else are you expecting?
    I just tried it on my computer, and it doesn't select the range for me. Just the single target cell (E6). It must be a setting on my system then? Also, I want to be able to do this on all of my sheets, are they working on other sheets for you as well as the Jewellery sheet?

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,227

    Re: VBA Code no longer working

    I don't think you answered the questions in post 5.

    Regards

  13. #13
    Registered User
    Join Date
    10-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA Code no longer working

    Quote Originally Posted by TMShucks View Post
    I don't think you answered the questions in post 5.

    Regards
    You're right. I don't know what the "Macro security" setting is that you are talking about :/ Where would I find that? There are no other macros from me to check, though.

  14. #14
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,881

    Re: VBA Code no longer working

    In Excel, click the Office button (top left corner) then click Excel Options. Select 'Trust Center' and then click 'Trust Center Settings...' to the right of that. Click 'Macro Settings' and make sure it's set to 'Disable all macros with notification'. Click OK twice to return to Excel.

    Whenever you open that XLSM file in the future, you should see a prompt below the toolbar (above the cells) notifying you that macros need to be enabled. If you disregard that notification, macros won't run for that Excel session.

    Your other option is to save the file in XLS (Excel 97-2003) format. You should get the same prompt, though. You can't save a workbook with macro (VBA) code as XLSX (the standard for Excel 2007/2010).

    Back in the Trust Center Settings, you can add Trusted Locations either on your PC or on the network, which will stop Excel from notifying you about macros and simply enable them when you open files stored in those locations.

    If that's all well and good, perhaps you ran a macro that disabled your events. To fix that, open a blank workbook and press ALT+F11 to open the VB Editor window. Paste the following code into that window and run it. Close that workbook without saving, and then try to open your faulty workbook and run the macro again.
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    10-24-2011
    Location
    Las Vegas, NV
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: VBA Code no longer working

    Quote Originally Posted by Paul View Post
    In Excel, click the Office button (top left corner) then click Excel Options. Select 'Trust Center' and then click 'Trust Center Settings...' to the right of that. Click 'Macro Settings' and make sure it's set to 'Disable all macros with notification'. Click OK twice to return to Excel.

    Whenever you open that XLSM file in the future, you should see a prompt below the toolbar (above the cells) notifying you that macros need to be enabled. If you disregard that notification, macros won't run for that Excel session.

    Your other option is to save the file in XLS (Excel 97-2003) format. You should get the same prompt, though. You can't save a workbook with macro (VBA) code as XLSX (the standard for Excel 2007/2010).

    Back in the Trust Center Settings, you can add Trusted Locations either on your PC or on the network, which will stop Excel from notifying you about macros and simply enable them when you open files stored in those locations.

    If that's all well and good, perhaps you ran a macro that disabled your events. To fix that, open a blank workbook and press ALT+F11 to open the VB Editor window. Paste the following code into that window and run it. Close that workbook without saving, and then try to open your faulty workbook and run the macro again.
    Please Login or Register  to view this content.
    That was it! Thank you, I'd have never found that on my own. Thank you and everybody else that helped me with this silly noob issue.

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,227

    Re: VBA Code no longer working

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save

  17. #17
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,227

    Re: VBA Code no longer working

    PS: for future reference, please don't quote whole posts as it clutters up the thread.

    Glad we got you to a solution!

    Kudos to Paul for coming at the kill

+ 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