show userform when CLICKING on a cell, not when RIGHTclicking

[robertvdb]

I have a sheet where I open a userform, when RIGHTclicking on any cell A3:A10.

Now, I want to load the userform not by rightclicking, but by merely CLICKING on any cell A3:A10.

See the image.

Here is my code:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim i, nrow As Integer
i = ActiveCell.Row
nrow = Cells(3, 1).End(xlDown).Row 'sets the last used row

If i > 2 And i < nrow + 1 Then
    If Target.Column = 1 Then
        UserForm5.Show
        Cancel = True
    Else
        Cancel = True
    End If
Else
    Cancel = True
End If

End Sub

 

[Georgiboy]

Perhaps the below will do it:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range(Cells(3, 1), Cells(3, 1).End(xlDown))) Is Nothing Then
        UserForm5.Show
    End If
End Sub

 

[robertvdb]

Thanks Georgiboy,

this might be useful, however the issue remains that when I MOVE (with the down arrow) the cursor from, say, A2 to A3, or A3 to A4, the userform appears. I don't want this. I only want it to appear by CLICKING on A3 etc.

 

[Georgiboy]

Maybe the below adjustment?

Dim inRng As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range(Cells(3, 1), Cells(3, 1).End(xlDown))) Is Nothing Then
        If Not inRng Then
            UserForm5.Show: inRng = True
        End If
    Else
        inRng = False
    End If
End Sub

 

[robertvdb]

alas this doesn't solve the issue... no change.

 

[Fluff]

If you only want to show the userform if you click on a cell then you need to use either the BeforeRightClick or BeforeDoubleClick events.

 

[Georgiboy]

The code i provided in my last post will only show the form once all the time you select cells inside the targeted range, when you select outside of the target range and then back into the target range it will show again.

It is important to make sure that the below line is above the sub line:

Dim inRng As Boolean

Is your data contiguous in the targeted area?

 

[robertvdb]

Thanks Georgiboy for your reply. Yes, my data is contiguous.

However, your valuable solution does not solve my initial question. When I MOVE the cursor to the targeted area (using the arrow keys), it does the same as CLICKING on the target cell. I'm afraid that Fluff (see above) is right...

 

[Georgiboy]

I see what you mean now, I made my code so that it would only pop up the UF once. You can move around without it popping up again.

I missed the bit about mouse clicks, Fluff is right, there are workarounds I believe but not wilthout a ton of code and a buggy spreadsheet full of events.

 

[robertvdb]

OK Thanks. So we agree that there is no simple solution for the issue I raised initially. At least, you helped me in accepting/learning this. Thanks for your efforts.