Code Erasing Cell Contents Inappropriately
 

I have the following code, which works correctly to hide/unhide rows as the
user inputs responses and also to erase data appropriately. However, when I
run another macro to check for certain things on the worksheet, somehow what
is in Cell C91:M91 gets erased even though it is not suppose to if C90:F90 =
"via a method not listed". It also hides row 91 even though 91 is suppose to
stay visible if if C90:F90 = "via a method not listed". There is nothing in
this other code that references anything in rows 90 or 91. After working all
day to try and figure out what I've done wrong, am asking for some help. I
feel sure it is in the way I've written the following code........thanks for
any assistance that can be provided.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("$C$89")) Is Nothing) Then
If Target.Value = "Yes" Then
ActiveSheet.Unprotect Password:="alaska"
Range("90:90,92:92").EntireRow.Hidden = False
Rows("91").Hidden = True
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("90:92").Hidden = True
Range("C90:F90,C92:D92,C91:M91").ClearContents
End If
End If
If Not Intersect(Target, Range("C90:F90")) Is Nothing Then
If Target(1).Value = "via a method not listed" Then
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = False
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = True
Range("C91:M91").ClearContents
End If
End If
End Sub

Code Erasing Cell Contents Inappropriately
 

If you code chagnes C89 and C89 < Yes, then you clear C90:F90 which will
fire the change macro again and do what you describe.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("$C$89")) Is Nothing) Then
If Target.Value = "Yes" Then
ActiveSheet.Unprotect Password:="alaska"
Range("90:90,92:92").EntireRow.Hidden = False
Rows("91").Hidden = True
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("90:92").Hidden = True
Application.EnableEvents = False
Range("C90:F90,C92:D92,C91:M91").ClearContents
Application.EnableEvents = True
End If
End If
If Not Intersect(Target, Range("C90:F90")) Is Nothing Then
If Target(1).Value = "via a method not listed" Then
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = False
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = True
Application.EnableEvents = False
Range("C91:M91").ClearContents
Application.EnableEvents = True
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
I have the following code, which works correctly to hide/unhide rows as
the
user inputs responses and also to erase data appropriately. However, when
I
run another macro to check for certain things on the worksheet, somehow
what
is in Cell C91:M91 gets erased even though it is not suppose to if C90:F90
=
"via a method not listed". It also hides row 91 even though 91 is suppose
to
stay visible if if C90:F90 = "via a method not listed". There is nothing
in
this other code that references anything in rows 90 or 91. After working
all
day to try and figure out what I've done wrong, am asking for some help.
I
feel sure it is in the way I've written the following code........thanks
for
any assistance that can be provided.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("$C$89")) Is Nothing) Then
If Target.Value = "Yes" Then
ActiveSheet.Unprotect Password:="alaska"
Range("90:90,92:92").EntireRow.Hidden = False
Rows("91").Hidden = True
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("90:92").Hidden = True
Range("C90:F90,C92:D92,C91:M91").ClearContents
End If
End If
If Not Intersect(Target, Range("C90:F90")) Is Nothing Then
If Target(1).Value = "via a method not listed" Then
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = False
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = True
Range("C91:M91").ClearContents
End If
End If
End Sub

Code Erasing Cell Contents Inappropriately
 

Thanks, Tom; your expertise is a life saver!

"Tom Ogilvy" wrote:

If you code chagnes C89 and C89 < Yes, then you clear C90:F90 which will
fire the change macro again and do what you describe.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("$C$89")) Is Nothing) Then
If Target.Value = "Yes" Then
ActiveSheet.Unprotect Password:="alaska"
Range("90:90,92:92").EntireRow.Hidden = False
Rows("91").Hidden = True
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("90:92").Hidden = True
Application.EnableEvents = False
Range("C90:F90,C92:D92,C91:M91").ClearContents
Application.EnableEvents = True
End If
End If
If Not Intersect(Target, Range("C90:F90")) Is Nothing Then
If Target(1).Value = "via a method not listed" Then
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = False
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = True
Application.EnableEvents = False
Range("C91:M91").ClearContents
Application.EnableEvents = True
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Paige" wrote in message
...
I have the following code, which works correctly to hide/unhide rows as
the
user inputs responses and also to erase data appropriately. However, when
I
run another macro to check for certain things on the worksheet, somehow
what
is in Cell C91:M91 gets erased even though it is not suppose to if C90:F90
=
"via a method not listed". It also hides row 91 even though 91 is suppose
to
stay visible if if C90:F90 = "via a method not listed". There is nothing
in
this other code that references anything in rows 90 or 91. After working
all
day to try and figure out what I've done wrong, am asking for some help.
I
feel sure it is in the way I've written the following code........thanks
for
any assistance that can be provided.


Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Target, Range("$C$89")) Is Nothing) Then
If Target.Value = "Yes" Then
ActiveSheet.Unprotect Password:="alaska"
Range("90:90,92:92").EntireRow.Hidden = False
Rows("91").Hidden = True
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("90:92").Hidden = True
Range("C90:F90,C92:D92,C91:M91").ClearContents
End If
End If
If Not Intersect(Target, Range("C90:F90")) Is Nothing Then
If Target(1).Value = "via a method not listed" Then
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = False
Else
ActiveSheet.Unprotect Password:="alaska"
Rows("91").Hidden = True
Range("C91:M91").ClearContents
End If
End If
End Sub