Solved

Date entered in userform used for looking up date range

Posted on 2014-10-03
8
140 Views
Last Modified: 2014-10-06
I am using an Excel userform with various entry fields and listboxes for spreadsheet data entry.  One of the entry fields is a date.  I want to use the date entered to search a worksheet with date ranges to obtain the Academic Year and Academic Term that the entry date belongs.  I have a formula in the attached file to do the lookup, but want to use VBA to obtain the information.
DateLookupExample.xlsm
0
Comment
Question by:deedeheard
[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
  • 4
  • 4
8 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 40361472
You mean in the Form you want to select the student on the list then put a date and you want to get Academic Year and Academic term show up ? if yes you want this in a mesagebox or ?
and are you referring to the information you have in the sheet Adjustments ?

Like you want to eliminate this sheet completely and simply have the form to querry this info ? is this what your looking for ?
gowflow
0
 

Author Comment

by:deedeheard
ID: 40361605
The student is selected in the user form and a date is entered. When the dad button is clicked, I want to use the entered date to lookup the corresponding academic year and academic term and write a record (with student name , student number, date, academic year and academic term) in the worksheet. I hope this makes sense.
0
 

Author Comment

by:deedeheard
ID: 40361619
I have two academic year and academic terms in the adjustments worksheet, but don't want to use the columns with the formulas - I want to determine and populate the Academic Year Value and Academic Term Value from vba..
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 30

Expert Comment

by:gowflow
ID: 40361845
ok then you want to update the Sheet 'adjustments' with the values from the form (for sure adding year and term) right ?? In other words we will be overriding the formulas ??

gowflow
0
 

Author Comment

by:deedeheard
ID: 40362147
Yes
0
 
LVL 30

Accepted Solution

by:
gowflow earned 500 total points
ID: 40362305
ok here it is.
I added a button on the sheet Adjustments to display the form anytime you want.

Just choose several combinations of dates
System will check for dates outside the listed ones and advise you
also system will check if the combination already exist and will advise you.

Let me know.
gowflow
DateLookupExample-V01.xlsm
0
 

Author Closing Comment

by:deedeheard
ID: 40362665
This is awesome!  Thank you so much!
0
 
LVL 30

Expert Comment

by:gowflow
ID: 40363153
Your welcome glad I could assist.
gowflow
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

739 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