Popup a shape in Excel Desired Cell

Admin 1
Viewed 58

I have been trying to popup a shape whenever double click on a cell and I have wrote a code but its not working for me.

All I want is that Whenever I double click on a cell between Range("A1"A100000") then shape will be popup otherwise it will be hidden.

My code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
     If Intersect(Target, Range("A1:A5000")) Is Nothing Then
         ActiveSheet.Shapes("Group 4").Visible = True
     End If
End Sub
vba excel
1 Answers

I have been trying to popup a shape whenever DoubleClick on a cell and i have wrote a code but its not working for me.

You have to use If Not Intersect to check if user clicked on A1:A5000.

Is this what you are trying?

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:A5000")) Is Nothing Then
        Shapes("Group 4").Visible = True
    Else
        Shapes("Group 4").Visible = False
    End If
End Sub

EDIT

Thank you very much its working great but its not moving cell to cell. I mean when i click on cell A100 it remains still where it was before. – Mento 4 mins ago

That is because you are not moving the shape in the code. Is this what you are trying?

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("A1:A5000")) Is Nothing Then
        With Shapes("Group 4")
            .Top = Range("B" & Target.Row).Top
            .Left = Range("B" & Target.Row).Left
            .Visible = True
        End With
    Else
        Shapes("Group 4").Visible = False
    End If
End Sub

Note: If you do not want the cursor blinking when you double click in A1:A5000, then use Cancel = True in the first part of the If

user_11405790
Related