Lesson reservation calendar

**Frungy** · 07-27-2011, 03:14 AM

I would *really* appreciate some direction on where to look for help on my lesson reservation calendar.
I tried searching the forums but I don't know exactly what I should look for.

Basically, I want to be able to put names onto an English lesson reservation calendar and be able to click directly on the name to send e-mail.

I already know how to create a hyperlink in Excel to send e-mail.

What I don't know how to do is have Excel autofill and create a hyperlink automatically every time I put their name on a certain date on the calendar.

A couple details if you need them:

I am running a small English school with around 50 students. They take a certain number of lessons before going abroad.
So, I have workbook that has one worksheet with a list of names of students. I keep track of when they take each lesson.

For example:
SMITH, JOHN - Lesson 1: 2/4/2011 | Lesson 2: 3/3/2011 | Lesson 3: |
^-- Currently, this name is hyperlinked to their e-mail address.

This is fine, but on another sheet I have a reservation calendar with spaces for their names. They reserve lessons ahead of time and I put their name into the calendar.
Some days can have 10 to 12 people reserved. I want to be able to autofill or choose their name from a list and have it automatically create a hyperlink the same as the name on the other worksheet.

For example:
July 27th:
SMITH <-- I would like this name to be linked to the mail address on the lesson record.
JOHNSON
EARP.. etc.

Is this possible, and how would I go about looking for the solution?

**Miraun** · 07-27-2011, 04:59 AM

Hi Frungy;

There was a lot of information in that post... There are many questions, each of which build off of eachother. From experience, it's best to break these up, and post one specific question at a time. Additionally, while I know you've described your worksheet, posting a sample workbook (while removing any personal information) makes it much easier to give you better advice.

"create a hyperlink automatically when I put a name in"

Creating it automatically would require a macro that is performed upon worksheet update. This can be imbedded directly into the particular sheet that you're working on. Since I don't know the e-mail hyperlinks you've been using, or how you're looking to set up your e-mails, that's the only direction I can point you in for that question.

"using drop down menus to select students for reservations"

This is used by data validation... but I'm not sure if you're looking to have your entire list of students, or just a subsection based upon some other scheduling.

**Frungy** · 07-27-2011, 08:38 AM

Miraun:

Thank you very much. I have to admit I have never posted in a forum before, so the advice is appreciated.

I've attached a sample workbook. It seems like a simple thing to do, but I just don't know what to look for. If someone could help me out it would be great!

In return, I'll teach you to say anything you want in Japanese, one phrase! (Keep it clean, though.)

**Miraun** · 07-28-2011, 02:23 AM

Flying from Bangalore to Boston today/tonight/tomorrow/forever... I'll download the example you posted and get back to you... eventually... unless another member chimes in.

I spent 4 months working out of Tokyo, living over at the Hilton in Shinjuku, commuting over to Kawasaki for a bit, and then at an office next to Suitengumae. Can't really speak any Japanese anymore, but I can pretty well understand spoken.

**Frungy** · 07-28-2011, 05:34 AM

Well, please take your time. I'm relying completely on your generosity so I'll just wait. If I find the solution myself (... probably unlikely) I'll post what I found.

**solnajeff** · 07-28-2011, 10:04 AM

Hi

Please find attached an amended workbook to achieve what you are looking for, albeit slightly differently.

I hope that you can make use of it.

Regards

Jeff

**brokenbiscuits** · 07-28-2011, 11:07 AM

Hi Frungy

Try the attached macro, I think this should work for your needs. You do need to add a little more data on the lesson record tab (ID and e-mail for each student), but once this is done once for each student it should work fine.

Regards

BB

**Miraun** · 08-01-2011, 05:18 PM

Sub Createlink()
Dim Current As Range
Dim Startsheet As String
Dim Name As String

Application.ScreenUpdating = False
Startsheet = ActiveSheet.Name

MsgBox (ActiveRange)
For Each Cell In Selection
    Set Current = ActiveCell
    Name = ActiveCell.Value
        If ActiveCell.NumberFormat = "m/d;@" Then
        Exit For
    Else
        Sheets("Lesson Record").Select
        Cells.Find(What:=Name, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
            On Error GoTo Error
        ActiveCell.Copy
          Sheets(Startsheet).Current.Select
        ActiveCell.Paste
    End If
Error:
    Next
Application.ScreenUpdating = True

End Sub

Hey Frungy;

Sorry for the delay. This code will basically do a quick search on your Lesson Record page, and replace the current cell with the cell (containing the hyperlink) from the Lesson Record. Unfortunately, I had problems using ranges properly, so you can only run it on a single cell at a time... it'll take me another hour or so to make it applicable for a range.

I took a look at the other responses, and I like Brokenbiscuits response better than my own. Although, I'm not sure how much data you actually have in your sheet as to whether or not the separation of the e-mail address is feasible.

**Frungy** · 08-06-2011, 04:35 AM

Hello solnajeff, brokenbiscuits, and Miraun!

Forgive my delay. I tried connected to the forum for the last couple days but it seemed be down. Not sure why I couldn't connect. With all these ideas, I think I can get my Lesson Record working great.

As for excel, I can't really offer anything to you guys but again, if you need something translated or help with Japanese, let me know... I don't know how else I can thank you (other than clicking the scales.) :D Thank you very much.