dynamic dropdown list, VBA

[Stallnig]

Hi Folks

I have created a cell with a dynamic dropdown-list via data validation, which changes its range, depending on the value in another static dropdown-list.(works fine)

Appearently it is a common problem, that the cell keeps the last value that was selected from the previous range. So I have to manually select the dropdown and select one of the now available values.
This seems to happen because the change of range happens due to mouse-selection and not via direct input. A humongous paperchase on goolge concluded, that I'd have to use some VBA to bypass this.

So what I basically want is for the dynamic-list-cell to be cleared if the Value of the static list changes.
How I thought to achieve this:

Cell1 = Dropdown list(static) [Value1, Value2, Value3]
Cell2 = Dropdown list(dynamic)
Cell3 = Variable Value

Pseudo code:
If Cell3 unequal to Cell1 then
1. clear Cell2;
2. Copy Value of Cell1 into Cell3 //So the condition is no longer met until Cell1 is changed
else do nothing

I'm absolutely new to VBA but I tried this:

Please Login or Register  to view this content.

The debugger marks the second row yellow.

Can someone make this work please?

[TMS]

The code looks OK but you don't need ".Select ... Selection."

Please Login or Register  to view this content.

However, you would be better off with a Worksheet Change event handler monitoring cell A1. When A1 changes, clear cell A2, etc.

For more specific help, please upload a sample workbook.


Regards, TMS

[Stallnig]

Hello

Thank you for the fast reply.
I made an example sheet, but I don't know how to put it in here so you can download it.

What I also don't know is how the VBA script runs. Isn't it a continuous loop?
Also I wonder, if my PC and office programs are set to german-language and all the function-names in excel are german, would the VBA-script also require german expressions?

[TMS]

A Worksheet Change event handler, if present, runs continuously in the background and fires whenever a change is made on the monitored sheet. It is normal practice to restrict the cells monitored to just those "of interest"; in this case, probably cell A1.


Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Stallnig]

OK, I figured out that Worksheet_Calculate only triggers when an actual calculation is processed.
The script then works perfectly fine.
So the Worksheet_-Funktion is a condition under what the code underneath is being triggered?
Therefore your suggestion of Worksheet_change would mean that if anything in the worksheet changes, the code would be triggered, right?
With this assumption I changed the code to this:

Please Login or Register  to view this content.

If I now change Cell1, I get:
Runtime error '-2147417848 (80010108)'
The method 'ClearContents' failed for Object 'Range'.

(For me it is actually written in german)

[Stallnig]

Ok here's the example sheet. It follows the explaination in my first post.
Like I said with Worksheet_Calculate it would work fine.
I don't know if the language matters.

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[Stallnig]

Wonderful! Thank you very much.
I just hope I can reproduce this for my real sheet.

[TMS]

You're welcome.


If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Stallnig]

Thanks for the advice.