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:

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)


  '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
Who is Participating?
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:
After playing a bit with this, it is much easyer using the dateserial function than parsing strings.
Plus you don't need to concatenate anything, VBA will handle that.
The month selected is B1.
The year selected is C1 + 2016.
As for the day, 1 seems appropriate.
Current year and month will also be handled by VBA.

So, cells A1 to A22, B1 and C1 will be all you need.
Private Sub TextBox1_Change()
    Dim dateSelected As Date
    Dim dateCurrent As Date
    Dim wb As ExcelWorkbook
    Dim ws As Excel.Worksheet

   Set wb = ThisWorkbook
   Set ws = wb.Worksheets("Sheet1")
    dateSelected = DateSerial(ws.Range("C1").Value + 2016, ws.Range("B1").Value, 1)
    dateCurrent = DateSerial(Year(now), Month(now), 1)
    If(dateSelected > dateCurrent) Then
        TextBox1.Text = "You cannot select a future period."
        TextBox1.BackColor = RGB(255, 255, 0)
         TextBox1.Text = ""
         TextBox1.BackColor = RGB(255, 255, 255)
    End If
    Set ws = Nothing
    Set wb = Nothing
End Sub

Open in new window

Additional notes:
Never use objects such as ActiveWorkbook, ActiveSheets, ActiveCell, Selection, the global Sheets, Cells, Range collections as these objects are user dependant and by nature chaotic.
As a developper, you don't want to use chaotic objects.
Prefer referencing explicitly and precisely the objects you need.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.