textbox and combobox controls on an Excel worksheet

I have an Excel worksheet with 3 controls on it.  They are as follows:


1. Combobox1, which references the range of cells A1 to A12 (by designating the ListFillRange in the control's properties window
as A1:A12.  Combobox1's LinkedCell is B1.  The values contained in A1:A12 are January through December; allowing the user to make a "month" selection from the combobox.


2. Combobox2, which references the range of cells A13 to A22 (by designating the ListFillRange in the control's properties window
as A13:A12.  Combobox2's LinkedCell is C1.  The values contained in A13:A21 are 2017 through 2025; allowing the user to make a
"year" selection from the combobox.


3. Textbox1, which is to be used to possibly display a message based upon the combined values in the comboboxes.
An error message ("You cannot select a future period") is to be displayed IN THE TEXTBOX when the user selects any period greater than or equal to the current month and year. In other words, the user is not allowed to select a future period, which for my purposes includes the current month since it has not ended yet.

If I was dealing with only one combobox, it would not be an issue. However, because there can only be one linked cell in the textbox's properties, I've been trying to come up with a solution.  I attemped concatinating the 2 comboboxes' linked cells, using the concatinated cell (cell F1) as the linked cell for the textbox, and then referencing portions of that cell's contents using MID but it's not working.

I think if you review my code below, it'll be clear what I'm trying to do.

Before getting to the code though, here are the cells that get referenced and a description of what they contain:

cell B1: The text value of the month selection from Combobox1 (i.e. either January, February, March, etc)

cell C1: The selected year from Combobox2 (i.e. either 2017, 2018, 2019, etc)

cell D1: The numeric value of the month selected from Combobox1 obtained by having the following formula in D1:
=MONTH(DATEVALUE(B1&" 1"))

cell E1: formula in E1 is =YEAR((NOW())) which is used to check against the year selected in Combobox2.

cell F1: the formula in F1 is =C1&D1 which is a concatination of the selected year and selected month (represented as a number). So, for example if the year selected is 2017 and the month selected is December, F1 would contain 201712.


This is the current code for the textbox; accessed by right clicking the textbox control and clicking on 'view code':

Private Sub TextBox1_Change()

'Check if the year selected is greater than the current year or if the current year has been selected but a future month
'(which includes the current month for my purposes) within the current year has been selected.  

If (Sheets("sheet1").Range(Mid("F1", 1, 4)).Value - Sheets("sheet1").Range("E1").Value > 0) Or _

   ((Sheets("sheet1").Range(Mid("F1", 1, 4)).Value - Sheets("sheet1").Range("E1").Value = 0) And _
   (Sheets("sheet1").Range(Mid("F1", 5, 2)).Value - Sheets("sheet1").Range("D1").Value >=0))

  TextBox1.Text = "You cannot select a future period."

  'highlight the textbox to make the error message stand out to the user.

  TextBox1.BackColor = RGB(255, 255, 0)

Else

  'No problems with combobox selections, so make make sure textbox is empty and do not highlight it.  

   TextBox1.Text = ""
   TextBox1.BackColor = RGB(255, 255, 255)

End If