• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Show or Hide Shape Based on Empty/Non Empty Cell


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
Exit Sub
End If

If Not Intersect(Target, Range("D2:D5000")) Is Nothing Then
  For Each bObject In ActiveSheet.Shapes
        bObject.Visible = True
  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

Well-Known Member
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.