2 quick Worksheet_Change questions

[London_Calling]

Morning, these are pretty simple I imagine just can't find the solutions;

1. can I move through Worksheet_Change step-by-step like you can with normal macros, or indeed the events sheet when you use Worksheet_Calculate, and

2. Sometimes, after I've worked on my code, Worksheet_Change stops working and the only thing I can do is close the workbook and start again. I've played around with Break, Reset and Compile but to no avail . . .any suggestions?

Cheers.

[osknows]

Hello,

1. can I move through Worksheet_Change step-by-step like you can with normal macros, or indeed the events sheet when you use Worksheet_Calculate

In the VBA editor, put your cursor on the line with 'Private Sub Worksheet_Change(ByVal Target As Range)' and press F9 to insert a breakpoint. Then fire the event by changing something on the worksheet - eg find an empty cell and press DELETE

The code will then stop on the first line and you can cycle through each line using F8. You can put multiple breakpoints almost anywhere within your code

2. Sometimes, after I've worked on my code, Worksheet_Change stops working
When you have stopped your code mid flow you have probably turned off events using 'Application.EnableEvents = False'. Unless you turn this back on Excel will no longer trigger events. One solution is to create a temporary sub to run eg.

Sub resetevents()
Application.EnableEvents = True
End Sub

[London_Calling]

osknows - use a break point; of course!

I'll try the code the next time it happens though it sounds like the right idea.

Also wanted to thank you for other code you're put up in the forum which I've been looking at the last few days. You're very generous with your time you've been a considerable help to me gaining knowledge, so thank you very much for all that, as well as your help here.

Cheers!

[Captain Sensible]

Also wanted to thank you for other code you're put up in the forum which I've been looking at the last few days. You're very generous with your time you've been a considerable help to me gaining knowledge, so thank you very much for all that, as well as your help here.

Cheers!

I'll second that, it's always good there's quite a few VB experts on here willing to share their knowledge.

[danjuma]

Also wanted to thank you for other code you're put up in the forum which I've been looking at the last few days. You're very generous with your time you've been a considerable help to me gaining knowledge, so thank you very much for all that, as well as your help here.

Cheers!

I'll second that, it's always good there's quite a few VB experts on here willing to share their knowledge.

Have always mentioned this. A BIG BIG thanks to osknows, as well as all the other helpful forum members here, including you CS.

[osknows]

I'm just glad to be part of a great community. This is one of the best forums I've experienced with so many people contributing. Thanks to everyone

[kiint]

If you find that your events aren't firing it is most likely, as already pointed out that this is due to the application.events being false. It is worth noting that you can turn it back on from the immediate window. You can show it from the view menu of the vba editor and I paste go the required line and leave it there. When you want to run it, just place the cursor anywhere on the line and hit enter.

[London_Calling]

I paste go the required line and leave it there. When you want to run it, just place the cursor anywhere on the line and hit enter.

Hi kiint - sorry, I don't quite understand, could you try again!?

Thanks.

[osknows]

Kiint was highlighting the fact you can use the immediate window to run commands without the need to insert them into a module

In the VBA editor click
1. View/Immediate Window
2. Insert into the window 'Application.EnableEvents = True'
3. Put cursor over this line and press ENTER

Everytime your code deactivates events you can use the above method to renable them

[kiint]

Thanks Os for clarifying my drivvle.

I was typing that in via my phone whilst on an (ahem) break in work so didn't get a chance to sense check it.

[London_Calling]

That's a nice option as well.

Thanks both.

[London_Calling]

This option works sometimes, but it looks like I may have something else going on, any other suggestions for what might be knocking out the Worksheet_Change?