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.

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?

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

What level is your macro security set at?

Do other workbooks/macros work?

Regards

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.

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 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 don't think you answered the questions in post 5.

Regards

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.

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

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