VBA Code no longer working

[jdbel]

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.

[davegugg]

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?

[jdbel]

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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

On Error GoTo ErrorHandler
Application.EnableEvents = False

If Target.Row = 4 Then
    Target.Resize(47, 1).Select
End If

CleanExit:
Application.EnableEvents = True
Exit Sub

ErrorHandler:
MsgBox Err & " - " & Err.Description
GoTo CleanExit

End Sub

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.

[TMS]

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

[TMS]

What level is your macro security set at?

Do other workbooks/macros work?

Regards

[davegugg]

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.

[jdbel]

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.

[TMS]

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

[jdbel]

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.

[davegugg]

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?

[jdbel]

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?

[TMS]

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

Regards

[jdbel]

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.

[Paul]

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.

Sub reEnable()
Application.EnableEvents = True
End Sub

[jdbel]

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.

Sub reEnable()
Application.EnableEvents = True
End Sub

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.

[TMS]

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

[TMS]

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