How to I return the position of a selected cell in a range?

[dareks]

Hi all,

I am trying to create a routine in VBA using which I should be able to
do the following:

- Lets say I have 10 column headers
- They have long descripotions (e.g. "Time spent on.....") but a short
header name (e.g. TM1).
- The descriptions in the corresponding columns in a different row
(hidden)

I was to be able to display a comment with the appropriate text
whenever the user clicks on a header cell.

I am half way there. Code attached: What I can't figure out is how to
return the position of the cell that the user selects so that I can
refer to the right text for the comment. Any help is highly
appreciated.

Code-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim newtext$
Dim Addr$
Dim RangeToCheck As Range
Dim RangeComment As Range
Dim isect As Range
Dim intRow As Integer
Dim intCol As Integer

If Target.Cells.Count > 1 Then
Exit Sub

Set RangeToCheck = ThisWorkbook.Sheets("Rep Data").Range("headers")
RangeComment = ThisWorkbook.Sheets("Rep Data").Range("headertext")
Set isect = Application.Intersect(Target, RangeToCheck)

If isect Is Nothing Then Exit Sub
Addr = isect.Address
'intCol = isect.Row
ThisWorkbook.Sheets("Rep Data").Range("row").Value = Addr
'ThisWorkbook.Sheets("Rep Data").Range("col").Value = intCol

'newtext = RangeComment.(intCol).Value
'isect.Comment.Text newtext
End If
End Sub

 

[Tom Ogilvy]

Target is the cell the user selected.

so if you description is one row below

desc = Target.Offset(1,0).Value

 

[dareks]

Thanks Tom. It's working now. However, I have one small question -
Now, everytime I select a cell in the header row, a comment pops up -
but it remains there when I move to the next one. Is there a way to set
the comments to be visible only when the user has selected that cell?

My revised code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim newtext$
Dim RangeToCheck As Range

If Target.Cells.Count > 1 Then Exit Sub

Set RangeToCheck = ThisWorkbook.Sheets("Rep Data").Range("headers")
Set isect = Application.Intersect(Target, RangeToCheck)

If isect Is Nothing Then Exit Sub

newtext = isect.Offset(1, 0).Value

With isect.AddComment
.Visible = True
.Text newtext
End With

End Sub

Thanks again!

Kunal

 

[Tom Ogilvy]

Untested, but possibly:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim newtext$
Dim RangeToCheck As Range

for each cmt in Activesheet.Comments
cmt.Visible = False
Next

If Target.Cells.Count > 1 Then Exit Sub

Set RangeToCheck = ThisWorkbook.Sheets("Rep Data").Range("headers")
Set isect = Application.Intersect(Target, RangeToCheck)

If isect Is Nothing Then Exit Sub

newtext = isect.Offset(1, 0).Value

With isect.AddComment
.Visible = True
.Text newtext
End With

End Sub

Although I am not sure why you don't just create all your comments one time
(or in the sheet activate event) and let excel perform it normal behavior of
displaying it when the mouse goes over the cell and hiding if not.

 

[dareks]

Thanks a ton Tom. That worked! And I agree with you. It was a bit silly
of me not to think of your last suggestion.

Cheers!
Kunal