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
dbfromnewjerseyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice 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)
    Else
         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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.