Run Macro when Workbook changes

[vio.coman]

Hi guys,

I need a macro that should run other macros when something changes in Workbook ("Input"). Range (B6:EU37).
However, the macro should not be a Private Sub.

Is this possible!? if yes, I really appreciate your help!

Thanks

[Andy Pope]

The macro code does not need to be private.

The event code might be though. Can the range B6:EU37 be on any sheet in the Workbook Input?

[vio.coman]

Hi,

thanks for your reply.
Sorry, I meant Worksheet, not Workbook.
So, when changes occur in Worksheet ("Input") within the specified range, the macro should call Macro_2 for example...

Cheers

[Andy Pope]

So right click sheet tab and View Code.

Please Login or Register  to view this content.

Macro_2 code can be in a standard code module.

[vio.coman]

Thanks!

Sadly it does not work. I guess because of the Option Explicit on the top, which I need for further macros in the code.
That's why I thought it must be the Private Sub that does not go with the Option Explicit.

Please see my code below. When trying to run the code, the private sub does not appear in the dialog box and therefore cannot be run.

Please Login or Register  to view this content.

cheers

[Andy Pope]

Nothing to do with option explicit.

If you placed Macro_2 in the worksheet object (same place as the event code) then it should appear in the Macro dialog as Input.Macro_2

But as I said you can place Macro_2's code in a standard code module.

[vio.coman]

I took 1:1 the code you provided me with.

Should I have done something more? If yes could you please enter the changes in the code you gave me?

Right now, I do changes in Worksheet ("Input"), without that the macro calls macro_2 and runs the rest of the code.

As mentioned, the dialog box does not list the private sub in the macros list...please see attachment

[Andy Pope]

Can not really tell from screen shot.

In the attached example Macro_2 code is in a standard code module, which is separate from Input sheet code.

[vio.coman]

Obviously I am doing something wrong, because it does not seem to work...

In the attached Excel File you can see the entire code and macros.
As mentioned before, I need that the code is being run starting with macro_2 whenever I do changes in Sheet "Input"...

Please have a look, thanks!

[Andy Pope]

You now have the event code in a standard module.

Move this code from Modul1 to Input sheet code module.

Please Login or Register  to view this content.

[vio.coman]

Unfortunately I do not know how to do that....my VBA knowledge is rather poor, but thanks again!

[Andy Pope]

I told you how to do it in post #4.

So first select and cut the code.
Go to the workbook and right click the Input sheet tab. Select 'View Code'.
Paste code.

[vio.coman]

I worked like a charm. Many thanks!

One more short question: is there a way to prevent the macro from shifting the view of the user from the Sheet "Input" to Sheet "Daten" every time I change something in Sheet "Input"???

In this particular case, it would be great if the user would be able to stay in Sheet "Input" and continue changing things. Otherwise, one has to return to Sheet "Input" after each change made...

[Andy Pope]

Simplest way, without affecting how you existing code runs.

Please Login or Register  to view this content.

Add line to active sheet when you other code has finished.

[vio.coman]

Thanks a million! Worked out perfectly!