Mouseover functionality in a CELL

diwakarm25

New Member
Joined
Apr 28, 2010
Messages
3
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

diwakarm25

New Member
Joined
Apr 28, 2010
Messages
3
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
Hi,

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

What is the layout of your s/sheet?
 

diwakarm25

New Member
Joined
Apr 28, 2010
Messages
3

ADVERTISEMENT

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

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
Hi,

Do you mean something like this:
Sheet1:
Excel Workbook
ABCDEF
1Seat NoNameIDReporting ManagerOrganisationSeat Status
21Name 1ID 1Reporting Manager 1Organisation 1Seat Status 1
32Name 2ID 2Reporting Manager 2Organisation 2Seat Status 2
43Name 3ID 3Reporting Manager 3Organisation 3Seat Status 3
54Name 4ID 4Reporting Manager 4Organisation 4Seat Status 4
65Name 5ID 5Reporting Manager 5Organisation 5Seat Status 5
76Name 6ID 6Reporting Manager 6Organisation 6Seat Status 6
87Name 7ID 7Reporting Manager 7Organisation 7Seat Status 7
Sheet1
Excel 2003

continued ....
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
... continuation

Sheet2:
Excel Workbook
ABCDEFGHIJK
1Seating Plan
212345678910
311121314151617181920
421222324252627282930
531323334353637383940
Sheet2
Excel 2003

Are the seat numbers numeric or text?
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
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:
Code:
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
 

Forum statistics

Threads
1,136,326
Messages
5,675,122
Members
419,551
Latest member
thangxpm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top