'Worksheet_Change or Worksheet_SelectionChange event not engaging
After some time passed using Sheet27 both events stopped working. The code for the events are on Sheet27. No other Sub is called. There are four events for Sheet27
The file is located on OneDrive and I'm using Office365 on Windows 10 latest build. I built this app for other users who may have different Excel versions (2010 to Latest) and I'm not sure if this is going to repeat in other versions. Any light shone on this issue would be greatly appreciated.
When it stopped working I had to exit Excel entirely and reopen the file. I tried closing and reopening the file but that didn't work.
I thought it could have been the graphics card because it's a little bit older but when exiting Excel and reopening file worked, it cancelled that notion.
Maybe something along the code is causing it to stop working that I cannot see due to lack of expertise.
Maybe to do with the Excel Application or the Excel Workbook itself and not the Worksheet_Change or Worksheet_SelectionChange event because it works flawlessly when the file newly opened.
Here is Sheet27 Code: I labeled the sections that stopped working
Option Explicit
Dim RowNum As Long
Private Sub Worksheet_Change(ByVal Target As Range) 'Stopped working after some use
Application.EnableEvents = False
'This section enables and disables rows 4 through 12 via cell D3 Value of 1-10, 10 being max
'Row 3 is always shown
Select Case Range("D3").Value
Case "": Range("4:12").EntireRow.Hidden = True 'If D3 is intentionally blank
Case 1: Range("4:12").EntireRow.Hidden = True
Case 2
Rows("4:4").EntireRow.Hidden = False
Rows("5:12").EntireRow.Hidden = True
Case 3
Rows("4:5").EntireRow.Hidden = False
Rows("6:12").EntireRow.Hidden = True
Case 4
Rows("4:6").EntireRow.Hidden = False
Rows("7:12").EntireRow.Hidden = True
Case 5
Rows("4:7").EntireRow.Hidden = False
Rows("8:12").EntireRow.Hidden = True
Case 6
Rows("4:8").EntireRow.Hidden = False
Rows("9:12").EntireRow.Hidden = True
Case 7
Rows("4:9").EntireRow.Hidden = False
Rows("10:12").EntireRow.Hidden = True
Case 8
Rows("4:10").EntireRow.Hidden = False
Rows("11:12").EntireRow.Hidden = True
Case 9
Rows("4:11").EntireRow.Hidden = False
Rows("12:12").EntireRow.Hidden = True
Case 10
Rows("4:12").EntireRow.Hidden = False
Case Is > 10: MsgBox "Maximum 10 employees. If you need more than 10, add more after posting these 10.", vbInformation, "Maximum 10 Rows"
End Select
Application.EnableEvents = True
'***************************************************************
'Monthly or weekly Employee
Application.EnableEvents = False
RowNum = Target.Row
If Not Intersect(Target, Range("F3:F12")) Is Nothing Then 'Employee Name Field. Dropdown list - 10 Rows - F3:F12
'Get last row in Sheet Posting to
Range("V3").Value = Worksheets(Range("B1").Value).Range("B9999").End(xlUp).Row + 1
If Range("M" & RowNum).Value = "12" Then '12=Monthly or 52=Weekly. M3 has Index/Match formula associated to Employee Name
Range("G" & RowNum).Value = "1" 'If M3=12 then Monthly paid employee value is 1 (multiplier for monthly wage on posting Month sheet)
Else
Range("G" & RowNum & ":L" & RowNum).Value = "" 'clear associated data in G3:L3 if M3=52 (weekly paid employee)
End If
End If
Application.EnableEvents = True
'***********************************************************************
'If Loan Balance is 0 or less show warning
Application.EnableEvents = False
Dim LoanDue As Variant
Dim EmpName As String
RowNum = Target.Row
EmpName = Range("F" & RowNum).Value
LoanDue = Range("P" & RowNum).Value
If Not Intersect(Target, Range("J3:J12")) Is Nothing Then
If LoanDue < 0 Then
Target.Value = ""
Target.Select
MsgBox EmpName & "'s Loan Balance is Zero." & vbNewLine & _
"Entered payment was cleared." & vbNewLine & _
"Please notify Admin on " & EmpName & "'s record to verify or make changes.", _
vbExclamation, "Loan Payment Error"
End If
End If
Application.EnableEvents = True
End Sub
Private Sub PayDateInfoOnLbl_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 'This section works all the time
'Show Note
ActiveSheet.Shapes.Range(Array("TxtBxPayDate")).Visible = msoTrue
End Sub
Private Sub PayDateInfoOffLbl_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 'This section works all the time
'Hide Note
ActiveSheet.Shapes.Range(Array("TxtBxPayDate")).Visible = msoFalse
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Stopped working after some use
Dim VacBal As String
Dim SickBal As String
Dim EmpName As String
RowNum = Target.Row
VacBal = Range("R" & RowNum).Value 'INDEX/MATCH Formula in cell to retrieve data from EmployeeInfo sheet
SickBal = Range("T" & RowNum).Value 'INDEX/MATCH Formula in cell to retrieve data from EmployeeInfo sheet
EmpName = Range("F" & RowNum).Value
If Not Intersect(Target, Range("K3:K12")) Is Nothing Then
If VacBal = "" Then Exit Sub
MsgBox EmpName & " has " & VacBal & " Vacation Day(s) remaining.", vbInformation, "Vacation Days Balance"
End If
If Not Intersect(Target, Range("L3:L12")) Is Nothing Then
If SickBal = "" Then Exit Sub
MsgBox EmpName & " has " & SickBal & " Sick Day(s) remaining.", vbInformation, "Sick Days Balance"
End If
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
