Cell value change to 0 doesn't cause the event to occur

[cherkey]

Hello,

I've been searching the forum to get some clues but wasn't successful.

I'm trying to run a bit of code that will trigger an inputbox when the value of a certain cell is changed to "0". The inputbox entry will be placed in a different cell. Been trying this:

Please Login or Register  to view this content.

Nothing occurs when the cell "C5" changes to "0". I feel as if I'm missing something simple here and I'd appreciate any guidance.

TIA
cherkey

[xlnitwit]

Hi,

That is not a valid event declaration. If you only need to monitor one sheet, you should put the code into the worksheet code module and it should look like this

Please Login or Register  to view this content.

That will work as long as the cell change is not the result of a calculation.

[davesexcel]

Like xlnitwit says the belongs in the worksheet module, right click the sheet tab and select view code, paste the code there.

Please Login or Register  to view this content.

[cherkey]

Hello,

Thanks for the replies. Yes, there is a calculation that causes the C5 cell to become "0". That would be a Worksheet_calculate then?

Also, when I run this code, the macro run window pops up, but I have no macro to run...I just need to code to execute... so I assume I'm still missing something... (?)

cherkey

[davesexcel]

The first line in my previous post explains where to put the code, it is a worksheet change event so if the sheet changes the code activates.

Worksheet change event does not work if it is just a formula that changes.

Check out another thread for more information

https://www.excelforum.com/tips-and-...et-events.html

[cherkey]

Thank you for your assistance, and the spreadsheet example. I was on the right track but I must have set something wacky in the file. I copied over to a new file and 'started over'. All is working now as I would have expected.

Regards,
cherkey

[cherkey]

However, I spoke to soon :-\

Yes, this occurs on a calculation so I do have this code working:

Please Login or Register  to view this content.

(The Select statement is used so I can cancel out of the loop that occurs, as I test this)

But what happens now, is that it now always sees "C5" is being "0" so it just keeps looping the Inputbox. How can I get this only to react to the first time is becomes "0"?

[xladept]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[cherkey]

Hello,

I've attached the file... a use description is within (pretty basic).

Thank you for your time.

cherkey

[Norie]

Use a change event and check if C4 or E7 have changed.

Please Login or Register  to view this content.

[cherkey]

Hello,

Thank you. I tried and this doesn't work. As I understand it, a change event will not work because there's a formula that changes the cell contents.

cherkey

[davesexcel]

Norie's assumption is correct, if you are changing the values in C4 or E4 then the worksheet change event will work.

Will you be entering values in column E or just E4?

[cherkey]

Hello again,

OK, I think I now understand what this is doing now and it seems to be behaving (except the Inputbox pops up twice but I'll deal with it). Yes, the table will grow along with column E so I just changed the first line to:

Please Login or Register  to view this content.

Thanks again,
cherkey