I am creating my own sports coding application on Excel.
I am using 7 consecutive dropdowns lists within a single column to select a roster of players in each a match. I have created a macros that concatenates the players name and playing position into a single string, with each name and position delimited by a "-". The aim is to track roster changes as the game progresses. This macros has been set within a function called RosterList. I desire to trigger the RosterList macros via a change in the roster (form validation list). The most direct way to achieve this is by linking it to a worksheet change event, which will be set within each of the 7 dropdown lists.
When the function is triggered by the change event (a change in players from dropdown list form validation), the function goes haywire and repeats rapidly. I intend for just one cycle through the function to record the current roster post change. It however records the first change then the dropdown malfunctions and causes the function to fire over and over till VBA stops it by pushing a debug message.
Any ideas as to why the change event overreacts?
When the function is triggered by the change event (a change in players from dropdown list form validation), the function goes haywire and repeats rapidly. I intend for just one cycle through the function to record the current roster post change. It however records the first change then the dropdown malfunctions and causes the function to fire over and over till VBA stops it by pushing a debug message.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <= 8 And Target.Column = 16 Then
Target = RosterList ()
End If
End Sub
-------------------------------------------------------------------------------------
Function RosterList()
'
Dim y As Integer
Dim r As Integer
x = 200
'Navigates, copies Lineup Selection and returns to Lineup sheet
Sheets("231").Select
Range("O2:P8").Select
Selection.Copy
'Sheets("Lineup").Select
'In position to place next post
Sheets("Lineup").Cells(y, 2).End(xlUp).Offset(1, 0).Select
r = ActiveCell.Row
'In position to paste Lineup Selection
Cells(r, 11).Select
ActiveSheet.Paste
'In position to paste concatenated Lineup
Sheets("Lineup").Cells(y, 2).End(xlUp).Offset(1, 0).Select
Selection.FormulaR1C1 = _
"=R[6]C[9]&""-""&R[6]C[10]&""-""&R[5]C[9]&""-""&R[5]C[10]&""-""&R[4]C[9]&""-""&R[4]C[10]&""-""&R[3]C[9]&""-""&R[3]C[10]&""-""&R[2]C[9]&""-""&R[2]C[10]&""-""&R[1]C[9]&""-""&R[1]C[10]&""-""&RC[9]&""-""&RC[10]"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
enter image description hereenter image description here
'In position to select and delete Lineup Selection
Cells(r, 11).Select
ActiveCell.CurrentRegion.Select
Selection.ClearContents
'Returns to starting position
Sheets("Lineup").Cells(y, 2).End(xlUp).Offset(1, 0).Select
End Function
