Forum Discussion
sf49ers19238597
Oct 14, 2024Iron Contributor
I need help with an error in vba code
I need help with an error in the VBA code. I have a user form that has scores. I also have a bye week worksheet that has weeks when teams bye. The bye weeks worksheet looks at user form and t...
- Oct 15, 2024
I fix my error problem.
Private Sub cmbweeks_Change() Dim Ws As Worksheet Dim WsBye As Worksheet Dim R As Long Dim C As Long Dim I As Long Dim TEAMS As String Set Ws = Worksheets("INPUT_SCORES") Set WsBye = Worksheets("BYE WEEKS") NFL_SCORES_FORM.txtbills.SetFocus With Me 'SENDING TO FORM 2 to 33 ROWS 'AFC EAST TEAMS .txtbills.Value = Ws.Cells(2, Me.cmbweeks.Value + 1) .txtdolphins.Value = Ws.Cells(3, Me.cmbweeks.Value + 1) .txtjets.Value = Ws.Cells(4, Me.cmbweeks.Value + 1) .txtpatriots.Value = Ws.Cells(5, Me.cmbweeks.Value + 1) 'AFC NORTH TEAMS .txtbengals.Value = Ws.Cells(6, Me.cmbweeks.Value + 1) .txtbrowns.Value = Ws.Cells(7, Me.cmbweeks.Value + 1) .txtravens.Value = Ws.Cells(8, Me.cmbweeks.Value + 1) .txtsteelers.Value = Ws.Cells(9, Me.cmbweeks.Value + 1) 'AFC SOUTH TEAMS .txtcolts.Value = Ws.Cells(10, Me.cmbweeks.Value + 1) .txtjaguars.Value = Ws.Cells(11, Me.cmbweeks.Value + 1) .txttexans.Value = Ws.Cells(12, Me.cmbweeks.Value + 1) .txttitans.Value = Ws.Cells(13, Me.cmbweeks.Value + 1) 'AFC WEST TEAMS .txtbroncos.Value = Ws.Cells(14, Me.cmbweeks.Value + 1) .txtchargers.Value = Ws.Cells(15, Me.cmbweeks.Value + 1) .txtchiefs.Value = Ws.Cells(16, Me.cmbweeks.Value + 1) .txtraiders.Value = Ws.Cells(17, Me.cmbweeks.Value + 1) 'NFC EAST TEAMS .txtcommanders.Value = Ws.Cells(18, Me.cmbweeks.Value + 1) .txtcowboys.Value = Ws.Cells(19, Me.cmbweeks.Value + 1) .txteagles.Value = Ws.Cells(20, Me.cmbweeks.Value + 1) .txtgiants.Value = Ws.Cells(21, Me.cmbweeks.Value + 1) 'NFC NORTH TEAMS .txtbears.Value = Ws.Cells(22, Me.cmbweeks.Value + 1) .txtlions.Value = Ws.Cells(23, Me.cmbweeks.Value + 1) .txtpackers.Value = Ws.Cells(24, Me.cmbweeks.Value + 1) .txtvikings.Value = Ws.Cells(25, Me.cmbweeks.Value + 1) 'NFC SOUTH TEAMS .txtbuccaneers.Value = Ws.Cells(26, Me.cmbweeks.Value + 1) .txtfalcons.Value = Ws.Cells(27, Me.cmbweeks.Value + 1) .txtpanthers.Value = Ws.Cells(28, Me.cmbweeks.Value + 1) .txtsaints.Value = Ws.Cells(29, Me.cmbweeks.Value + 1) 'NFC WEST TEAMS .txt49ers.Value = Ws.Cells(30, Me.cmbweeks.Value + 1) .txtcardinals.Value = Ws.Cells(31, Me.cmbweeks.Value + 1) .txtrams.Value = Ws.Cells(32, Me.cmbweeks.Value + 1) .txtseahawks.Value = Ws.Cells(33, Me.cmbweeks.Value + 1) 'BYE's for the selected week R = Int((Val(.cmbweeks.Value) - 1) / 6) * 8 + 1 C = (Int((Val(.cmbweeks.Value) - 1) * 3) + 2) Mod 18 For I = 1 To 6 R = R + 1 If Not IsEmpty(WsBye.Cells(R, C)) Or Len(WsBye.Cells(R, C)) > 0 Then TEAMS = Trim(WsBye.Cells(R, C).Value) ' Print the value of TEAMS for debugging Debug.Print "TEAMS: " & TEAMS ' Print the control name for debugging Debug.Print "Control Name: " & "txt" & TEAMS On Error Resume Next .Controls("txt" & TEAMS).Value = "BYE" If Err.Number <> 0 Then Debug.Print "Error: " & Err.Description Err.Clear End If On Error GoTo 0 End If Next I End With End Sub
HansVogelaar
Oct 15, 2024MVP
In that case I cannot help you
sf49ers19238597
Oct 15, 2024Iron Contributor
I fix my error problem.
Private Sub cmbweeks_Change()
Dim Ws As Worksheet
Dim WsBye As Worksheet
Dim R As Long
Dim C As Long
Dim I As Long
Dim TEAMS As String
Set Ws = Worksheets("INPUT_SCORES")
Set WsBye = Worksheets("BYE WEEKS")
NFL_SCORES_FORM.txtbills.SetFocus
With Me
'SENDING TO FORM 2 to 33 ROWS
'AFC EAST TEAMS
.txtbills.Value = Ws.Cells(2, Me.cmbweeks.Value + 1)
.txtdolphins.Value = Ws.Cells(3, Me.cmbweeks.Value + 1)
.txtjets.Value = Ws.Cells(4, Me.cmbweeks.Value + 1)
.txtpatriots.Value = Ws.Cells(5, Me.cmbweeks.Value + 1)
'AFC NORTH TEAMS
.txtbengals.Value = Ws.Cells(6, Me.cmbweeks.Value + 1)
.txtbrowns.Value = Ws.Cells(7, Me.cmbweeks.Value + 1)
.txtravens.Value = Ws.Cells(8, Me.cmbweeks.Value + 1)
.txtsteelers.Value = Ws.Cells(9, Me.cmbweeks.Value + 1)
'AFC SOUTH TEAMS
.txtcolts.Value = Ws.Cells(10, Me.cmbweeks.Value + 1)
.txtjaguars.Value = Ws.Cells(11, Me.cmbweeks.Value + 1)
.txttexans.Value = Ws.Cells(12, Me.cmbweeks.Value + 1)
.txttitans.Value = Ws.Cells(13, Me.cmbweeks.Value + 1)
'AFC WEST TEAMS
.txtbroncos.Value = Ws.Cells(14, Me.cmbweeks.Value + 1)
.txtchargers.Value = Ws.Cells(15, Me.cmbweeks.Value + 1)
.txtchiefs.Value = Ws.Cells(16, Me.cmbweeks.Value + 1)
.txtraiders.Value = Ws.Cells(17, Me.cmbweeks.Value + 1)
'NFC EAST TEAMS
.txtcommanders.Value = Ws.Cells(18, Me.cmbweeks.Value + 1)
.txtcowboys.Value = Ws.Cells(19, Me.cmbweeks.Value + 1)
.txteagles.Value = Ws.Cells(20, Me.cmbweeks.Value + 1)
.txtgiants.Value = Ws.Cells(21, Me.cmbweeks.Value + 1)
'NFC NORTH TEAMS
.txtbears.Value = Ws.Cells(22, Me.cmbweeks.Value + 1)
.txtlions.Value = Ws.Cells(23, Me.cmbweeks.Value + 1)
.txtpackers.Value = Ws.Cells(24, Me.cmbweeks.Value + 1)
.txtvikings.Value = Ws.Cells(25, Me.cmbweeks.Value + 1)
'NFC SOUTH TEAMS
.txtbuccaneers.Value = Ws.Cells(26, Me.cmbweeks.Value + 1)
.txtfalcons.Value = Ws.Cells(27, Me.cmbweeks.Value + 1)
.txtpanthers.Value = Ws.Cells(28, Me.cmbweeks.Value + 1)
.txtsaints.Value = Ws.Cells(29, Me.cmbweeks.Value + 1)
'NFC WEST TEAMS
.txt49ers.Value = Ws.Cells(30, Me.cmbweeks.Value + 1)
.txtcardinals.Value = Ws.Cells(31, Me.cmbweeks.Value + 1)
.txtrams.Value = Ws.Cells(32, Me.cmbweeks.Value + 1)
.txtseahawks.Value = Ws.Cells(33, Me.cmbweeks.Value + 1)
'BYE's for the selected week
R = Int((Val(.cmbweeks.Value) - 1) / 6) * 8 + 1
C = (Int((Val(.cmbweeks.Value) - 1) * 3) + 2) Mod 18
For I = 1 To 6
R = R + 1
If Not IsEmpty(WsBye.Cells(R, C)) Or Len(WsBye.Cells(R, C)) > 0 Then
TEAMS = Trim(WsBye.Cells(R, C).Value)
' Print the value of TEAMS for debugging
Debug.Print "TEAMS: " & TEAMS
' Print the control name for debugging
Debug.Print "Control Name: " & "txt" & TEAMS
On Error Resume Next
.Controls("txt" & TEAMS).Value = "BYE"
If Err.Number <> 0 Then
Debug.Print "Error: " & Err.Description
Err.Clear
End If
On Error GoTo 0
End If
Next I
End With
End Sub