mouse over cell -> listbox

[Ago]

hey all! :hi:
im new here, and also quite new in VBA.

i have a question, is it possible to make a macro that shows a listbox (or simular) when the cursor is above a cell.

i have a excellsheet that contains a list of names and IPs (and other not so important stuff).
in column G is where all the IPs are, and the list is growing each day.

when i move my cursor over a cell in column G i want it to show a box with names that use the same IP.

i think i can do the code for matching IP and names but i dont knwo how to make the box appear (and dissapear).

help please


EDIT: something like on this forum is what i had in mind, when your mouse is over a thread you can read the thread in a box. but with the names instead.

[Bob Phillips]

Post details of where the data is (columns, sheets, etc.) or preferably an example workbook.

[Ago]

this is the file!

EDIT: ohh.. it was too big. i had to cut it down alot in size, sorry for that

[Bob Phillips]

How about this? When you select an item in column G, it filters by that value, so you see all the associated names.

It goes in the worksheet code module.



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mpIPList As Variant
Dim mpIPCount As Long
Dim mpLastRow As Long

Application.EnableEvents = False

With Target

If .Column = 7 And .Row > 5 Then

If .Value <> "" Then

ReDim mpIPList(1 To 1000)
mpLastRow = Me.Cells(Me.Rows.Count, .Column).End(xlUp).Row
.Cells.AutoFilter Field:=6, Criteria1:=.Value
Set mpdata = .Cells(6, .Column).SpecialCells(xlCellTypeVisible)
mpIPList = mpdata
' Me.Cells.AutoFilter
End If

Else
.Cells.AutoFilter Field:=6
End If
End With

Application.EnableEvents = True
End Sub

[Ago]

Thank you!
it works fine, but it creates a problem.
i dont dare to get close to G column :-)
and i cant select "across" G without the macro crashing, ex: G15-G16.

when i copy in new data to the list it doesnt give me the option to use the textimport guide, but once the macro is crashed it works.

is it possible to use the comments? if i would add a comment to each G-cell, is it possible to type on the comment trough a macro?

[Bob Phillips]

The crash is easily handled



Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mpIPList As Variant
Dim mpIPCount As Long
Dim mpLastRow As Long
Dim mpdata As Range

Application.EnableEvents = False

With Target

If .Cells.Count = 1 Then

If .Column = 7 And .Row > 5 Then

If .Value <> "" Then

ReDim mpIPList(1 To 1000)
mpLastRow = Me.Cells(Me.Rows.Count, .Column).End(xlUp).Row
.Cells.AutoFilter Field:=6, Criteria1:=.Value
Set mpdata = .Cells(6, .Column).SpecialCells(xlCellTypeVisible)
mpIPList = mpdata
' Me.Cells.AutoFilter
End If

Else
.Cells.AutoFilter Field:=6
End If
End If
End With

Application.EnableEvents = True
End Sub




is it possible to use the comments? if i would add a comment to each G-cell, is it possible to type on the comment trough a macro?

I don't understand that bit.

[Ago]

if you look in H2 on my sheet there is this red thing at the top (i hope).
when the mouse is over H2 a messagebox appears with information.

what i mean was if its possible to use those perhaps?

[Bob Phillips]

You can add a comment, but how would it get updated if that IP address is added to another cell? The only way would be to re-create every comment after each cell change, and that is ridiculous to me.