Forum Discussion

Copper Contributor

Mar 04, 2021

Solved

I want a macro to run in Excel 365 when a cell returns a certain value

Example: =WENN(E3=1;Macroname();"") so when E3=1 the macro with the name "Macroname()" should run. This isn't accepted, an error-message of "wrong syntax" is displayed, highlighting the "()" in th...

Macros and VBA

HansVogelaar
Mar 05, 2021
Carsten0764

In one of my previous replies, I wrote

"Make sure that TestMakro1 is a function, not a procedure (sub) and that it is located in a standard module in the workbook with the formula."

The function called in your formula is MakrotestStart. This function is located in the worksheet module of Tabelle1. That is precisely the problem! It should be in the standard module Modul1.

See the attached version.

Test1forHans.xlsm16 KB

HansVogelaar

MVP

Mar 04, 2021

Carsten0764

A function in a cell formula can only return a value in its cell; with a few exceptions it cannot run code that manipulates other cells.

If the user will enter a value in E3, you can use the Worksheet_Change event procedure:

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("E3"), Target) Is Nothing Then
        If Range("E3").Value = 1 Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Call MacroName
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End If
End Sub

Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.

If cell E3 contains a formula, it would be best to use code like above, but to check the cell or cells that contribute to the result in E3.

Carsten0764

Copper Contributor

Mar 04, 2021

HansVogelaar

Hello, Hans,

Thanks for the answer, but this is not what I mean.

Lets say: If the value E3 changes from 0 to 1 I want the Makro Testmakro1() to run.

So in, for example E4, I enter the following formula: =WENN(E3=1;Testmakro1();"")

In all the forums that I looked into this formula was described like that and I remember I myself used it in an older Excel-Version.

Now I changed to Excel 365, and suddenly the Syntax is rejected, marking the double bracket of the Makroname as wrong.

I can program a Function like Function1() and enter that, and still the syntax is rejected as wrong - but all Functions, Makros etc. automatically get that () at the end of their names.

Has there been a change for that syntax in Excel 365?

Best Regards,

Carsten

HansVogelaar
MVP
Mar 04, 2021
Carsten0764

Make sure that TestMakro1 is a function, not a procedure (sub) and that it is located in a standard module in the workbook with the formula.
- Carsten0764
  Copper Contributor
  Mar 04, 2021
  Yes, I do, but even the function name automatically gets the "()" at the end, and that is refused with a syntax error, I simply cannot enter it in the WENN Formula. It's not that the function won't execute - at least I can't test that, because the WENN-formula steadfastly refuses the function name.
  BR,C.
  - HansVogelaar
    MVP
    Mar 04, 2021
    Carsten0764
    
    That is strange - it works for me.
    
    Could you attach a small sample workbook without sensitive information that demonstrates the problem?