Calendar tooltip

[samc2696]

Hi all,


I have a month-by-month calendar in an excel spreadsheet, that currently fills the cell with the event title (based of a table of event data).


What I really want to achieve is that when the user clicks on the cell, a window/textbox will appear with more details of that event in. I would really like to be able to format this text box in a style that matches the calendar too.


Unfortunately the work is confidential, but I should be able to work with the code, I just do not know how to start it.


Many thanks
Sam

 

[Yongle]

I think that this lookup formula should work for you where cell A1 is a cell in your calendar containing the Event Title to look up
(amend G to the last column in your table)
=VLOOKUP(A1,'Event Data'!A:G,2,0)

VLOOKUP looks for a match for A1 in first column of Event_Data columns A:G (ie column A) and returns the value in 2nd column

Amend VBA
Test the formula in Excel first and (assuming it returns the correct value) ...

replace

 .Value = Target.Value & vbCr & DetailsFromCalendar

with

On Error Resume Next
.Value = WorksheetFunction.VLookup(target, Sheets("Event Data").Range("A:[COLOR=#ff0000]G[/COLOR]"), 2, 0)

remembering to replace G with the last column in your table

On Error Resume Next prevents code crashing if Title is not found
- you may think that should never happen but one day it will!!

 

[samc2696]

That has worked perfectly, thank you very much.

One final thing - and I do not think this is possible, as I have been all over the internet looking. Can I change the format of each line in the textbox? I am using vbCrLf in-between each lookup so I have more than just the event type in the tooltip box, but I would like that to be in one format, and then another piece of info in a different format.

I have seen a few examples, but nothing seems to work.

Not to worry if not, thank you all the same!

 

[Yongle]

How about several textboxes
- one for each event element
- each element formatted as you please
- they can be "trained" to appear as one object or displayed separately etc