Code stopped working no reason

[chef]

I have the simple code below and on various workbooks that have worked for years.
Just a list of staff and when I click it places name in A3 and I calculate various interations from there.

For some reason this has stopped working and I'm at a loss as to why. I havent changed anything on the code and everything looks in order.
Automatic calculation is on.
Have similar type of code on code on other workbooks and they dont work now either. Finding really strange and at a loss.

The only thing I have read is that there is some additional security features from Microsoft in Jan but unsure of impact or how to resolve, if indeed this is now causing a problem

Can anyone suggest a solution please as the code is correct?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then
Range("A3").Value = Target.Value
End If
End Sub

regards
Ian

 

[James006]

Hi,

You can re-instate your Event macros with Application.EnableEvents = True

 

[chef]

Thank for for your quick response
I'm not great at VBA and got this code from here years ago and unsure where to put this code and if I have to change every workbook as I have many
Sorry if this is basic but just unsure where to place this.
I have tried this below on one of the workbooks but nothing happens?

Thank you for your help


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = True
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then
Range("A3").Value = Target.Value
End If
End Sub

 

[shinigamilight]

Put this here

 

[James006]

Your own very first sentence of your first message :

I have the simple code below and on various workbooks that have worked for years.

Sorry but this leads to assume that, quite obviously, you do know where all Event Macros do need to be stored ....

 

[chef]

Ok place in separate line on Worksheet selection change as below and not working
I'm really sorry if I am being basic. This has worked for years no problem


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("C5:C35")) Is Nothing Then
Range("A3").Value = Target.Value
End If
End Sub

 

[chef]

Your own very first sentence of your first message :

I have the simple code below and on various workbooks that have worked for years.

Sorry but this leads to assume that, quite obviously, you do know where all Event Macros do need to be stored ....

Actually, I just have this simple code that I use in various workbooks by viewing code, cut, paste and just change ranges
I'm not really familiar with VBA or event macros as per your assumption and just copy paste into workbooks from the help I received years ago.
Not sure why else I would ask for help if I knew how to resolve?

 

[James006]

A couple of remarks :

- You can only ONE single Event with the Name Worksheet_SelectionChange
- To make everything work again .... forget what I suggested ...

1) Save your workbook
2) Exit everything Close Excel
3) and Re-Open

 

[chef]

Thanks for all the help but still not working

I put code in new worksheet and message Security Risk - Microsoft blocked as source file untrusted

Ive read article about Microsoft blocking macros and I can change by going to windows explorer / properties / general and uncheck box.

Uncheck box not there so I will pass to IT dept to see if they can resolve if this is a network issue where files sit.

Pain as these files were helpful and I can only hope IT dept can resolve any security issues.
Thanks again and also to share experience with others if situation crops up
regards

 

[James006]

Quite normally for macros to operate ... you do need to have Macros Enabled ...

How to Enable Macros in Microsoft Excel

Using Microsoft Excel macros, you can run a prerecorded set of steps to automate many of your repetitive tasks. You’ll have to turn on the feature before you can use it. We’ll show you how to activate macros on a per-spreadsheet basis as well as for all spreadsheets in Excel.

www.howtogeek.com