Show or Hide Shape Based on Empty/Non Empty Cell

[Nu2Java]

I am using this code to show a shape next to the active cell. What I cannot seem to figure out is how to hide the shape if the selected cell is not empty in column D.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Box As Shape
Set Box = Me.Shapes("Flowchart: Alternate Process 10")

If Intersect(Target, Range("D2:D5000")) Is Nothing Then
Dim sObject As Shape
Dim bObject As Shape
    For Each sObject In ActiveSheet.Shapes
        sObject.Visible = False
        Next
Exit Sub
End If

If Not Intersect(Target, Range("D2:D5000")) Is Nothing Then
  For Each bObject In ActiveSheet.Shapes
        bObject.Visible = True
  Next
  End If

If Selection.Left + Selection.Width _
+ Box.Width > Rows(1).Width Then
Box.Left = Selection.Left - Box.Width + 10
Else: Box.Left = Selection.Left + Selection.Width + 2
End If

If Selection.Top + Selection.Height _
+ Box.Height > Columns(1).Height Then
Box.Top = Selection.Top - Box.Height + 15
Else: Box.Top = Selection.Top + Selection.Height - 12
End If

Box.ZOrder msoBringToFront

End Sub

 

[Peter Bartholomew]

There is a VBA macro Forum where you might get more specialist input. That aside, you might like to try a test on the top left cell of the intersection between column D and your user selection.

If Not Intersect(Target, Range("D2:D50")) Is Nothing Then
   Set Target = Intersect(Target, Range("D2:D50")).Item(1)
   If Not Target.Value = vbEmpty Then
      ...

The placement of the Box also looked convoluted.

  With Box
    .Left = Target.Left + Target.Width + 2
    .Top = Target.Top
    .ZOrder msoBringToFront
  End With

would appear sufficient but, no doubt, something is going on.

 

[Nu2Java]

Thanks Peter, your code works for what I want.