Forum Discussion
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 the macro name.
I tried a Module with :
Public Function Macroname() As String
End Function
...but that didn't help either.
Can someone please give me a hint as to what I could change to make that work?
Best Regards,
Carsten
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.
15 Replies
- Sub test()
wert = Sheets(1).Range("a1").Value
If wert=1 Then
'DeinMakro
End If
End Sub
Wenn Ihnen diese Informationen weitergeholfen haben, bitte markieren Sie diesem Post als "Richtige Antwort" und auf Like (Hand mit Daumen hoch), somit können auch andere Mitglieder von diese Info später profitieren. Wenn Ihnen die Informationen nicht weitergeholfen haben, bitte ignorieren sie diese Informationen.
Nikolino
I know I don't know anything (Socrates)- Hallo, Nikolino,
das probiere ich auf jeden Fall auch aus, Danke!
Hans hatte das Problem allerdings mit seinem Vorschlag bereits gelöst
Beste Grüße,
Carsten- Habe es im Nachhinein auch gesehen.
Naja ein Lösungsvorschlag mehr kann nicht schaden.
Wenn es mit Hans Vorschlag geklappt hat ist doch Super!
Viel Spaß mit Excel
Nikolino
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 SubSwitch 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.
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
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.
