Tough questions
 

here are some problems I found recently and would like some answers

1) The help window in VBA doesn't close. This started recently but cannot
get it closed unless I log off. Don't know why

2) Can anything be done to stop Worksheet_Change from recusively being
called when an entire row gets added or deleted. I added code to return if
the Target.address is not in the form $3:$3 so my code only gets called one.
Any suggestions.

3) Is there any way that the Worksheet_Change macro would know whatt action
called it. Needed tto know if an entire row was being added or deleted.
Didn't know how to tell the diffference.


4) Changing background color of cell does not automatically update
worksheet. I generated the function below. When I changed color of
referenced cell the value return by the functtion did not change. Can
anything be done tto havve excel recognize a color change and tthen
automatically reclculate the worksheet.

Function GetColorValue(Target As Range)
'
' GetColrovalue Macro
' Macro recorded 2/28/2007 by Joel
'

'
GetColorValue = Target.Interior.ColorIndex

End Function

Tough questions
 

1) Have no idea - never heard of it as a problem

2) Yes, defensive programming is the solution. If you will perform actions
in an event that will fire the event, then you need to set enable events to
false before performing the action and turning them back on after or use an
IF statement to prevent code from firing except under certain conditions.

3) Target is the only indication provided by the change event.

4) No, changing color does not cause a recalculation or trigger any events.

--
Regards,
Tom Ogilvy




"Joel" wrote:

here are some problems I found recently and would like some answers

1) The help window in VBA doesn't close. This started recently but cannot
get it closed unless I log off. Don't know why

2) Can anything be done to stop Worksheet_Change from recusively being
called when an entire row gets added or deleted. I added code to return if
the Target.address is not in the form $3:$3 so my code only gets called one.
Any suggestions.

3) Is there any way that the Worksheet_Change macro would know whatt action
called it. Needed tto know if an entire row was being added or deleted.
Didn't know how to tell the diffference.


4) Changing background color of cell does not automatically update
worksheet. I generated the function below. When I changed color of
referenced cell the value return by the functtion did not change. Can
anything be done tto havve excel recognize a color change and tthen
automatically reclculate the worksheet.

Function GetColorValue(Target As Range)
'
' GetColrovalue Macro
' Macro recorded 2/28/2007 by Joel
'

'
GetColorValue = Target.Interior.ColorIndex

End Function