Solved

Date entered in userform used for looking up date range

Posted on 2014-10-03
8
122 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now