After using a copy and paste link command, can it run a macr

[sam99car]

Hello,

My question is: After using the copy command on one spreadsheet, and using the paste link feature to paste to another worksheet in the same workbook, can it automatically run a macro?

 

[Jim North]

You could set up a Worksheet_Change event on the receiving worksheet to initiate the macro.

 

[sam99car]

Hello,

OK-I've found some info using this site regarding "Worksheet_Change" and this is my code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim rng As Range

'Set rng = Range("f4:f154")
If Target = [F4] Then
Exit Sub
Else
MsgBox "Hello"
End If

End Sub

How do I make it check a range?

 

[Jim North]

Such as:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Targets As Range
Set Targets = Range("G20")
If Targets.Address = "$G$20" Then
If Range("G20").Value < 0 Then
Exit Sub
Else
MsgBox "You must enter a negative" & Chr(10) & " value for this
tranfer !"
Range("G20").Activate
End If
End If
End Sub


If it's a column... Target.Column = 1 (for A)

 

[sam99car]

Hello Jim,

Sorry I've been trying to get this thing to work but I just can't do it.

Basically, I have formulas in c4,d4,e4 which affect f4. If there are any changes to f4 to f154, call a macro.

The code below kind of works but if I make changes in g4 or anywhere on the wrksht, the macro is called.

Please ignore the remarks stmts as I was experimenting.

AARRGH!! Any other suggestions.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

'Set rng = Range("f4:f154")

'If Union(Target, rng).Address = rng.Address Then
'If Union(Target, [F4:F154]).Address = [F4:F154].Address Then
'If Intersect(Target, Range("F4:F154")) Is Nothing Then

If Target = [f4] Then
Exit Sub
Else
Call Update_x_and_Media
End If

End Sub

 

[sam99car]

Never mind-I've just figured it out...

-sam