PDA

View Full Version : Display text on cell selection



fatalcore
02-04-2012, 10:31 AM
Hi,
I have a mapping sheet called "Map2". Here I have three types of data's [Sheet Name, Field Name, Message ].

What I want is when I select the mapped cells of the sheet, the message against that field name is displayed in the B2 cell of that sheet.

Thanks in advance.

p45cal
02-04-2012, 02:46 PM
try this in the Thisworkbook's code module (and you'll have to make sure that the sheet names in column A of Map2 are all spelt correctly as none of them are!:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
x = Replace(Replace(Split(Target.Address(0, 0, external:=True), "]")(1), "'", ""), "!", "")
With Sheets("Map2")
For Each cll In Intersect(.UsedRange, .Columns(1)).Cells
If Not IsEmpty(cll.Value) Then currentSht = cll.Value
zz = currentSht & cll.Offset(, 1)
If currentSht & cll.Offset(, 1) = x Then
Sh.Range("B2").Value = cll.Offset(, 2).Value
Exit For
End If
Next cll
End With
End Sub
Tested. It should get you started. Also see attached.

fatalcore
02-04-2012, 07:32 PM
Thanks p45cal. You are awesome !

p45cal
02-05-2012, 07:19 AM
Well thanks! However, I made heavy weather of:
x = Replace(Replace(Split(Target.Address(0, 0, external:=True), "]")(1), "'", ""), "!", "")
where I should've used the simpler:
x = Sh.Name & Target.Address(0, 0)

fatalcore
02-05-2012, 07:22 AM
p45cal, u r a true valued human ! Godbless !!!

fatalcore
02-08-2012, 06:48 AM
Hi ,
I just need another help.
There are some cells which are merged in the sheets. When I am clicking these cells the messages are not updating. Can somebody please help me.
I have highlighted few cells for example.
Thanks in advance.

p45cal
02-08-2012, 08:34 AM
You can do one or the other of the following, but not both:
1. change the x = Replace(Replace... line to:
x = Sh.Name & Target.Cells(1).Address(0, 0)
or:
2. on the Map2 sheet, change the likes of A15 of to A15:D15 being the full extent of the address of the merged cells.

fatalcore
02-08-2012, 09:32 AM
Thanks for the tip mate ! :D Awesome !!!
It solves the problem !!!