Clear cell contents if a cell is =0

[Rocky2013]

Hello,
Need a real time VBA on this....

if A1=0, then clear contents in B1:B5

Also I need a seperate one for ...
if A1=0, then clear content in B1

Thanks

[alansidman]

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Rocky2013]

Hi,
For some reason, it does not work for me. I use scroll bar to drag the value up and down. When A1=0,
I want the values in B1:B5 to be deleted.

[Trebor76]

You need to put Alan's macro in a standard module and assign the macro to the spinner object (Spinner 2) you have in cell A1 by right-clicking it and then clicking Assign Macro from the short-cut menu.

[Rocky2013]

Hi Trebor76,
I put marco in sheet1, now I deleted marco and copied to Module,
I already assign marco to spinner object. It still does not work.

I preset A1 to 3, then I start to click the down arrow of spinner,
until it shows 0, values but B1:B5 do not get deleted.

Did I do something wrong?

[alansidman]

It appears that you are looking for worksheet change event. The following will do that if you change the spinner to a manual entry in Cell A1. Worksheet change events do not work with spinners or data validation type of events. There needs to be a physical change.

Please Login or Register  to view this content.

[AlphaFrog]

Hi Trebor76,
I put marco in sheet1, now I deleted marco and copied to Module,
I already assign marco to spinner object. It still does not work.

I preset A1 to 3, then I start to click the down arrow of spinner,
until it shows 0, values but B1:B5 do not get deleted.

Did I do something wrong?

You need to reassign the Spinner's macro to Rocky1; the same macro you copied to module 1. After that it works fine.

[Rocky2013]

Hi AlphaFrog,
Thanks it works. I did try that it does not work for me. I must did
something wrong.

I like to extend my request, I need an undo to recover the numbers that were deleted.

Thanks

[Rocky2013]

It appears that you are looking for worksheet change event. The following will do that if you change the spinner to a manual entry in Cell A1. Worksheet change events do not work with spinners or data validation type of events. There needs to be a physical change.

Please Login or Register  to view this content.

Hi alansidman,
I manual type in 0, values deleted from B1:B5. But there is a "Run-time error '13': Type mismatch" popup message.
Also looking an undo.

Thanks

[alansidman]

try this modified code

Please Login or Register  to view this content.

In order to have an undo when using a macro/vba, you will need to store the deleted values somewhere and then copy and paste them back. Once you delete something with a VBA method, there is no undo. So now that you know, where would you like to store the values that are currently in B1:B5