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]

There is another method using invisible hyperlinks:

Basically you put some values in a range on a sheet and then you would run the below code on that range to create hyperlinks that are invisible if you like:

Sub CreateHyperlink()
    Dim rng As Range, rCell As Range
    
    Set rng = Range("A4:A8") ' change to suit

    For Each rCell In rng
        With rCell
            .Formula = "=HYPERLINK(""#MyFunctionkClick()"", " & IIf(IsNumeric(rCell), rCell, """" & rCell & """") & ")"
            .Font.Underline = False
            .Font.Color = vbBlack
        End With
    Next rCell
End Sub

Once you have done that the hyperlinks will fire code when clicked, but the hyperlink cell looks like any other cell. You would also need to paste the below code in the same module as the above code:

Function MyFunctionkClick()
    Set MyFunctionkClick = Selection
    UserForm1.Show ' change to suit
End Function

Now you can go back to the worksheet and click on a link cell which will launch the form, if you navigate with arrow keys the hyperlink will not be triggered.

You could even build the CreateHyperlink sub as a function or even an event that triggers when you unsert a value into a specific range.

hyperlink.xlsm

 

[Jaafar Tribak]

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...

It is difficult to detect if the mouse left button is down on a worksheet. The Selection_Change event doesn't tell us but with a bit of trickery , we should be able to distinguish between clicking with the mouse VERSUS selecting with the keyboard keys while using the Selection_Change event .

Try this adaptation of your code and see how it goes : ( assumes target cells are in column A- change as required)

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#Else
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
#End If

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim i, nrow As Integer
    Dim vKeysArray As Variant, vKey As Variant
 
    vKeysArray = Array( _
        vbKeyReturn, _
        vbKeyTab, _
        vbKeyDown, _
        vbKeyUp, _
        vbKeyLeft, _
        vbKeyRight, _
        vbKeyHome, _
        vbKeyPageUp, _
        vbKeyPageDown, _
        vbKeyEnd _
    )

    For Each vKey In vKeysArray
        If GetAsyncKeyState(vKey) Then Exit Sub
    Next vKey

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

End Sub

 

[robertvdb]

Thanks Jaafar, this does the trick !

One question: when I paste your code, then the line below appears in red. Any idea why this is ?

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer

 

[MARK858]

As Jaafar is probably offline the code is written so it works in both 64bit and 32bit, the opposite version to what you have will appear in red (it doesn't affect the running of the code)

 

[robertvdb]

Thanks Mark.