JimGray-7725 avatar image
0 Votes"
JimGray-7725 asked

Limit Target range with Selection Change in VBA

My objective is to show the contents of a formulas cell that is looking up a list of descriptions to match item codes (around 500) in a Pivot table (the existing table descriptions were free text and create too many rows for the each item code). The look up item list is using a new set of standard descriptions from another query table. The descriptions can be very long and I want to keep the description column narrow by only showing the description if the cell if column A is selected.

I have VBA code to show the contents of the formula cell when selected. This works okay for the column A target. However, the comments are created and don't delete when I move out of column A and another cell is selected. Can this code be modified to delete the comment when any other cell (restrict to Column A) is selected in the worksheet.

Private LastTarget As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 1 Then

On Error Resume Next

 If Not LastTarget Is Nothing Then
     If Not LastTarget.Comment Is Nothing Then LastTarget.Comment.Delete
 End If

 If Not Trim$(Target.Value) = vbNullString Then
     If Target.Comment Is Nothing Then
         Target.AddComment Target.Text
         Target.Comment.Visible = True
         Target.Comment.Shape.Width = 300 'Change as needed
         Target.Comment.Shape.Height = 50 'Change as needed
         Target.Comment.Shape.Fill.Transparency = 0#  'Make the comment a little see through
     End If
 End If
 Set LastTarget = Target

End If

End Sub

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

0 Answers