We have an Excel 2007 (.xlsm) file which serves as an electronic logbook by several users.
When the file is open for some hours, some VBA code doesn't run anymore. Some facts:
-For instance a sub "selectionchange" which highlights the row selected doesn't run automatically anymore.
-If I try to step into the sub to check it step by step, the sub will not start.
-No error messages appear.
-Some users (different pc's) have the problem more often than other users.
-other VBA code (eg. macro's behind buttons) keep on working fine.
-macro's are enabled in the 'thrust center'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim icolor As Integer
Dim cell As Variant
Dim rij As Range
Dim userFile As String
Application.ScreenUpdating = False
'On error resume in case user selects a range of cells
On Error Resume Next
icolor = Target.Interior.ColorIndex
'Leave On Error ON for Row offset errors
If icolor < 0 Then
icolor = 36
Else
icolor = icolor + 1
End If
'Need this test in case Font color is the same
If icolor = Target.Font.ColorIndex Then icolor = icolor + 1
Cells.FormatConditions.Delete
'Horizontal color banding
With Range("b" & Target.Row, "f" & Target.Row) 'Rows(Target.Row)
.FormatConditions.Add Type:=2, Formula1:="TRUE"
.FormatConditions(1).Interior.ColorIndex = icolor
Range("k12") = Range("g" & Target.Row).Value
Range("l12") = Range("j" & Target.Row).Value
End With
With Range("l12")
.FormatConditions.Add Type:=2, Formula1:=Range("l12") > 0
.FormatConditions(1).Interior.ColorIndex = 40
End With
With Sheets("ingavescherm")
Set rij = .Range("i12:i40")
End With
For Each cell In rij
If cell.Offset(0, 1).Value > 0 Then
cell.Interior.ColorIndex = 40
End If
Next
End Sub
Does anyone have an idea what can be the cause of the problem?
Thanks in advance!
Bookmarks