Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Worksheet Change event
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default 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


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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




Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 318
Default 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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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








Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 109
Default 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










Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 27,285
Default 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












Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Worksheet Change Event Tony S.[_2_] Excel Discussion (Misc queries) 11 February 18th 09 01:04 AM
Worksheet change Event ranswert Excel Worksheet Functions 1 January 17th 08 11:17 PM
worksheet change event? Steph[_3_] Excel Programming 5 March 28th 05 06:39 PM
Change Cell from Validated List Not Firing Worksheet Change Event [email protected] Excel Programming 3 October 4th 04 03:00 AM
Worksheet Change Event Eva Shanley[_2_] Excel Programming 1 September 17th 04 11:07 PM


All times are GMT +1. The time now is 04:46 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement