Limit Target Range with Selection Change in VBA

%3CLINGO-SUB%20id%3D%22lingo-sub-2820496%22%20slang%3D%22en-US%22%3ELimit%20Target%20Range%20with%20Selection%20Change%20in%20VBA%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2820496%22%20slang%3D%22en-US%22%3E%3CP%3EMy%20objective%20is%20to%20show%20the%20contents%20of%20a%20formulas%20cell%20that%20is%20looking%20up%20a%20list%20of%20descriptions%20to%20match%20item%20codes%20(around%20500)%20in%20a%20Pivot%20table%20(the%20existing%20table%20descriptions%20were%20free%20text%20and%20create%20too%20many%20rows%20for%20the%20each%20item%20code).%20The%20look%20up%20item%20list%20is%20using%20a%20new%20set%20of%20standard%20descriptions%20from%20another%20query%20table.%20The%20descriptions%20can%20be%20very%20long%20and%20I%20want%20to%20keep%20the%20description%20column%20narrow%20by%20only%20showing%20the%20description%20if%20the%20cell%20if%20column%20A%20is%20selected.%3C%2FP%3E%3CP%3EI%20have%20VBA%20code%20to%20show%20the%20contents%20of%20the%20formula%20cell%20when%20selected.%20This%20works%20okay%20for%20the%20column%20A%20target.%20However%2C%20the%20comments%20are%20created%20and%20don't%20delete%20when%20I%20move%20out%20of%20column%20A%20and%20another%20cell%20is%20selected.%20Can%20this%20code%20be%20modified%20to%20delete%20the%20comment%20when%20any%20other%20cell%20(restrict%20to%20Column%20A)%20is%20selected%20in%20the%20worksheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20LastTarget%20As%20Range%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_SelectionChange(ByVal%20Target%20As%20Range)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Target.Column%20%3D%201%20Then%3C%2FP%3E%3CP%3EOn%20Error%20Resume%20Next%3C%2FP%3E%3COL%3E%3CLI%3EIf%20Not%20LastTarget%20Is%20Nothing%20Then%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIf%20Not%20LastTarget.Comment%20Is%20Nothing%20Then%20LastTarget.Comment.Delete%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3BEnd%20If%3C%2FLI%3E%3CLI%3E%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3BIf%20Not%20Trim%24(Target.Value)%20%3D%20vbNullString%20Then%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BIf%20Target.Comment%20Is%20Nothing%20Then%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BTarget.AddComment%20Target.Text%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BTarget.Comment.Visible%20%3D%20True%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BTarget.Comment.Shape.Width%20%3D%20300%20'Change%20as%20needed%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BTarget.Comment.Shape.Height%20%3D%2050%20'Change%20as%20needed%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BTarget.Comment.Shape.Fill.Transparency%20%3D%200%23%26nbsp%3B%20'Make%20the%20comment%20a%20little%20see%20through%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3BEnd%20If%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3BEnd%20If%3C%2FLI%3E%3CLI%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FLI%3E%3CLI%3E%26nbsp%3BSet%20LastTarget%20%3D%20Target%3C%2FLI%3E%3C%2FOL%3E%3CP%3EEnd%20If%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2820496%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

  1. If Not LastTarget Is Nothing Then
  2.      If Not LastTarget.Comment Is Nothing Then LastTarget.Comment.Delete
  3.          
  4.  End If
  5.  
  6.        
  7.  If Not Trim$(Target.Value) = vbNullString Then
  8.      If Target.Comment Is Nothing Then
  9.          Target.AddComment Target.Text
  10.          Target.Comment.Visible = True
  11.          Target.Comment.Shape.Width = 300 'Change as needed
  12.          Target.Comment.Shape.Height = 50 'Change as needed
  13.          Target.Comment.Shape.Fill.Transparency = 0#  'Make the comment a little see through
  14.      End If
  15.      
  16.  End If
  17.     
  18.  Set LastTarget = Target

End If

0 Replies