need a vba code worksheet that go into a form

[TLS49]

have a NFL schedule program was made. I have worksheet that is call BYE WEEK. I have Userform that have comobox with weeks 1-17 and list all 32 NFL Teams names side to it 32 textbox. when I change weeks 1-17. It should match what are in BYE WEEKS worksheet. How get this working?

Press button that said form Input Scores Into Worksheet to open the form. when you press send score into input_score worksheet will be visible

Copy data from worksheet into form

DOWNLOAD NFL SCHEDULE FILE


Thanks You

TLS49

 

[TLS49]

No that picture is wrong wrorksheet.


DOWNLOAD FILE

I add multiple pictures and file on download fil. The pictures are number and text on picture in order .

 

[TLS49]

The pictures in your post is wrong worksheet

DOWNLOAD FILE HERE

I have multiple images is to large to on the post

a NFL schedule file

BYE TEAM worksheet

 

[hernantorres23]

Hi, in a copy of your workbook replace your codes in the form NFL_SCORES_FORM with the code below

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

Don't forget to create this textbox and name it txtCurWeek