I have userform to input scores. I need help to get BYE from bye week worksheet

[TLS49]

download file here

NEED HELP PUT BYE ON USERFORM AUTOMATICALLY FROM BYE WEEK WORKSHEET.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	BYE WEEK 1	TEAMS		BYE WEEK 2	TEAMS		BYE WEEK 3	TEAMS		BYE WEEK 4	TEAMS		BYE WEEK 5	TEAMS		BYE WEEK 6	TEAMS
2	1			1			1			1			1			1	LIONS
3	2			2			2			2			2			3	RAIDERS
4	3			3			3			3			3			2	TEXANS
5	4			4			4			4			4			4	TITANS
6	5			5			5			5			5			5
7	6			6			6			6			6			6
8
9	BYE WEEK 7	TEAMS		BYE WEEK 8	TEAMS		BYE WEEK 9	TEAMS		BYE WEEK 10	TEAMS		BYE WEEK 11	TEAMS		BYE WEEK 12	TEAMS
10	1	BILLS		1	CHIEFS		1	BROWNS		1	RAVENS		1	JAGUARS		1
11	2	RAMS		2	CHARGERS		2	COWBOYS		2	BENGALS		2	DOLPHINS		2
12	3	VIKINGS		3			3	BRONCOS		3	PATRIOTS		3	SEAHAWKS		3
13	4	EAGLES		4			4	GIANTS		4	JETS		4	BUCCANEERS		4
14	5			5			16	STEELERS		5			5			5
15	6			6			6	49ERS		6			6			6
16
17	BYE WEEK 13	TEAMS		BYE WEEK 14	TEAMS		BYE WEEK 15	TEAMS		BYE WEEK 16	TEAMS		BYE WEEK 17	TEAMS		BYE WEEK 18	TEAMS
18	1	CARDINALS		1	FALCONS		1			1			1			1
19	2	PANTHERS		2	BEARS		2			2			2			2
20	3			3	PACKERS		3			3			3			3
21	4			4	COLTS		4			4			4			4
22	5			5	SANTS		5			5			5			5
23	6			6	COMMANDERS		6			6			6			6
BYE WEEKS

NEED HELP PUT BYE ON USERFORM AUTOMATICALLY FROM BYE WEEK WORKSHEET.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	TEAMS	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
2	BILLS	31	41	19	23	38	24		27	17	30	31	28	24	20	32	35	NO GAME	35
3	DOLPHINS	20	42	21	15	17	16	16	31	35	39		30	17	17	29	20	21	11
4	JETS	9	31	12	24	40	27	16	17	20		3	31	22	12	17	3	6	6
5	PATRIOTS	7	17	26	24	29	38	14	22	26		10	26	10	27	24	18	23	23
6	BENGALS	20	17	27	27	17	30	31	13	42		37	20	27	23	34	22	NO GAME	27
7	BROWNS	26	30	29	20	28	15	20	32		17	23	23	27	10	13	10	24	14
8	RAVENS	24	38	37	20	19	20	23	27	27		13	27	10	16	3	17	13	16
9	STEELERS	23	14	17	20	3	20	10	13		20	30	24	19	14	24	13	16	28
10	COLTS	20	0	20	17	12	34	10	16	3	25	16	17	19		36	3	10	31
11	JAGUARS	22	24	38	21	6	27	17	17	27	17		28	14	36	40	19	31	20
12	TEXANS	20	9	20	24	13	BYE	20	10	17	16	10	15	14	23	24	19	3	32
13	TITANS	20	7	24	24	21		19	17	17	17	27	16	10	22	14	14	13	16
14	BRONCOS	16	16	11	23	9	16	9	21		10	16	10	9	28	24	14	24	31
15	CHARGERS	24	24	10	34	30	19	23		20	16	27	25	20	23	17	20	31	28
16	CHIEFS	44	27	17	41	30	20	44		20	27	30	26	24	34	30	24	27	31
17	RAIDERS	19	23	22	32	29		38	0	20	20	22	40	27	16	30	10	34	13
18	COMMANDERS	28	27	8	10	17	12	23	17	17	32	23	19	20		12	20	10	26
19	COWBOYS	3	20	23	25	22	17	24	49		28	40	28	54	27	34	40	27	6
20	EAGLES	38	24	24	29	20	26		35	29	21	17	40	35	48	25	34	10	22
21	GIANTS	21	19	16	20	27	24	23	13		24	18	20	20	22	20	24	38	16
22	BEARS	19	10	23	12	22	7	33	29	32	30	24	10	19		20	13	10	13
23	LIONS	35	36	24	45	0		6	27	15	31	31	25	40	34	20	23	41	20
24	PACKERS	7	27	14	27	22	10	21	34	9	31	17	33	28		24	26	41	16
25	VIKINGS	23	7	28	28	29	24		34	20	33	3	33	27	23	39	27	17	29
26	BUCCANEERS	19	20	12	31	21	18	3	22	16	21		17	17	7	23	19	30	17
27	FALCONS	26	27	27	23	15	28	17	37	17	15	27	13	16		18	9	20	30
28	PANTHERS	24	16	22	16	15	10	21	34	21	25	3	23		30	16	37	24	10
29	SAINTS	27	10	14	25	39	26	34	24	13	10	27	0	16		21	17	20	7
30	49ERS	10	27	10	24	37	14	23	31		22	38	13	33	35	21	37	37	38
31	CARDINALS	21	16	12	26	17	9	42	26	21	27	10	24		13	15	16	19	13
32	RAMS	10	31	20	9	10	24		14	13	17	20	10	23	17	12	51	10	16
33	SEAHAWKS	17	7	23	48	32	19	37	27	31	16		34	27	24	13	10	23	19
INPUT SCORES

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:S33	Cell Value	="BYE"	text	NO

I have userform to input scores. I need help to get bye from bye week worksheet. I need help to get bye team from bye week worksheet put BYE on the nfll_scores_form by vba code. I put bye in week 6 have Texans BYE in textbox manually on userform show what I want for rest teams. Next year bye can be on different week. On the form how combo box for week 1 to 18.

Thanks you

TLS49

 

[Bosquedeguate]

Try this code for your cmbweek event code (see modification at the end of the sub).
It seemed to work when I ran a quick test
I enter "BYE" only in the displayed form. When you click "Send Scores" the BYES (as well as all other scores are written to the "INPUT SCORES" sheet.

Private Sub cmbweeks_Change()
 Dim Ws As Worksheet
 Dim WsBye As Worksheet
 Dim r As Long, c As Long
 Dim i As Long, j As Long
    
    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
            team = WsBye.Cells(r, c)
            .Controls("txt" & team).Value = "BYE"
          End If
        Next i
        
    End With
End Sub

 

[TLS49]

Try this code for your cmbweek event code (see modification at the end of the sub).
It seemed to work when I ran a quick test
I enter "BYE" only in the displayed form. When you click "Send Scores" the BYES (as well as all other scores are written to the "INPUT SCORES" sheet.

Private Sub cmbweeks_Change()
 Dim Ws As Worksheet
 Dim WsBye As Worksheet
 Dim r As Long, c As Long
 Dim i As Long, j As Long
 
    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
            team = WsBye.Cells(r, c)
       .Controls("txt" & team).Value = "BYE"
          End If
        Next i
     
    End With
End Sub

I put code in my file, and it gave me an error. I red highlight where the error is locate .Controls("txt" & team).Value = "BYE"





Thank You