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?

[rowyn]

1. Nope. Cell contents are one of six possible plain text values (including blank).
2. Nope.

[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.

[rowyn]

*deletes all the worksheet code*
*adds new code that literally does nothing but "MsgBox ("Here I Am") when Target.Address = I451

Huh. It pops up "Here I Am" whenever I enter text, but if I use the delete key to blank the cell, nothing happens. Maybe it's a version issue? What version are you on? I'm using Excel 2013 for Windows.

[terrycloth.livejournal.com]

That's the version I was using, and almost the exact same methodology (I skipped the Target.Address check and had it put up a message box unconditionally, but that seems like a weird part of it to fail).

[rowyn]

O_o

I'll try it again tomorrow, both with unconditional message box and on a brand-new workbook. Maybe this relic has gotten corrupted in some weird way.

[terrycloth.livejournal.com]

Target.Address = "$I$451"

But if I used =I451 it didn't work even for entering text, so presumably you were just paraphrasing. Huh.

[rowyn]

Yeah, I used "I451" and not I451 . Or with the $ signs. I got nothin' on why my results are so weird. :/

[rowyn]

Okay, I figured out the key difference between my actual spreadsheet and default blank one.

Cell I451 is merged through N451 on this spreadsheet, and the whole is labeled "rngcoll1". For purposes of VBA code, Target.Address is $I$451 or I451 when you enter a value in the merged cell. If you use the delete key to clear the cell, Target.address is $I$451:$N$451 or rngcoll1. A set of events triggered by changes to the first address won't trigger if it's the second, and vice versa.

So it kinda makes sense, but it's still annoying. :/

[terrycloth.livejournal.com]

Of course. Text is only entered in the first cell of a merged cell, but the selection that a command like 'clear cells' operates over is expanded to include the entire merged cell.

[rowyn]

Okay, I figured out the key difference between my actual spreadsheet and default blank one.

Cell I451 is merged through N451 on this spreadsheet, and the whole is labeled "rngcoll1". For purposes of VBA code, Target.Address is $I$451 or I451 when you enter a value in the merged cell. If you use the delete key to clear the cell, Target.address is $I$451:$N$451 or rngcoll1. A set of events triggered by changes to the first address won't trigger if it's the second, and vice versa.

So it kinda makes sense, but it's still annoying. :/

[rowyn]

Okay, I figured out the key difference between my actual spreadsheet and default blank one.

Cell I451 is merged through N451 on this spreadsheet, and the whole is labeled "rngcoll1". For purposes of VBA code, Target.Address is $I$451 or I451 when you enter a value in the merged cell. If you use the delete key to clear the cell, Target.address is $I$451:$N$451 or rngcoll1. A set of events triggered by changes to the first address won't trigger if it's the second, and vice versa.

So it kinda makes sense, but it's still annoying. :/

[rowyn]

Okay, I figured out the key difference between my actual spreadsheet and default blank one.

Cell I451 is merged through N451 on this spreadsheet, and the whole is labeled "rngcoll1". For purposes of VBA code, Target.Address is $I$451 or I451 when you enter a value in the merged cell. If you use the delete key to clear the cell, Target.address is $I$451:$N$451 or rngcoll1. A set of events triggered by changes to the first address won't trigger if it's the second, and vice versa.

So it kinda makes sense, but it's still annoying. :/

[rowyn]

Okay, I figured out the key difference between my actual spreadsheet and default blank one.

Cell I451 is merged through N451 on this spreadsheet, and the whole is labeled "rngcoll1". For purposes of VBA code, Target.Address is $I$451 or I451 when you enter a value in the merged cell. If you use the delete key to clear the cell, Target.address is $I$451:$N$451 or rngcoll1. A set of events triggered by changes to the first address won't trigger if it's the second, and vice versa.

So it kinda makes sense, but it's still annoying. :/