Why does a macro assigned to a cell fail?

Question

I have a macro assigned to a cell that executes once clicked on. The macro involves SAP GUI scripting, which if ever interrupted (via CTRL+DEL+ALT), also causes my previously clickable cell to stop working.

I can run the macro via Developer yet the cell is not working. Note that the code provided below is pasted in the sheet where the clickable cell is, not in ThisWorkbook.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal target As Range)

   If Selection.CountLarge = 1 Then
    
   If Not Intersect(target, Range("A10")) Is Nothing Then
       Call FolderPicker
   End If
    
End Sub

Any ideas what might be causing the issue?

You have an if statement without an `End If` to close it off — Tim Stack, May 28 '19 at 11:31
Apart from what @Tim said - do you disable events anywhere in your code? — Darren Bartrup-Cook, May 28 '19 at 11:37
Sorry about the End If part, it got cut off in copy/pasting. To rephrase, even if we forget about SAP scripting for a moment, if I run or click on FolderPicker cell and instead of selecting a folder I chose to stop the process by canceling, I won't be able to run it again by clicking the cell until I reopen the Excel file (only by running the code in Developer). The error I get is Invalid procedure call or argument but I guess this is a really general warning. — vlad_milovanovic, May 28 '19 at 12:17

score 0 · Answer 1 · answered May 28 '19 at 11:36

0

You need to close all if statements.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal target As Range)

   If Selection.CountLarge = 1 Then

      If Not Intersect(target, Range("A10")) Is Nothing Then
       Call FolderPicker
      End If
   End If

End Sub

answered May 28 '19 at 11:36

QuickSilver

730
5
28

Thanks QuickSilver, I somehow cut off the End If part while copy/pasting. – vlad_milovanovic May 28 '19 at 12:17

score 0 · Answer 2 · answered May 28 '19 at 11:43

0

You could use:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Check count how many cells affected to avoid errors
    If Target.Count = 1 Then

        If Not Intersect(Target, Range("A10")) Is Nothing Then
            Application.EnableEvents = False 'Disable events to avoid pointless code trigger
                Call FolderPicker
            Application.EnableEvents = True
        End If

    End If

End Sub

answered May 28 '19 at 11:43

Error 1004

7,877
3
23
46

Thanks Error 1004, I've modified the code but I believe something else is causing the issue here. I've tried rephrasing in the comment above. – vlad_milovanovic May 28 '19 at 12:18
Do you mnanage to find a solution? – Error 1004 May 28 '19 at 12:20
No nothing yet, I'm still testing so I'll report here if I figure it out. – vlad_milovanovic May 29 '19 at 15:28

Why does a macro assigned to a cell fail?

2 Answers2