?
Solved

I need help with an Excel template

Posted on 2014-11-30
5
Medium Priority
?
196 Views
Last Modified: 2014-12-14
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
0
Comment
Question by:mainrotor
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 40473115
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 1000 total points
ID: 40473504
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
 

Author Comment

by:mainrotor
ID: 40474611
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40475726
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
 

Author Comment

by:mainrotor
ID: 40485556
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

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question