Excel VBA wheeeee x_x

[rowyn]

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?

Comments

[vik_thor]

(low level Excel user here…)
Are there conditional items in the cell that are getting deleted?
Or is the cell this is getting entered in a blank one, other than whatever is entered?

After deleting the contents of the cell, does changing the active cell trigger the actions you're looking for?

[terrycloth.livejournal.com]

Private Sub Worksheet_Change(ByVal Target As Range) triggers on delete when I try it. You'd have to check that Target.Address was equal to the location of the picklist.