unsupoprted macro type in office 2007...i think

[stwigge]

I have just changed from 2003 to 2007.
I have a macro that is written within each sheet (see below)
from what i have read, it seems that macros written within each sheet (i right click on the worksheet, and paste the code into the 'view code' bit) are not supported with 2007.
therefore I don't know what to do.
Please could someone help?
(I have of course checked my macro security settings, and tried recording a simple macro to check that macros can run)
many thanks

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
End If
On Error GoTo ErrHandler:
If Not Application.Intersect(Me.Range("B3:B500"), Target) Is Nothing Then
If IsNumeric(Target.Value) = False Then
Application.EnableEvents = False
'Target.Value = StrConv(Target.Text, vbLowerCase)
Target.Value = StrConv(Target.Text, vbUpperCase)
'Target.Value = StrConv(Target.Text, vbProperCase)
Application.EnableEvents = True
End If
End If
ErrHandler:
Application.EnableEvents = True

If Target.Column <> 19 Then If Target.Column <> 26 Then If Target.Column <> 224 Then Exit Sub

ActiveSheet.Unprotect Password:="twig"

If IsEmpty(Target(1)) Then Exit Sub '-- also rejects if entire row cleared
If Target.Row = 1 Then Exit Sub

If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Date
Target.Offset(0, 1).NumberFormat = "dd/mm/yy"

ActiveSheet.Protect Password:="twig", DrawingObjects:=True, Contents:=True, Scenarios:= _
False, AllowSorting:=True, AllowFiltering:=True
End If
End Sub

 

[MARK858]

You can use sheet macros in 2007 exactly the same as 2003. What exactly is the issue you are getting ?

 

[wigi]

from what i have read, it seems that macros written within each sheet (i right click on the worksheet, and paste the code into the 'view code' bit) are not supported with 2007.

This should work, exactly the same as with Excel 2003.
Open up your file in Excel 2007 and it should work without modifications.

 

[stwigge]

the issue im getting is that the macro does not work.
it is supposed to add a date when i enter in a preceding selected column, but it doesnt do anything. (no error message)

so in summary, it works in 2003.
when i open in 2007 it doesnt. i have also tried saving it as an xlsm file (the one that says 'with macros') but again it didnt work.
very strange

 

[MARK858]

Use a file sharing site to upload a copy of the 2003 file and then post a link in the thread or pm me and I will have a look over the weekend. Remember to set it up for sharing and most importantly change any confidential information

 

[RoryA]

What are your macro security settings and did you enable macros when you opened the file?

 

[wigi]

Also, any chance that events are disabled (check that Application.EnableEvents = True)?