Mouseover functionality in a CELL

[diwakarm25]

I have a complete resource seating list for an office in MS Excel. For easy referencing of where these resources are seated, I have created a excel based floor map with the respective seat numbers. I have a couple of clarifications where I need your expert advice

1. Based on a specific status (Assigned / Vacant), I want the seat numbers in the floor map to change.

2. Also, I want to create a mouse over reference of the person occupying the seat and the name of the manager when I move my mouse over any specific seat number.

Do we have any inbuilt functionality in excel that can do this job?, else how can we create a macro to perform this activity

Regards

 

[al_b_cnu]

Hi, Insert a comment

 

[diwakarm25]

I missed to add one point. I want the mouseover data to be dynamic so that I don't have to keep changing it every time. I have looked at the Insert Comment and Tooltips. It will not work in this case

 

[al_b_cnu]

Hi,

Sorry, On re-reading your post, perhaps VBA is needed.

What is the layout of your s/sheet?

 

[diwakarm25]

I don't have the option of attaching a sample sheet here. However I will try and explain it

In Sheet 1, I will have the raw data like Seat No, Name, Unique ID, Reporting manager, Organization, seat status,etc

In sheet 2, I will have the floor map with seat numbers mentioned across cells. The floor map will resemble the seating options as available across a floor.

The seat status will have "Assigned" and "Vacant" as the two categories. When the status is set to assigned, I want the relative seat no. cell to turn RED and Green when "Vacant"

Also, if I do a mouseover a cell that is RED, I want to have the Resource Name and Reporting Manager name to appear

Hope this is clear

 

[al_b_cnu]

Hi,

Do you mean something like this:
Sheet1:

Excel Workbook
	A	B	C	D	E	F
1	Seat No	Name	ID	Reporting Manager	Organisation	Seat Status
2	1	Name 1	ID 1	Reporting Manager 1	Organisation 1	Seat Status 1
3	2	Name 2	ID 2	Reporting Manager 2	Organisation 2	Seat Status 2
4	3	Name 3	ID 3	Reporting Manager 3	Organisation 3	Seat Status 3
5	4	Name 4	ID 4	Reporting Manager 4	Organisation 4	Seat Status 4
6	5	Name 5	ID 5	Reporting Manager 5	Organisation 5	Seat Status 5
7	6	Name 6	ID 6	Reporting Manager 6	Organisation 6	Seat Status 6
8	7	Name 7	ID 7	Reporting Manager 7	Organisation 7	Seat Status 7
Sheet1

Excel 2003

continued ....

 

[al_b_cnu]

... continuation

Sheet2:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Seating Plan
2		1	2	3	4	5	6	7	8	9	10
3		11	12	13	14	15	16	17	18	19	20
4		21	22	23	24	25	26	27	28	29	30
5		31	32	33	34	35	36	37	38	39	40
Sheet2

Excel 2003

Are the seat numbers numeric or text?

 

[al_b_cnu]

Given the above, the following sheetchange code (for Sheet1) assumes that a named range 'Seating_Plan' has been defined for the seats in (in the above example) cells B2:K5:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iPtr As Integer
Dim rCur As Range, rSeatPlanRange As Range, rFind As Range
Dim sComment As String
Dim vCurSeatNum As Variant
Dim vaHeadings As Variant, vaData As Variant

If Intersect(Target, Columns("F")) Is Nothing Then Exit Sub
Set rSeatPlanRange = Sheets("Sheet2").Range("Seating_Plan")
vaHeadings = Range("A1:C1").Value

For Each rCur In Target
    If rCur.Column = 6 And rCur.Row > 1 Then
        vCurSeatNum = Cells(rCur.Row, 1).Value
        Set rFind = rSeatPlanRange.Find(vCurSeatNum, LookIn:=xlValues, lookat:=xlWhole)
        If Not rFind Is Nothing Then
            
            On Error Resume Next
            rFind.Comment.Delete
            On Error GoTo 0
            
            sComment = ""
            If LCase$(rCur.Value) = "assigned" Then
                vaData = Range(Cells(rCur.Row, 1).Address, Cells(rCur.Row, 3).Address).Value
                For iPtr = 1 To UBound(vaData, 2)
                    sComment = sComment & CStr(vaHeadings(1, iPtr)) & ": " & CStr(vaData(1, iPtr)) & vbLf
                Next iPtr
                With rFind
                    .Interior.ColorIndex = 3
                    .AddComment sComment
                End With
            Else
                rFind.Interior.ColorIndex = xlNone
            End If
        End If
    End If
Next rCur

End Sub