Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Change Event code

This seems easy, but select case and if statements have not worked for me.

Range of cells THESE = D48, I48, N48

Range of cells THOSE = D49, I49, N49

If any cell in THESE change then clear all THOSE.

If any cell in THOSE change then clear all THESE.

Howard
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Change Event code

This seems easy, but select case and if statements have not worked
for me.

Range of cells THESE = D48, I48, N48

Range of cells THOSE = D49, I49, N49

If any cell in THESE change then clear all THOSE.

If any cell in THOSE change then clear all THESE.

Howard


Perhaps you're struggling with the effect of not disabling
'EnableEvents' while your code is running?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Change Event code



Perhaps you're struggling with the effect of not disabling
'EnableEvents' while your code is running?

--
Garry


I used EnableEvents = False/True but no go with what I tried.

I dumped my failures, I will recreate them and post them.

Howard
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Change Event code


I used EnableEvents = False/True but no go with what I tried.

I dumped my failures, I will recreate them and post them.

Howard



Private Sub Worksheet_Change(ByVal Target As Range)

Dim PctCells As Range, AmtCells As Range
Set PctCells = Range("D48,I48,N48")
Set AmtCells = Range("D49,I49,N49")

Application.EnableEvents = False
If Not Application.Intersect(PctCells, Range(Target.Address)) Is Nothing _
Or Application.Intersect(AmtCells, Range(Target.Address)) Is Nothing Then Exit Sub

If Target = PctCells Then AmtCells.ClearContents
If Target = AmtCells Then PctCells.ClearContents
End If

' Select Case Target.Range

' Case Is = PctCells
' AmtCells.ClearContents

' Case Is = AmtCells
' PctCells.ClearContents

' Case Else

' End Select

'End If
Application.EnableEvents = True

End Sub
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Change Event code

Howard, you write such hi-maintenance code for this...

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ErrExit
Select Case Target.Address
Case "$D$48", "$I$48", "$N$48"
Range("$D$49,$I$49,$N$49").ClearContents

Case "$D$49", "$I$49", "$N$49"
Range("D48,I48,N48").ClearContents
End Select

ErrExit:
Application.EnableEvents = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion




  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Change Event code

On Monday, January 26, 2015 at 4:11:56 PM UTC-8, GS wrote:
Howard, you write such hi-maintenance code for this...

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ErrExit
Select Case Target.Address
Case "$D$48", "$I$48", "$N$48"
Range("$D$49,$I$49,$N$49").ClearContents

Case "$D$49", "$I$49", "$N$49"
Range("D48,I48,N48").ClearContents
End Select

ErrExit:
Application.EnableEvents = True
End Sub

--
Garry


If my ignorance frustrates you then ignore my posts.

Thanks for the code.

Howard
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,514
Default Change Event code

On Monday, January 26, 2015 at 4:11:56 PM UTC-8, GS wrote:
Howard, you write such hi-maintenance code for this...

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ErrExit
Select Case Target.Address
Case "$D$48", "$I$48", "$N$48"
Range("$D$49,$I$49,$N$49").ClearContents

Case "$D$49", "$I$49", "$N$49"
Range("D48,I48,N48").ClearContents
End Select

ErrExit:
Application.EnableEvents = True
End Sub

--
Garry


If my ignorance frustrates you then ignore my posts.

Thanks for the code.

Howard


Absolutely not the case, ..I was merely expressing an observation in
you that reminds me of myself! I apologize if I offended you as it was
not my intent. Because of my typing difficulties (due to having Lou
Gehrig's), I made a mission of developing code brevity so as not to
'spend' time/energy that could be better 'invested'!

I do see the value in your sample in terms of clarity while trying
stuff. I do the same until a project is complete, then I go through and
clean out all the 'under construction' debris I have scattered
everywhere.

FWIW, I don't consider your learning efforts as ignorance, and your
posts don't frustrate me. Your relentless tenacity for learning reminds
me so very much of myself! Please know I'll continue to help you
however I can. Though I definitely will not always be able to help
you.., I will definitely always want to be helpful!!!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

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
Run VBA code only worksheet change, but don't trigger worksheet_change event based on what the code does ker_01 Excel Programming 6 October 3rd 08 09:45 PM
Event Change code karen Excel Programming 2 September 24th 08 04:39 PM
Event code to continuously monitor change Ybor Tony Excel Programming 3 October 20th 06 05:09 PM
Need code for simple change event Jake[_8_] Excel Programming 2 May 14th 04 11:29 PM


All times are GMT +1. The time now is 09:08 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"