Thanks for the reply Kenneth. I am not a VBA pro by any means, I'm still learning. Your comment reminded me of an important part I omitted from original posts code. I omitted parts to try and keep it clean and not take focus away from the problem area. The part that hides and unhides sheets is controlled by an intersect. If the users enter their initials in a cell within the range it enters the date in the column next to it, and also unhides the issue sheet that goes with it.
There is also another single cell that needs to run the macro every time the value changes. Cell B4 - the user enters the number of rows they need for data and the macro unhides only the rows needed.
The problem is every time anything is entered there is about a 3 second pause before the macro finishes and you are able to enter next cell value.
I don't need to run everything when any cell is changed but at the time I started it I didn't know any better. It worked fine until I added all the sheets to unhide.
Private Sub Worksheet_Change(ByVal Target As Range)
Set rng = ActiveCell
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("AD193:AD213")) Is Nothing Then
With Target(1, -1)
.Value = Now
End With
'this part hides and unhides rows on the sheet as needed by users for data entry. Rows 8-186
If Target.Address = "$B$4" Then
Toggle_Rows
End If
If Range("AB193") <> "" Then
Worksheets("Issue 1").Visible = xlSheetVisible
Else
Worksheets("Issue 1").Visible = xlSheetHidden
End If
If Range("AB194") <> "" Then
Worksheets("Issue 2").Visible = xlSheetVisible
Else
Worksheets("Issue 2").Visible = xlSheetHidden
End If
If Range("AB195") <> "" Then
Worksheets("Issue 3").Visible = xlSheetVisible
Else
Worksheets("Issue 3").Visible = xlSheetHidden
End If
If Range("AB196") <> "" Then
Worksheets("Issue 4").Visible = xlSheetVisible
Else
Worksheets("Issue 4").Visible = xlSheetHidden
End If
If Range("AB197") <> "" Then
Worksheets("Issue 5").Visible = xlSheetVisible
Else
Worksheets("Issue 5").Visible = xlSheetHidden
End If
If Range("AB198") <> "" Then
Worksheets("Issue 6").Visible = xlSheetVisible
Else
Worksheets("Issue 6").Visible = xlSheetHidden
End If
If Range("AB199") <> "" Then
Worksheets("Issue 7").Visible = xlSheetVisible
Else
Worksheets("Issue 7").Visible = xlSheetHidden
End If
If Range("AB200") <> "" Then
Worksheets("Issue 8").Visible = xlSheetVisible
Else
Worksheets("Issue 8").Visible = xlSheetHidden
End If
If Range("AB201") <> "" Then
Worksheets("Issue 9").Visible = xlSheetVisible
Else
Worksheets("Issue 9").Visible = xlSheetHidden
End If
If Range("AB202") <> "" Then
Worksheets("Issue 10").Visible = xlSheetVisible
Else
Worksheets("Issue 10").Visible = xlSheetHidden
End If
If Range("AB203") <> "" Then
Worksheets("Issue 11").Visible = xlSheetVisible
Else
Worksheets("Issue 11").Visible = xlSheetHidden
End If
If Range("AB204") <> "" Then
Worksheets("Issue 12").Visible = xlSheetVisible
Else
Worksheets("Issue 12").Visible = xlSheetHidden
End If
If Range("AB205") <> "" Then
Worksheets("Issue 13").Visible = xlSheetVisible
Else
Worksheets("Issue 13").Visible = xlSheetHidden
End If
If Range("AB206") <> "" Then
Worksheets("Issue 14").Visible = xlSheetVisible
Else
Worksheets("Issue 14").Visible = xlSheetHidden
End If
If Range("AB207") <> "" Then
Worksheets("Issue 15").Visible = xlSheetVisible
Else
Worksheets("Issue 15").Visible = xlSheetHidden
End If
If Range("AB208") <> "" Then
Worksheets("Issue 16").Visible = xlSheetVisible
Else
Worksheets("Issue 16").Visible = xlSheetHidden
End If
If Range("AB209") <> "" Then
Worksheets("Issue 17").Visible = xlSheetVisible
Else
Worksheets("Issue 17").Visible = xlSheetHidden
End If
If Range("AB210") <> "" Then
Worksheets("Issue 18").Visible = xlSheetVisible
Else
Worksheets("Issue 18").Visible = xlSheetHidden
End If
If Range("AB211") <> "" Then
Worksheets("Issue 19").Visible = xlSheetVisible
Else
Worksheets("Issue 19").Visible = xlSheetHidden
End If
If Range("AB212") <> "" Then
Worksheets("Issue 20").Visible = xlSheetVisible
Else
Worksheets("Issue 20").Visible = xlSheetHidden
End If
If Range("AB213") <> "" Then
Worksheets("Issue 21").Visible = xlSheetVisible
Else
Worksheets("Issue 21").Visible = xlSheetHidden
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Bookmarks