PDA

View Full Version : [SOLVED] Run Macro only if selected by left click



thk12205
03-29-2018, 09:08 AM
Excel Version: 2013 Professional Plus

I am trying to make a cell change values only if it is selected by a left click.

I tried to fix it by adding in "if left clicked" macro I found online that allows left clicks to be tracked by VBA.

Unfortunately, Public Declare Function is not allowed in the Microsoft Excel Objects, and changing it to Private seems to make the code not run at all (after I click, nothing activates, not even error pop up)

How can I fix this? And is there better way of getting macros to activate only with left click on cell?





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


Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Application.ScreenUpdating = False

For n = 7 To 25

'Set thisCell = Range(Cells(n, "H"))
If Target.Address = Cells(n, "H").Address Then
If GetAsyncKeyState(vbKeyLButton) And &H8000 Then
If Cells(n, "H") = 0 Then
If Cells(n, "I") = 3 Then
Cells(n, "H") = 1
Range(Cells(n, "F"), Cells(n, "G")).Select
Exit Sub
ElseIf Cells(n, "I") = 2 Then
MsgBox "System is 'In-Work'"
ElseIf Cells(n, "I") = 1 Then
MsgBox "System is 'Line Stopped'"
End If

ElseIf Cells(n, "H") = 1 Then
Cells(n, "H") = 0
Range(Cells(n, "F"), Cells(n, "G")).Select
Exit Sub

Else
Cells(n, "H") = 0
Range(Cells(n, "F"), Cells(n, "G")).Select
Exit Sub

End If
End If
End If

Next

Application.ScreenUpdating = True


End Sub

SamT
03-29-2018, 10:47 AM
It is built in to allow macros to run only on a doubleclick


Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Intersect(Range("H7:H5"), Target) Is Nothing Then Exit Sub
Cancel = True

'Only change the cells in the doubleclicked Row
If Target = 0 Then
Select Case Target.Offset(, 1)
Case 1: MsgBox "System is 'Line Stopped'"
Case 2: MsgBox "System is 'In-Work'"
Case 3: Target = 1
Case Else 'Do Nothing
End Select

ElseIf Target = 1 Then
Target = 0
Else 'Do Nothing
End If

Rows(Target.Row).Range("F1:G1").Select
End Sub

thk12205
03-29-2018, 02:03 PM
Thank you for this! Not only did you solve my problem, but you showed me a better way to script.

One question though. I noticed by adding ",Cancel As Boolean" and "Cancel = True" changes the previous macro from "select to activate" to "double click to activate". What is the logic behind this?

SamT
03-30-2018, 06:44 AM
adding ",Cancel As Boolean" and "Cancel = True" changes the previous macro from "select to activate" to "double click to activate".
That's not what happened. I gave you a different Event Sub. I didn't "add" Cancel. It is an intrinsic requirement of the BeforeDoubleClick Event sub.

thk12205
03-30-2018, 09:01 AM
Ahh, I see. I didn't see the different event.

Thank you SamT.