Me.Rows(Target.Range.row).Delete Eliminates Dropdown

[grnzbra]

I have a worksheet with two dropdown boxes. The first one references a fixed table on a separate sheet. The second, whose choices are set by the selection made in the first dropdown box, is driven by a pivot table. The fixed table consists of the columns MainAssetType, SubAssetType and SubAssetLookUp. The SubAssetLookUp column is =IF([@MainAssetType] =valueSelectedAssetType,UPPER([@SubAssetType])). This results in the SubAssetLookUp will have either a valud from the SubAssetType column or the value "FALSE". The pivot table is set to show the SubAssetLookUp coumn values with "FALSE" filtered out.

When the code

Me.Rows(Target.Range.row).Delete

and I try to add more data, I get this when I get to the second dropdown.

The values in that row are deleted. However, the second dropdown box becomes a normal cell. I have compared this to another similar workbook and it doesn't happen. (I am stepping through the code and as soon as this line is run I stop the code. The cell contents on both sheets disappear for that row, but when I start adding another row and make a selection on the first dropdown and then step into what should be the second dropdown, it is no longer a dropdown (although the contents of the pivot table are correct). On the other worksheet, when I repeat this process, the second dropdown remains.

Is there some worksheet or data validation setting that prevents the dropdown from becoming just a normal cell?
This is the data validation setting for the second dropdown. There are no Input Messages or Error Alerts.

 

[SkipVought]

Hi,

Why are you deleting the row containing your Data > Validation Dropdown Boxes?

I've used this technique many times, going as many as 4 boxes deep.

I've used MS Query, rather than a PT, to generate 1) the master list for the first Dropdown and then upon making a selection, a Worksheet Change Event to trigger the second query based on the first selection and so on.

You don't delete the dropdowns. You clear the link(s) to the list(s) in your VBA.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[grnzbra]

Unfortunately, this is the code that I'm stuck with. I can make minor changes but can't redesign it.
In the past month, I wasted a lot of time trying to find the code that was causing the spreadsheet to "misbehave". In both cases, it turned out to be something about the spreadsheet that was causing the problem. Since this problem occurs instantly on both the good sheet and the bad sheet, I can't help thinking there is something about the spreadsheet or how I set up the drop box that is causing the problem unless there is a line of code ahead of this one that says, "Hey, don't mess with drop boxes that depend on pivot tables!".

Also, when I run this line

EquipmentAssetList.ListRows.Add

on the bad spreadsheet the cell that should be a dropdown isn't , while on the good spreadsheet, the dropdown is part of the newly added line.

This shows what EquipmentAssetList refers to

It is what happens when the 123 (also a dropdown that refers to a cell on another worsheet) is selected and the code is stopped immediately after the "Add" line is run. In the first line, the Sub Asset Type is a dropdown while in the second line it is not. In the good spreadsheet, when this line is run, everything that should be a dropdown, is a dropdown.

 

[SkipVought]

Hey, don't mess with drop boxes that depend on pivot tables!".

I never tried nor even thought about dragging a dropdown filter off a PT to use elsewhere. Hooda thunk? Is that what they did, or is this dropdown truly a Data > Validation List referenced to a PT Field?

How about

Me.Rows(Target.Range.row).[b]ClearContents[/b]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[grnzbra]

The row needs to actually be deleted and the next row moved up along with every row below it including the last row which is blank.
Also, when a row is added, as I explained in my edit to my above response, that particular dropdown is not added, but the Main Asset Type dropdown is added.

 

[SkipVought]

Can you RESORT the table, maybe by adding a dummy column?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[grnzbra]

Sounds good. I'll give it a try.

 

[SkipVought]

grnzbra, how did you solve this issue?

Our member would like to know what you did and how successful your outcome.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!