I need help with an Excel template

Hi Experts,
I have an Excel Spreadsheet that I want to use as a template for employees to fill in their work availability for any given month.

In cell B9, I have a drop down control with all the months as values (see image 1, below).
In column A, starting at cell A11, I have values 1-31 for the days of the month
In Column B, starting at cell B11, I have a combo box where employees select from the following values (Available, or Not-available)

Image 1

If an employee is submitting his availability for a month with only 30 days, I want the cell in column A with the value 31 and the combo box next to it to become disabled (see image 2, below).  
image 2I have used VBA before just not in Excel.  How do I do this in Excel 2007?  Do I need to use VBA to accomplish this?

Thank you very much in advance,
mrotor
mainrotorAsked:
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.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

I'm wondering why you would use VBA controls (combo boxes) for this. They are a pain to place (individually) and a pain to maintain. You'd have to assign a sheet cell to capture the value of each combo box. Why the hassle?

Instead of combo boxes you could use Excel's spreadsheet tool for data entry validation: Data validation with a list.

Here is a sample file. Invalid days are greyed out. Selections are made with data validation.

Note that users can still use the availability drop-down in the gray cells, but with the color coding of the user interface they are less inclined to do so.  Who would log availability against a non-defined date? (I know, I know..... users will do anything to upset our carefully constructed solutions).

cheers, teylyn
sample.xlsx
0
Rob HensonFinance AnalystCommented:
Alternative option with no helper columns/sheets.

In A11 use following formula:

=DATEVALUE(1&" "&B9&" "&YEAR(TODAY()))

In A12 use following formula:

=IF(A11="","",IF(MONTH(A11+1)>MONTH(A11),"",A11+1))

Copy down to A41

Format A11 to A41 as "dd" or "d"  depending on whether you want single digit values to appear as two digit, eg 09.

Also slightly simpler Conditional Formatting Rule:

Formula:   =$A11=""

Applies to $A11:$B41

You could get round the input into invalid cells by using a Custom Data Validation that checks whether column A contains a Date but you lose the drop down arrow functionality. You can get round it by making sure the "Available" and "Not Available" are in cells above the input area so using Alt & Down arrow gives a Pick From list.

New version attached.

Thanks
Rob H
sample.xlsx
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
mainrotorAuthor Commented:
Teylyn and Rob thanks for your replies.

I will need to import the information from the Excel spreadsheets into an Access database.  There shouldn't be a problem with me doing so using either of your solutions, correct?

Thank you both, I will try your solutions later today.

mrotor
0
Rob HensonFinance AnalystCommented:
The only issue I would foresee is Access normally expects tables to start with headers on row 1. The way round would be to give the data area a range name. The import can then look at the specific area. The range name can be dynamic to ignore the rows with no date.

Thanks
Rob H
0
mainrotorAuthor Commented:
So, I tried both of your solutions.  I like them both.  I will try to incorporate both of your solutions in my project.  I will keep you posted on the outcome.

thank you,
mrotor.
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.