| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Erasing contents and leaving the formula | New Users to Excel | |||
| VBA Code to identify contents of a cell | Excel Programming | |||
| Run code based on cell contents | Excel Programming | |||
| How to validate in code the contents of a cell? | Excel Programming | |||
| run code after cell contents change | Excel Programming | |||
Linear Mode
