Worksheet Change event

[Gareth]

I have a problem with some sheet code, I think I know what is causing the
problem but I cannot figure a solution. This is the code that causes the
error:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$D$7" And Target.Value = "option 1" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "H"
ElseIf Target.Address = "$D$7" And Target.Value = "option 2" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "M"
ElseIf Target.Address = "$D$7" And Target.Value = "option 3" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "L"
ElseIf Target.Address = "$D$7" And IsEmpty(Target.Value) Then
Range("A8:A11").EntireRow.Hidden = True
Range("D8").ClearContents
End If
End Sub

D7 is really D7:G7 but the option 1, option 2 and option 3 parts work
(chosen from data validation list) but the hide and clearcontents don't. I
think that the If Target.Count > 1 part is to blame and unfortunately the
cells need to be merged. Is there any way around this.

Thanks in advance.

Gareth

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.MergeArea.Address = "$D$7:$G$7"
if Range("D7").Value = "option 1" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "H"
ElseIf Range("D7").Value = "option 2" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "M"
ElseIf Range("D7").Value = "option 3" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "L"
ElseIf Range("D7").Value = "" Then
Range("A8:A11").EntireRow.Hidden = True
Range("D8").ClearContents
End If
End If
End Sub

Perhaps

 

[Guest]

As far as I can tell if D7:G7 are merged and there is a change in D7 the
Target.count will always be more than 1 and hence your code will always exit
from the sub.

Just comment out the line.
if Target.count>1 then exit sub

Alok Joshi

 

[Gareth]

Tom

When I delete contents of D7 I get:

run-time error '1004':
Application-defined or object-defined error

with the following line in yellow:

If Target.MergeArea.Address = "$D$7:$G$7" Then

Any ideas?

Gareth

 

[Tom Ogilvy]

typo - left out the THEN


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.MergeArea.Address = "$D$7:$G$7" Then
If Range("D7").Value = "option 1" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "H"
ElseIf Range("D7").Value = "option 2" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "M"
ElseIf Range("D7").Value = "option 3" Then
Range("A8:A11").EntireRow.Hidden = False
Range("D8").Value = "L"
ElseIf Range("D7").Value = "" Then
Range("A8:A11").EntireRow.Hidden = True
Range("D8").ClearContents
End If
End If
End Sub

 

[Gareth]

sorry Tom, but I think I may have misled you...

the sheet contains much more single cell change code so If Target.Count > 1
Then Exit Sub must be included (mustn't it?)

any other possible solutions?

Gareth

 

[Tom Ogilvy]

Not unless you have additional code in the change event.

Unless the cell changed is D7, the change event wouldn't do anything.