Macros - How can I update the active worksheet only?

[josephss]

I am using excel 2010.

I have a macro-based employee leave system that works by couting the number of days shaded with a certain colour and thus calculating leave days taken, remaining and entitled... I have a single workbook with multiple sheets for different employees.

However, when I update by using ctrl alt f9, it updates all the other worksheets (ignoring their shading) with the values of the active worksheet! So if I update Peter, who has taken 14 days so far, it will update Liam's sheet too, with 14 days, ignoring Liam's actual shaded days...

How can I update each sheet individually, without compromising the other sheets?

Excel is not responding to Shift - F9.

 

[Richard Schollar]

So how does the macro itself work? Presumably the macro isn't initiated by Ctrl+Alt+F9? Unless is it triggered by a Calculate event? Can you post the code?

 

[josephss]

Hi Richard.

Voila:


Sub Macro1()

End Sub

Public Function countColourCompare(rowN As Integer, startCol As Integer, endCol As Integer, colorIndexRange As Range, weekdayRow As Integer, halfFridays As Boolean) As Double
countColourCompare = 0
For curCol = startCol To endCol
If Cells(rowN, curCol).Interior.colorIndex = colorIndexRange.Interior.colorIndex Then
countColourCompare = countColourCompare + 1
If Cells(weekdayRow, curCol) = "F" And halfFridays Then countColourCompare = countColourCompare - 0.5
End If
Next curCol
'return countcolour
End Function

 

[Richard Schollar]

OK with a UDF, the Ctrl+Alt+F9 will force a recalc of all the UDFs in the workbook. The problem with the function as written is that it makes a call to Cells(). This is an unqualified reference (doesn't have the parent sheet reference preceding it) so it refers to whatever is the Activesheet at the time. So the Function may well end up referring to a different sheet from that on which it is enetered.

I think the easiest way to correct this is to re-write the code to reference the sheet on which the calling function is held:

Public Function countColourCompare(rowN As Integer, startCol As Integer, endCol As Integer, colorIndexRange As Range, weekdayRow As Integer, halfFridays As Boolean) As Double
Dim shtParent As Worksheet
Set shtParent = Application.Caller.Parent   'hold ref to parent sheet
countColourCompare = 0
For curCol = startCol To endCol
If shtParent.Cells(rowN, curCol).Interior.colorIndex = colorIndexRange.Interior.colorIndex Then
countColourCompare = countColourCompare + 1
If shtParent.Cells(weekdayRow, curCol) = "F" And halfFridays Then countColourCompare = countColourCompare - 0.5
End If
Next curCol
'return countcolour
End Function

Amendments are in red.