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?
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.

dbfromnewjerseyAuthor Commented:
Sorry but that solution is causing me  new problems.  No offense to you but the same type of thing has happened to me repeatedly on here with other questions. As soon as I start getting away from the original solution I was trying to employ, new problems come up. And what should have been a relatively easy task turns into a never-ending debugging problem.

That code now causes Excel to stop responding when I try to print the sheet.  Indicates an AppCrash. When I disable macros, it goes away but I need to have macros on for the code to work.

I need to come up with a different solution that won't cause Excel to crash, so I reposted.  Thank you.
0
Fabrice LambertFabrice LambertCommented:
upload your workbook. I suspect that something else is in the work.
there is no reason that a simple solution as the one I provided previously could freeze a workbook.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dbfromnewjerseyAuthor Commented:
OK. Time for another novel. Backtracking and starting all over from square one:

I have a situation where employees send me their Excel worksheets on a monthly basis.

They routinely make numerous errors on their worksheets; all of which I won't get into.

I've been trying to modify their sheets (meaning I would give them a "template" to use and resuse over
and over for submitting to us) to prevent as many errors as possible. One of the simplest modifications has been to lock all cells (for example, ones that have SUM formulas in them) other than cells that they're supposed to be entering data into (because they always make mistakes and enter data into the wrong columns, rows, etc and wind up knocking out formulas, etc).


One of the errors I've been trying to address and what this EE question is about is to prevent an employee from submitting a worksheet with an invalid date selected. By "date selected" I mean the employee specifies the month and year the data on the worksheet pertains to.  The way I originally attempted to handle this was to deliberately avoid using a macro-enabled worksheet because of all the problems that can occur with them and instead use a formula-driven sheet.   The following was my original attempt and worked fine except for the issue I'll
explain further down:

Worksheet has 2 comboboxes on it near the top of the sheet.

User selects a month from Combobox1 and a Year from Combobox2.
 
If the user selects a "future date", which for my purposes includes the current month (in other words, if the user selects
the current year, he/she can only select a month that has already ended), then the following message gets displayed across
a group of merged cells:

"Invalid date selected. Worksheet will not be accepted until error is corrected."

The formula for the above, which works fine is as follows:

=IF(OR((AND(SUM(XFD29-XFA1)<=0,SUM(XFC1-XFD28)>=0)),SUM(XFC1-XFD28)>0),"Invalid date selected. Worksheet will not be accepted until error is corrected.","")

In the above, cell XFD29, by way of the "=MONTH((NOW()))" formula contains the (numeric) current month. Cell XFA1 contains the numeric form of the month selected from Combobox1.  So, for example, the current month at the time of this writing is December. Therefore, the value in XFD29 is 12.   Let's say the user selects "December" from Combobox1.  The value in XFA1 will therefore be 12.  12 minus 12 equals 0, so that half of the "AND" condition has been met.  Now let's say the user selects 2017 from Combobox2. The selected value will be placed in cell XFC1.   The value in XFD28, which is, by way of the "=YEAR((NOW()))" formula, the current year will be subtracted from the
selected year. Because 2017 minus 2017 equals 0, the second half of the "AND" condition has been met and the error message will properly be displayed.

As far as the "OR" side goes, which is much simpler, any year selected that is greater than the current year, regardless of the month selection will properly cause the error message to display.

Using the above formula is all I need to achieve what I was trying to achieve EXCEPT that I didn't realize until after I completed it that I cannot lock the cells that the error message displays in. This opens up the possibility for the user to be able to delete the error message without having corrected the error. What I'm trying to do is not allow the user to submit a worksheet with the error message displayed; effectively forcing them to correct it before submitting to me.  I want an error message to display that they can't mess with.

Because I ran into trouble with the locked cells issue, I then said to myself let me try using a textbox to display the error message in instead of displaying it in a group of cells. Now, instead of simple formulas, I'm back into macroland as has happened with other worksheets in the past and all the problems that go along with them.  The latest issue, as has happened before with other Excel macro solutions is when I attempt to print the worksheet (which is now macro-driven to get the error message to display or be suppressed), Excel stops responding due to the following:

Problem Event Name:      APPCRASH
  Application Name:      EXCEL.EXE
  Application Version:      15.0.4867.1000
  Application Timestamp:      57d78f3d
  Fault Module Name:      EXCEL.EXE
  Fault Module Version:      15.0.4867.1000
  Fault Module Timestamp:      57d78f3d
  Exception Code:      c0000005
  Exception Offset:      0005f7a0
  OS Version:      6.1.7601.2.1.0.256.4
  Locale ID:      1033

Additional information about the problem:
  LCID:      1033
  skulcid:      1033
 

So, in summary, what started out as a relatively quick attempt to use a formula to achieve a goal has turned into a multi-day fiasco that's now dealing with macros.  Thank you.
0
dbfromnewjerseyAuthor Commented:
Sample file attached.  On my end at least, when I attempt to print the worksheet, Excel stops responding.  If I disable macros, the error goes away but the macros are needed in order to display or not display the error message.
Test-file-for-EE.xlsm
0
Fabrice LambertFabrice LambertCommented:
First thing:
Your printing issue comes bc you have your "parameters" data on the very last columns of the worksheet.
If you don't want users to see these, it is better to put them in a 2nd worksheet that you will hide.

For your data input issue:
The first thing to do is to lock (protect if you prefer) all cells, then unlock only cells users are supposed to manipulate.
Lock the worksheet also, else cells locking will be ineffective.

For you data validation issue:
Use data validation rules whenever it is possible (excel feature, not VBA).
Eventually, VBA can run on the BeforeSave event of the workbook. I'll take a deeper look at it when I'll have more free time in my hands.
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
dbfromnewjerseyAuthor Commented:
The printing issue is not because I have the parameters on the last columns of the worksheet. I could put those parameters anywhere on the worksheet and will experience the same issue.  The problem is that there is a flaw in the Excel software.  I've experienced this type of issue with other worksheets I've worked on over the past few years with functionality that is completely different than what I'm trying to accomplish this time.  That's why when, in my view, the functionality I'm trying to achieve is seemingly very simple, I try to avoid macros as much as possible because they always wind up being very time-consuming to get going.
0
Fabrice LambertFabrice LambertCommented:
Hi,

Sorry for answering that late.
I pretty much reworked your workbook completly:

- Move your data into a separate worksheet named Parameters that is very hidden.
- Month names should be displayed in your country's language.
- Deleted all uneeded columns in sheet1 to ensure the printing range is small (and fix your printing issue).
- Segregated data with the same meaning in it own column (it isn't a good idea to have data with different meaning in the same column).
- Linked combo boxes with cells in sheet Parameters
- made your month combo-box multi columns and display 2nd one (this is done by setting 1st column wodth to 0).
- Deleted the textbox
- Protected Sheet1 (password is "Default", don't forget to change it (change the constant named password it in VBA too).
- Completly rewrote the VBA code.

Please, see attached file.
EE.xlsm
0
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.

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.