Worksheet Change event

  • Thread starter Thread starter Gareth
  • Start date Start date
G

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
 
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
 
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
 
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
 
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
 
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
 
Not unless you have additional code in the change event.

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