(Win XP Home SP2, Office XP)

Hi all,

I have a collection of discs in numerous cases that each hold 510
discs.

Column A has the disc number info (#'s 1 - 2600)

I would like to be able to mouse over or click the cell with the disc
number and it tell me what case and position the disc is situated.

E.G. Discs 1 to 510 are in positions 1 to 510 in case 1, Discs 511 to
1020 are in positions 1 to 510 in case 2 etc.

If I mouse over or click the cell for disc #530 I would like to see a
popup or similar telling me that the disc is in position 20 in case 2
(2-20 format would suffice).

Any help would be greatly appreciated.

TIA

Robbie

This does it on a cell select

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
MsgBox "Case " & (.Row \ 510 + 1) & _
", position" & .Row - ((.Row \ 510) * 510)
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob Phillips

wrote in message
oups.com...
(Win XP Home SP2, Office XP)

Hi all,

I have a collection of discs in numerous cases that each hold 510
discs.

Column A has the disc number info (#'s 1 - 2600)

I would like to be able to mouse over or click the cell with the disc
number and it tell me what case and position the disc is situated.

E.G. Discs 1 to 510 are in positions 1 to 510 in case 1, Discs 511 to
1020 are in positions 1 to 510 in case 2 etc.

If I mouse over or click the cell for disc #530 I would like to see a
popup or similar telling me that the disc is in position 20 in case 2
(2-20 format would suffice).

Any help would be greatly appreciated.

TIA

Robbie

Thanks for the speedy reply Bob.

It almost works. Your code refers to row numbers when in fact it is the
cell value that is required as the data can be sorted differently at
any given time.

e.g. cell value is 1000 therefore it should return Case 2, Position 490

Would it be best to divide the cell value by 510 and then round up to
the nearest whole number to determine Case number?

For position perhaps loop the cell value minus 510 until the answer is
< 511, if cell value is less than 510 then position equals cell value.

Your help would be greatly appreciated again.

Perhaps if I had looked at your code before posting I would have seen
it does everything I needed.

Thanks again Bob it was much appreciated.

All I did was replace the ".Row" with ".Value" and all worked to
perfection.

Robbie