ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Worksheet Change event (https://www.excelbanter.com/excel-programming/330007-worksheet-change-event.html)

Gareth[_3_]
Worksheet Change event
 
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
Worksheet Change event
 
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

--
Regards,
Tom Ogilvy

"Gareth" wrote in message
...
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

Alok
Worksheet Change event
 
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.count1 then exit sub

Alok Joshi

"Gareth" wrote:

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
Worksheet Change event
 
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

--
Regrds,
Tom Ogilvy


"Gareth" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Gareth" wrote in message
...
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

Gareth[_3_]
Worksheet Change event
 
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" wrote in message
...
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

--
Regrds,
Tom Ogilvy


"Gareth" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Gareth" wrote in message
...
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
Worksheet Change event
 
Not unless you have additional code in the change event.

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

--
Regards,
Tom Ogilvy

"Gareth" wrote in message
...
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" wrote in message
...
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

--
Regrds,
Tom Ogilvy


"Gareth" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Gareth" wrote in message
...
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


All times are GMT +1. The time now is 09:15 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com