Solved

Date entered in userform used for looking up date range

Posted on 2014-10-03
8
133 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
  • 4
  • 4
8 Comments
 
LVL 29

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 29

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 29

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 29

Expert Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

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…
The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
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…

773 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