Implementing Triggers in Excel

A reader recently asked if it's possible to run SAP GUI scripts by clicking a cell to access detailed information in SAP. The answer is yes! In this article, we'll walk you through the process of implementing this functionality.

Before You Begin

Make sure your workbook is saved as a macro-enabled workbook. If not, you may lose your VBA scripts when you close the workbook.

Setting Up the Trigger

To trigger a worksheet event, follow these steps:

Open the VBA editor via the Development tab or using Alt-F11 shortcut.

Double-click on the worksheet you want to monitor.

Paste the following code into the blank editor, replacing the cell range as needed. In this example, we'll monitor range A1:A10. Feel free to adjust this range to fit your needs.

Private Sub Worksheet_Worksheet_SelectionChange(ByVal Target As Range)
	Dim clickedCell As String 
	clickedCell = Target.Address 
	If Not Intersect(Target, Range("A1:A10")) Is Nothing 
	Then 
		Call MySAPScript(clickedCell) 
	End If 
End Sub

Now, let's create a simple test to ensure our trigger is working as expected. We'll create a script that pops up a message box showing the clicked cell and its value.

Sub MySAPScript(cellAddress As String)
    MsgBox "You clicked cell " & cellAddress & " with value " & Range(cellAddress).Value
End Sub

Let's try it out. Double-click any cell in the target range to test the trigger. If the message box appears, you're good to go!

Now, replace the MsgBox line with your SAP script. For example, you can use the MM03 transaction code to display the material details you clicked on in SAP.

Sub MySAPScript(cellAddress As String)
    Dim SapGuiAuto As Object
    Dim objGUI As Object
    Dim objConn As Object
    Dim session As Object
    Dim windowTitle As String
    
    Set SapGuiAuto = GetObject("SAPGUI")
    Set objGUI = SapGuiAuto.GetScriptingEngine
    Set objConn = objGUI.Children(0)
    Set session = objConn.Children(0)
    windowTitle = session.ActiveWindow.Text
    AppActivate windowTitle
    
    session.findById("wnd[0]/tbar[0]/okcd").Text = "/nmm03"
    session.findById("wnd[0]").sendVKey (0)
    session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").Text = CStr(Range(cellAddress).Value)
    session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 4
    session.findById("wnd[0]").sendVKey (0)
    session.findById("wnd[1]/usr/tblSAPLMGMMTC_VIEW").getAbsoluteRow(0).Selected = vbTrue
    session.findById("wnd[1]/tbar[0]/btn[0]").press
End Sub

Once the script detects a click in the target range, it will bring SAP into focus and execute the script.

And that's it. You've just learned how to set up worksheet triggers and pass values into your scripts. Now you can navigate to tcodes and explore data dynamically! 😎

Questions or Feedback?

Feel free to reach out to me via email. Your questions and feedback keep me going, so don't hesitate to ask! Looking forward to hearing from you.