Forum Discussion
VBA Code stops
Not sure what is going on... this was working and now all the sudden the code stops part way through.
Here is the current code (linked to a button)
Private Sub CommandButton2_Click()
Dim cofCom As Object
Set cofCom = Application.COMAddIns("SapExcelAddIn").Object
Dim api As Object
Set api = cofCom.GetPlugin("com.sap.epm.FPMXLClient")
api.RefreshActiveWorkbook
Application.ScreenUpdating = False
ActiveWorkbook.Sheets("Spend Detail").Activate
Sheet14.CommandButton2_Click
Sheet14.CommandButton1_Click
ActiveWorkbook.Sheets("Spend Detail 1").Activate
Sheet21.CommandButton4_Click
Sheet21.CommandButton3_Click
ActiveWorkbook.Sheets("Spend Detail 2").Activate
Sheet22.CommandButton6_Click
Sheet22.CommandButton5_Click
ActiveWorkbook.Sheets("Spend Detail 3").Activate
Sheet23.CommandButton8_Click
Sheet23.CommandButton7_Click
ActiveWorkbook.Sheets("Spend Detail 4").Activate
Sheet24.CommandButton10_Click
Sheet24.CommandButton9_Click
ActiveWorkbook.Sheets("Spend Detail 5").Activate
Sheet25.CommandButton12_Click
Sheet25.CommandButton11_Click
ActiveWorkbook.Sheets("Spend Detail 6").Activate
Sheet27.CommandButton14_Click
Sheet27.CommandButton13_Click
ActiveWorkbook.Sheets("Finance Input Sheet").Activate
Application.ScreenUpdating = True
End SubThe problem is that all tabs update for the refresh but for some reason, going to each sheet and clicking the buttons is not occurring now. It is essentially stopping at line 6 and I have tried removing the screenupdating false and true and that did not make any difference. Just like for some reason the code stops and it was working a week ago.
Any suggestions?
4 Replies
Please share the complete VBA code and let us know exactly where it stops or if you're getting any error message. This will help identify the issue and suggest the correct fix.
The code stops because an unhandled error occurs in one of the button‑click procedures after the SAP refresh. Because ScreenUpdating = False is active and you have no error handler, the error is silent and execution halts.
Try these steps in order:
1. Make errors visible – Add a basic error handler:
On Error GoTo EH ... Exit Sub EH: Application.ScreenUpdating = True MsgBox "Error: " & Err.DescriptionThis will reveal exactly which line fails and why.
2. If no error message appears but execution still stops, the fault is likely that a button subroutine is now Private or has been renamed/deleted. In the VBA editor, check that each called macro (Sheet14.CommandButton2_Click etc.) exists in the appropriate sheet module and is Public (or has no scope keyword).
3. If the error points to missing data, the SAP refresh may now finish asynchronously. Insert a short wait (Application.Wait Now + TimeValue("0:00:03")) or, if the add-in supports it, check api.IsRefreshRunning in a loop before continuing.
4. Confirm sheet code names – If a sheet was deleted and re‑added, its internal Sheet14 name may have changed. In the Project Explorer, verify the name in parentheses matches the SheetXX you are calling.
Start with the error handler – it will instantly pinpoint the breaking point.
My answers are voluntary and without guarantee!
Hope this will help you.
however, does anyone have any ideas as to how I could force the file to only refresh specific tabs and not all the tabs?
Can you place the file in a trusted folder?
However, I would avoid selecting sheets and calling the routines associated with the buttons (rows 8 ... 28).
Unfortunately, you're referring to a specific call to SAP, so without knowing the environment you're working in, it's difficult to be more precise.
Bye
Okay... did some trial and error and found it had to do with "trusted" documents and for some reason, the enable content was not "enabling" properly. Took off trusted documents and have to click on enable content when it opens and now it works.... however, does anyone have any ideas as to how I could force the file to only refresh specific tabs and not all the tabs? (time saver)... most of the files only need a couple tabs updated while others need all, so if I create a button to give the choice it will save time.
