How to write Worksheet ChangeEvent if cell value is great than one

[Twaddy006]

Hello all

Firstly to point out my VBA knowledge is very limited so apologies if this is an obvious one, I've managed to fudge together the below code which does work, however I have an issue when I attempt to run another macro via a button to clear certain cells.

ChangeEvent2 runs if the value in M13 changes, which works fine until I run the Clear macro, which deletes the entry in M13, which I think then triggers ChangeEvent2 again. Excel appears to get stuck in a loop before crashing

I'm thinking I could change ChangeEvent2 to only run if M13 value is great than 1 so not to be triggered when the Clear macro runs, how would I tweak ChangeEvent2 so that it only triggers if M13 value is greater than 1?

Hopefully I've explained the situation clearly

Many thanks in advance

Twaddy

Please Login or Register  to view this content.

[stnkynts]

Use Application.EnableEvents = False at the beginning of code to prevent worksheet_change event from triggering

Please Login or Register  to view this content.

[PCI]

Stop the EVEN when launching "ChangeEvent2" to avoid to fire "Worksheet_Change" again

Please Login or Register  to view this content.

See also

Please Login or Register  to view this content.

[Twaddy006]

Hi Stnkynts & PCI

Thank you both for your replies, I'm still having problems I'm afraid although it does feel closer

I've changed the code as below which works fine, however when I then record and apply the Clear Macro it does't run properly. I've created the clear Macro clearing all the cells I need however it then impacts the ChangeEvent if I enter figures again and only seems to run half of ChangeEvent2, please see 2nd section of code as to where I think it's failing

Please Login or Register  to view this content.

It seems to run this element ok and copy and pastes as it should

Please Login or Register  to view this content.

However seems to fall down when it tries to copy and paste the 2nd element of the code

Please Login or Register  to view this content.

I've tested the Clear macro without attempting to clear M13 and it works fine, it's just when I include M13 to be cleared that it fails

This is a sample Clear macro I've recorded which does clear the cells but then stops the sheet working correctly if I try and use it again

Please Login or Register  to view this content.

Any further thoughts?

Many thanks

Twaddy

[stnkynts]

Abandon your ChangeEvent1 and ChangeEvent3 from original post. They are pointless. Replace your Worksheet_Change event and Clear macro with the ones below. Does this accomplish what you are trying to do?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Twaddy006]

Thanks for your reply and your suggested coding, I'll give it a go

Why are events 1 and 3 pointless? I want the sheet to move to the next cell to be completed in the form once the user has inputted data, and because of how the sheet is put together the cells don't run in a straight sequence

Thanks

[stnkynts]

"pointless" was the wrong choice of words. I apologize for that. What I should have said is that there are many more efficient ways of doing what you are trying to do. I still don't fully understand your workflow or else I would have coded a way for you to do it.

[Twaddy006]

No problem, no offence taken. I appreciate it's not easy trying to picture what I'm trying to achieve without seeing the sheet and partial coding, and I probably haven't explained myself very well

To give some context to Events 1 and 3, which was originally just one event until I realised I needed to also incorporate event 2, the sheet is essentially a tool for employees to use to record their contract details and select various company benefits to demonstrate the value of their overall salary/reward package. As some cells that need completing run across the sheet, whereas other sections run down the sheet, and because I wanted to ensure all sections are completed to ensure all calculations work, I wanted the sheet to select the next cell in sequence automatically and this was the only way I knew how to achieve that, if you know of a better way then I'd be more than happy to learn and incorporate it.

Thanks again for your help and patience

Twaddy

[stnkynts]

If it was me, I would look at setting up a custom UserForm. A UserForm will allow you to completely customize the data that they need to enter, with instructions, and once all data has been completed it will populate the spreadsheet. If you submit a copy of your workbook with sensitive information removed (try not to damage the integrity of the sheet) I could probably build you one.

I just need to know what cells are required to be filled and what data needs to go where.

[Twaddy006]

Hi stnkynts
That's very kind of you to offer, and in hindsight a Userform would probably have suited my needs better. I have built one before and may look to build one if I do a 2nd version of the sheet, however I need to launch it at work on Friday so time is against me
Thanks again for all your help
Cheers
Greg