Recognizing cell change in a Sub Worksheet_Change procedure
Actually, I bet that your procedure is running nicely.
If you paste a single cell into A1, you'll see that the other stuff gets run.
But if you paste over a bunch of cells, then this line:
if target.address = "$A$1" then
with not be what you want. The Target.Address will be something like
$A$1:$C$99--multiple cells--so the stuff under the "then" portion of your code
doesn't run.
Since you're only using A1, you could modify it to something like:
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Me.Range("A1")
If Intersect(Target, myCell) Is Nothing Then
Exit Sub
Else
With Worksheets("Sheet2")
.Rows("1:35").EntireRow.Hidden = False
If myCell.Text = "Apples" Then
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True
ElseIf myCell.Text = "Pears" Then
.Rows("15:15").EntireRow.Hidden = True
.Rows("25:25").EntireRow.Hidden = True
ElseIf myCell.Text = "Oranges" Then
.Rows("30:30").EntireRow.Hidden = True
.Rows("35:35").EntireRow.Hidden = True
Else
.Rows("12:12").EntireRow.Hidden = True
.Rows("16:16").EntireRow.Hidden = True
End If
End With
End If
End Sub
Note that I added another "Option" statement. Then Pears, PEARS, PEarS, pears,
.... will all be treated the same.
======
One more thing...
If your code that does the copy turns off events:
application.enableevents = false
'code that copies|pastes
application.enableevents = true
Then this event won't fire. If you want to have the code run, then stay away
from disabling events -- or call this procedure -- or do the same kind of thing
in your copy|paste routine.
AMY Z. wrote:
Hi,
The procedure below works great if I manually enter a value in A1. I have
another macro that copies and pastes a certain row into row1. The procedure
below won't change if I run the copy and paste macro, even though A1 has
changed.
Is there a way around this so the code below recognizes a change by copy and
pasting?
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
With Worksheets("Sheet2")
.Rows("1:35").EntireRow.Hidden = False
If Target.Text = "Apples" Then
.Rows("10:10").EntireRow.Hidden = True
.Rows("20:20").EntireRow.Hidden = True
ElseIf Target.Text = "Pears" Then
.Rows("15:15").EntireRow.Hidden = True
.Rows("25:25").EntireRow.Hidden = True
ElseIf Target.Text = "Oranges" Then
.Rows("30:30").EntireRow.Hidden = True
.Rows("35:35").EntireRow.Hidden = True
Else
.Rows("12:12").EntireRow.Hidden = True
.Rows("16:16").EntireRow.Hidden = True
End If
End With
End If
End Sub
I hope I explained so you can understand.
Thank you your time in advance,
Amy
--
Dave Peterson
|