VBA Calculate Event

[Joinersfolly]

The following procedure works as expected, however I want the it happen automatically when the ("F3") cell in each sheet change,s NOTE F3 is a calculated cell.
I want the calculated figure in cell F3 to be sent to column 2 in the ATotalsSheet, where column one in the ATotalsSheet has the same value as the worksheet name from whence the value of F3 comes.

(I tried the opposite approach of sucking the info in to the "ATotalsSheet" using the activate event however the line Sheets("ATotalsSheet").Select causes an infinite loop to occur.)

Sub AttendanceHours()
'This procedure updates the main attendance sheet
'hours for each player when they have been changed in an
'individual players sheet
 Dim Answer As String

Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "ACoveringSheet" Then
If ws.Name <> "NewPlayer" Then
If ws.Name <> "ATotalsSheet" Then
ws.Select
Answer = Range("F3")

Dim DeNextRow As Long
Sheets("ATotalsSheet").Select
DeNextRow = Range("A65536").End(xlUp).Row

For I = 1 To DeNextRow
If Cells(I, 1).Value = ws.Name Then
Cells(I, 2) = Answer
End If
Next I
End If
End If
End If
Next ws
End Sub

This is my first post so please advise if I have submitted to the correct forum.
I have been away from VBA for a few years, is VBA and VBAX the same?


Thanks in advance.

[SamT]

Code goes in ThisWorkbook Code module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim i As Long
Dim IgnoreSheets As Variant
Dim Found As Range

'List all sheets, by name, to ignore
IgnoreSheets = Array("ACoveringSheet", "NewPlayer", "ATotalsSheet")

If Target.address <> "$F$3" Then ExitSub

For i = Lbound(IgnoreSheets) to UBound(IgnoreSheets)
   If Sh.Name = IgnoreSheets(i) Then Exit Sub
Next i

Set Found = Sheets("ATotalsSheet").Range("A:A").Find(Sh.Name)
If Not Found Is Nothing then
Found.Offset(0, 1) = Target
Else
msgBox "The Name "& sh.Name & " was not in column A on sheet ATotalsSheet."
End If
End Sub

When any change occurs on any sheet, the sub first checks if the change was in F3 on that sheet, if not then exit. IF the change was in F3, then, the sub checks to see if it occurred in one of the Ignore Sheets, if so, then exit. If it did occur in F3 on any other sheet, then the sub finds the sheet name in column A on ATotalsSheet and sets the neighboring cell to the Changed F3 value.

[Bob Phillips]

For Each ws In Worksheets
If ws.Name <> "ACoveringSheet" Then
If ws.Name <> "NewPlayer" Then
If ws.Name <> "ATotalsSheet" Then
ws.Select

That says that if the sheet name is "ACoveringSheet" then if the same sheet is named "NewPlayer" then if the same sheet is named "ATotalsSheet" then

How can the same sheet have three names?

No, it says is not ...

JoinersFolly, you need to use the workbook sheet calculate event and test for the sheet being not one of those 3, and the value of cell F3 not being what it was, which means saving it each time.

[Paul_Hossler]

Not tested, but I'd start with something like this is the 'THisworkbook' module

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim DeNextRow As Long, i As Long

    If Sh.Name = "ACoveringSheet" Then Exit Sub
    If Sh.Name = "NewPlayer" Then Exit Sub
    If Sh.Name = "ATotalsSheet" Then Exit Sub
    
    If Target.Address <> "$F$3" Then Exit Sub

    Application.EnableEvents = False

    With Sheets("ATotalsSheet")
        DeNextRow = .Range(.Rows.Count).End(xlUp).Row
     
        For i = 1 To DeNextRow
            If .Cells(i, 1).Value = Sh.Name Then
                .Cells(i, 2) = Target.Value
                Exit For       'assuming there's only one
            End If
        Next I
    End With

    Application.EnableEvents = False

End Sub

[Joinersfolly]

Thanks everyone for your help, however I managed to get the calculate event to work as follows:

Private Sub Worksheet_Activate
Application.EnableEvents= False
Call AttendanceHours
Application.EnableEvents= True

Line one is the activate event for the "ATotalsSheet" worksheet
Line two now turns off events so that when the worksheet ATotalsSheet is selected again within the Line three,"AttendanceHours" procedure, the infinite loop does not happen and my code runs as required!
Line four turns "Enable events" back on.

Thanks all for your help.
I shall certainly be examining the code you all submitted for shortness and elegance and proper coding.

The problem is therefore solved.
....which of course leads me to my next problem!

[Paul_Hossler]

It might be overkill since the macro runs every time that WS is activated, instead of only when an appropriate change is made