Worksheet Change Triggers Macro... Sometimes

[lemo]

very strange -
i lifted this code from ozgrid site (thank you, ozgrid, and your long term member PCI) -

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If (Target.Address = "$A$1") Then
Cells(2, 2) = Cells(2, 2) + 1
' Your Code
Application.EnableEvents = True
End If
End Sub

when i use the sample file that came with it, it works - i change the value in cell A1, and value in cell B2 increases by one. however, if i disturb it in a slightest way, it stops working - e.g. if i change the trigger cell to "$A$2", or move the procedure to my own spreadsheet, etc.
what is it? i have not a slightest clue..
using excel 2007 (although the sample file is .xls)
thanks,
l

 

[smiler44]

I've had a play with this and as I'm go expert could be about to humiliate myself. Before you paste the code into the code window try making sure that from the top the left drop down menu you have selected worksheet and the right drop down menu you have selected change. Then past the code in between the lines Private Sub Worksheet_Change(ByVal Target As Range) and End sub.

I have also found that you need to do this as the first thing you do. If trying it on a workbook I have already played with I could not get it to work. I also found deleteing the data in B2 also stopped the code from working and I had to restart Excel

smiler44

 

[Brianwarnock]

Smiler is right, this is a tempremental event.
I have had to resort to deleting the sub, after copying to say WORD closing Excel , reopening and starting again , retyping the code.

Brian

 

[lemo]

thanks guys.
well, i just spent two hours trying to make this work. i was hardly breathing, trying not to disturb ultra sensitive ms excel vba aura..
it worked once. then i changed the trigger cell from "$a$1" to "$w$5" and it went downhill from there. shutting down excel, new workbooks - nothing..
i don't even know if i should pursue this anymore, seems like resistance is futile.. but it's a generally useful feature, i'd like to have it in my arsenal.. is there a workaround? in this case, i just need to paste a column as values when "w5" changes, as in -

Application.EnableEvents = False
If (Target.Address = "$w$5") Then
Range("W9:W80").Select
Selection.Copy
Range("X9").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("W5").Select
Application.EnableEvents = True
End If

 

[Brianwarnock]

Try this instead. I've altered some code to remove selections, and I cannot see the purpose of the EnableEvents code.
But the most important change was to capitalize the W in the If Target.Address = "$W$5" Then code


Brian

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Application.EnableEvents = False

If Target.Address = "$W$5" Then
Range("W9:W80").Copy
Range("X9").PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Range("W5").Select

'Application.EnableEvents = True
Application.CutCopyMode = False

End Sub

 

[lemo]

thanks Brian.
your code put me in an infinite loop triggered by change in any cell.
however, i finally came up with the one that works, see below.

for posterity, this code is generally useful if you need to plot a column of data, but do not want any of the Excel'07 options for dealing with the blanks. in this case, your trigger is in W5, your data is in W5:W80, and you are plotting X5:X80 (the point being that the blanks in X5:X80 are 'true' blanks).

please let me know if i am missing something.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("w5")) Is Nothing Then
Exit Sub
Else
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Range("x9:x80").Clear
For i = 9 To 80
If Range("w" & i) <> "" Then
Range("w" & i).Select
Selection.Copy
Range("x" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next i
Range("w5").Select
End If
With Application
.ScreenUpdating = True
.Calculation = xlAutomatic
End With
End Sub

 

[Brianwarnock]

thanks Brian.
your code put me in an infinite loop triggered by change in any cell.
however, i finally came up with the one that works, see below.

Yes that code did for me too!!, it is the
Range("W5").Select
code that causes that as I had inadvertently used the Worksheet_SelectionChange I changed to the Worksheet_Change and all was well, Goodness knows how the original ended up in my post!!

Brian

 

[lemo]

so there are two ways so far -

If Target.Address = "$W$5" Then

and

If Intersect(Target, Range("w5")) Is Nothing Then
Exit Sub
Else

is there advantage of one vs the other? i don't see any..
l

 

[Brianwarnock]

As its name suggests the Intersect is for finding where 2 ranges cross, it might be heavier on resources than a simple If address = .. ,I don't know, logically it seems OTT in this instance.
Just my opinion, no insider knowledge.

Brian