I have an application where a worksheet named 'weightage table' is used to
control weightages of items on 7 other worksheets. The user is supposed to be
able to change weightage on this worksheet to zero or back above zero, and
the row with the items in the respective worksheets should hide or unhide
accordingly. This feature works only if after changing the weightage the user
either presses enter of clicks on another cell, but will not work if the user
uses the arrow keys or the tab key to move away from the cell. This causes
problems as another feature of the application allows the removal of items
from all the worksheets, but runs into error if item is hidden in one of the
worksheets.
Below is my code, a little messy as I did get parts of it from other places
and tweaked till I thought it worked. Thanks in advance
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ModelSheet As String
Dim R As Long

'Application.EnableEvents = True
Application.ScreenUpdating = False
Select Case ActiveCell.Offset(-1, 0).Column
Case 2
ModelSheet = Sheet10.Name
Case 3
ModelSheet = Sheet3.Name
Case 4
ModelSheet = Sheet5.Name
Case 5
ModelSheet = Sheet6.Name
Case 6
ModelSheet = Sheet7.Name
Case 7
ModelSheet = Sheet8.Name
Case 8
ModelSheet = Sheet9.Name
End Select
' Hidding or unhidding row where cell value is 0
R = ActiveCell.Offset(2, 0).Row
If ActiveCell.Offset(-1, 0).Value = "0" Then
Worksheets(ModelSheet).Rows(R).Hidden = True
ElseIf ActiveCell.Offset(-1, 0).Value "0" Then
Worksheets(ModelSheet).Rows(R).Hidden = False
End If
Application.ScreenUpdating = True
End Sub