Private Sub cbsendscore_Click()
Dim ws As Worksheet
If Me.cmbweeks.ListIndex < 0 Then
MsgBox "You Need To Choose a Week.", vbCritical, "INPUT REQUIRED"
Exit Sub
End If
Run ("put_score_from_form")
'AUTOFIT COLUMN B-R
Sheets("INPUT_SCORES").Visible = True
Sheets("INPUT_SCORES").Select
Range("B2:R39").Select
Columns("B:R").EntireColumn.AutoFit
Range("A1").Select
End Sub
Private Sub DONE_Click()
ThisWorkbook.Worksheets("TEAMS_INFOS").Range("F1").Value = Me.cmbweeks.Value
ThisWorkbook.Save
Unload NFL_SCORES_FORM
End Sub
Private Sub cmbweeks_Change()
Dim ctrl As Control
For Each ctrl In NFL_SCORES_FORM.Controls
If (TypeOf ctrl Is MSForms.TextBox) Then
ctrl.Value = Null
End If
Next ctrl
Run ("get_score_from_sheet")
End Sub
Private Sub SAVE_EXIT_Click()
ThisWorkbook.Save
Application.Quit
End Sub
Private Sub UserForm_Initialize()
'you must create this textbox. See the picture
Me.txtCurWeek = ThisWorkbook.Worksheets("TEAMS_INFOS").Range("F1")
'Me.cmbweeks.Value = ThisWorkbook.Worksheets("TEAMS_INFOS").Range("F1").Value
End Sub
'-----------------------[SIZE=4]Then add one Standard module and put the code below[/SIZE]------------------------------------------
Sub get_score_from_sheet()
Dim ws As Worksheet
Dim sWeek As Integer
Dim checker As String
Dim teams As Range
Dim ctrl As Control
Set ws = Worksheets("INPUT_SCORES")
Set teams = ws.Range("A2:A33")
With Application
sWeek = NFL_SCORES_FORM.cmbweeks.Column(0) + 1
For Each ctrl In NFL_SCORES_FORM.Controls
If (TypeOf ctrl Is MSForms.TextBox) Then
'Debug.Print ctrl.Name & " " & ctrl.Value
'MsgBox Cells(1 + CInt(.Match(Mid(ctrl.Name, 4, 25), teams, 0)), sWeek).Address
checker = ws.Cells(1 + CInt(.Match(Mid(ctrl.Name, 4, 25), teams, 0)), sWeek)
If checker = "BYE" Then
ctrl.Value = ws.Cells(1 + CInt(.Match(Mid(ctrl.Name, 4, 25), teams, 0)), sWeek)
End If
End If
Next ctrl
End With
Set teams = Nothing
Set ws = Nothing
End Sub
Sub put_score_from_form()
Dim ws As Worksheet
Dim sWeek As Integer
Dim checker As String
Dim teams As Range
Dim ctrl As Control
Set ws = Worksheets("INPUT_SCORES")
Set teams = ws.Range("A2:A33")
With Application
sWeek = NFL_SCORES_FORM.cmbweeks.Column(0) + 1
For Each ctrl In NFL_SCORES_FORM.Controls
If (TypeOf ctrl Is MSForms.TextBox) Then
'Debug.Print ctrl.Name & " " & ctrl.Value
'MsgBox Cells(1 + CInt(.Match(Mid(ctrl.Name, 4, 25), teams, 0)), sWeek).Address
ws.Cells(1 + CInt(.Match(Mid(ctrl.Name, 4, 25), teams, 0)), sWeek) = ctrl.Value
End If
Next ctrl
End With
Set teams = Nothing
Set ws = Nothing
End Sub