Rowyn (rowyn) wrote,

Excel VBA wheeeee x_x

My Google-Fu has failed me. Help me, Social Media, you're my only hope.

I'm working on a ridiculously overcomplicated Excel workbook, with a bajillion triggered events based on the cell contents.

I want the user to be able to hit "delete" on one cell, and have the spreadsheet trigger an event that clears a varied assortment of other cells.

This workbook has existing code that already does this "clear assorted cells" operation whenever the cell is changed to one of the six values on its picklist. The picklist values are assigned from a sequence of cells elsewhere in the workbook. One of those six picklist cells is empty. If you choose the empty value from the picklist, it will trigger the "cell is empty" event. It does this with code that reads either:
* ElseIf IsEmpty(Target) 
* ElseIf Target.Text = ""

But if you hit the delete key, it doesn't trigger the "cell is empty" event. It looks like Excel doesn't consider the deleted cell to be a "Target" the way it does when the user selects an item from the picklist, because deleting the cell won't trigger an event phrased as EITHER "ElseIf IsEmpty(Target)" OR ElseIf IsEmpty(Target) = False, and one of those should be hit if it were triggering the "change happened" code at all. There's code that prevents the user from entering an option that's not on the picklist, but this won't stop the user from using the delete key.

So. Any idea how I get "delete cell contents" to trigger an event?

  • Post a new comment


    default userpic

    Your reply will be screened

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.