Macro stopped working after Office upgrade

[Snowgirl]

Hi Everyone!

I had Microsoft Office 2003 and had a macro in an Excel workbook that worked beautifully. There were some columns that I needed to have it so that if someone double clicked, a check mark would appear. All was well until our company just upgraded to Office version 2007--now it does not work. Would anyone have any suggestions as to how I can repair this macro? Or new code that would enable a check mark to appear if someone double clicks in a cell.

Thanks so much for any help you can give me!


Here is the code:


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Change this address to the area where you want the click to produce a checkmark
Const sCheckAddress As String = "y3:y1500,z3:z1500,aa3:aa1500,ab3:ab1500,ac3:ac1500"
Dim rngIntersect As Range

On Error Resume Next
Set rngIntersect = Intersect(Me.Range(sCheckAddress), Target)
On Error GoTo 0

If Not (rngIntersect Is Nothing) Then
Target.Font.Name = "Marlett"
Target.Value = "a"
End If


End Sub

 

[sykes]

There's probably nothing wrong with your code.

Sounds like the 2007 security - "Trust Center" settings. They're more "dumb" than in earlier versions.
Unless the user specifies within their settings, to "Enable all macros..." then macros will either be automatically disabled altogether, or disabled with notification. The notification appears in a white bar (which most people don't even notice) at the top of the excel page - beneath the formula bar. From memory, it says something like " ...some content has been disabled blah blah."

To access the trust center, and change the user's settings, click the "Office" button, "Excel Options", down the left hand side "Trust center", right hand side "Trust Center Settings" and change the settings in there.
You'll also see many other settings which can be changed - even whole areas on your network can be deemed to be trusted etc etc.
NB - If you .."enable all macros..." then you need to be sure that you know and trust the source of any workbooks you (or your users) open - otherwise, teach them how to accept content containing macros, on an individual basis.
If you have an IT department, they should be able to advise you on this.

 

[Snowgirl]

Thank you sykes!!

That was it! Wow..that is not obvious! I wonder why they felt they had to change it to this super-hidden way!

Thanks again!!

 

[sykes]

Pleasure - glad to be able to help. Thanks for the feedback - it helps other people with similar issues, who are searching the site.