PDA

View Full Version : [SOLVED] VBA Calculate Event



Joinersfolly
04-09-2016, 07:42 AM
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
04-09-2016, 10:13 AM
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
04-09-2016, 10:35 AM
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
04-09-2016, 11:50 AM
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
04-09-2016, 04:10 PM
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
04-09-2016, 04:49 PM
It might be overkill since the macro runs every time that WS is activated, instead of only when an appropriate change is made