exele
07-30-2014, 10:44 PM
Hi,
I'm new to VBA but I found yesterday a vba code suitable for my needs and it worked just fine. I tested a couple of times and now I notice that it is not working anymore. All I get is Compile error - Sub or Function not defined. I found some threads about this issue and they were mostly about problems occurring when you test the code for the first time. I do not have typos in the code as I copy&pasted it from the net, and I think I didn't change any excel settings.
Thank you in advance!
''Because this program is in the DDE worksheet, it runs
''each time a value changes
Private Sub Worksheet_Change(ByVal Target As Range)
''Do something only if the value changes in cell A1
If Target.Address = "$A$1" Then
''Look at the full list below the Target title
With ThisWorkbook.Names("ListDDE").RefersToRange.CurrentRegion
''Look at the cell at the bottom of the list
With.Offset(.Rows.Count, 0).Resize(1, 1)
''Enter the current time in the cell
.Value = Now
''Enter the new value to the right of the time
.Offset(0, 1).Value = Target.Value
End With
End With
End If
End Sub
I'm new to VBA but I found yesterday a vba code suitable for my needs and it worked just fine. I tested a couple of times and now I notice that it is not working anymore. All I get is Compile error - Sub or Function not defined. I found some threads about this issue and they were mostly about problems occurring when you test the code for the first time. I do not have typos in the code as I copy&pasted it from the net, and I think I didn't change any excel settings.
Thank you in advance!
''Because this program is in the DDE worksheet, it runs
''each time a value changes
Private Sub Worksheet_Change(ByVal Target As Range)
''Do something only if the value changes in cell A1
If Target.Address = "$A$1" Then
''Look at the full list below the Target title
With ThisWorkbook.Names("ListDDE").RefersToRange.CurrentRegion
''Look at the cell at the bottom of the list
With.Offset(.Rows.Count, 0).Resize(1, 1)
''Enter the current time in the cell
.Value = Now
''Enter the new value to the right of the time
.Offset(0, 1).Value = Target.Value
End With
End With
End If
End Sub