Automatic Data Validation on Horizontal row which has filtered data from another row

[Hein Htut Oo]

Hi, I am completely new to VBA, and I have been looking for a solution for data validation list that changes according to the selected entry whenever the source is changed. I have come across a website which i am not allowed to post but have specified in the attachment below where she has provided a solution. However, the code is for when the source is changed manually, and only when there are no blank cells between entries in the source. So I thought I would be able to override this by filtering my source for blanks and putting it in another row and changing the code to view it as the new source. However, doing so doesn't work because the code doesn't register changes due to formula (because of Worksheet_change usage maybe?) I tried using Worksheet_calculate but Target returns empty and having no idea how the code works, I'm pretty much stuck. Could anyone help please? Also, I'm getting a warning whenever I try to save the sheet saying that a compatibility problem might be in place due to a spill or possible future spill?

Edit(The original excel guru for the website is no longer providing vba support)

[Keebellah]

Data validation applies to that cell and I honestly do not understand what you want to achieve.
Both you validations point to the row 7 in F
What happend if you point it to row 8?

[jindon]

Hein Htut Oo,

See if this is how you wanted.

Please Login or Register  to view this content.

[Hein Htut Oo]

Data validation applies to that cell and I honestly do not understand what you want to achieve.
Both you validations point to the row 7 in F
What happend if you point it to row 8?

Sorry, Im a bit bad at expressing what I want. The current code works well for row 7, from F to I. whenever i change F7 or I7, which are chosen in b3 and b4, both the value in f7 and b3, and i7 and b4 will change accordingly. B3 and B4 does not change accordingly if I point it to row 8, from F to I, with the current code. I think it is because the code only registers manual data entry change in row 7 as opposed to the change according to the filter formula in row 8. Sorry again, I hope I made it clear now :X

[Hein Htut Oo]

Hein Htut Oo,

See if this is how you wanted.

Please Login or Register  to view this content.

Oh, that looks promising! Cant wait to try it out when I have access to my computer in about 7 hours! Thank you in advance, cheers sir

[Keebellah]

I hope @Jindon's code works for you, as goes to the explanation, sorry, still don't get it

[Hein Htut Oo]

Unfortunately, it didn't, but I think we're getting closer!

1.Source for Data Validation List is in F7:O7 (1,2,3,4,5,6,7,8,9,10)
2. Data Entry range is in B3:B12 (1,2,3,4,5,6,7,8,9,10)
3. Let's assume B3 has selected F7 as its entry, B4 selected F8, and so on.

The Problem before the worksheet solution by Ashish Mathur was that whenever I changed the source F7 from 1 to anything, the data entry range that corresponds to F7 which is B3, does not change accordingly.
After the worksheet solution, I can now change F7 from 1 to anything, and B3 would automatically change to that value.

However, there is one minor issue. The code only works up till a blank cell appears in between the source range. For example, if the blank is in I7, the corresponding data entry ranges do not change accordingly when any of the values in J7, K7, L7, M7, N7, and O7 are changed. The code still works for cells before I7, but I would like to have the whole range covered.

As a workaround, I thought filtering the data by excluding blanks in row 12 and changing the source range in the code might work, which means that the new source range would automatically update whenever I change the old source values, but the code does not work if the new source range used in the code is not manually changed(in this case the new source value is changed due to filter formula).

What I would like is for the code to be able to detect changes in the filtered source range(F12:O12) which changes according to my manual editing of old source range (F7:O7), and then transfer those changes to corresponding validation entries in data entry range B3:B12.

A minor issue also when trying this new test sheet, changing F7 from 1 to something would also transfer that change to B12 because it has selected O7 and has a 1 in its "10". Minor issue but would be neat to have this resolved too!
Any help would be appreciated :3

[jindon]

I took F7:I7 from the data validation formula in your original post.
Change to

Please Login or Register  to view this content.

[Keebellah]

One thing I still don't get, when you have a data validation list it works for all the cells that use the same validation.
Personally I do not see why if you change B3 the B$ must be the next one.
That I still don't understand and is not logic for me.
You probably need it that way and that won't work with just Datavalidation

[Hein Htut Oo]

Thank you for such a fast response! However, I have to say that the code still does not work when a filtered row is put in place of F7 to I7. Also, it took about 7 to 8 seconds longer for the code to execute whenever I changed a value compared to your previous code.

I think there is some misunderstanding still, forgive me if I'm wrong sir. The code works fine when F7 is a manually entered value, and all changes to it are transferred to B3. However, when F7 is a filtered array referencing to F12, any change I make to F12 is transferred to F7 due to the formula reference, but the code is not transferring the change in F7 to B3(not detecting it as change).

I think the issue is with Worksheet_Change(By Val Target as Range) only registering when manual input is entered.
In another example, I wanted to change the name of a sheet according to the variable name of another sheet and add the following phrase "used" after it. An Example is attached below where I have successfully managed to alter the code to use Worksheet_Calculate instead of Worksheet Change. I tried implementing Worksheet_Calculate in my original post but got stuck when Target returns as empty.

[jindon]

Did you want to use row 12 for the validation source range?

Please Login or Register  to view this content.

[Hein Htut Oo]

The code works perfectly! and executes at less than half a second sir! If I could trouble you once more, I just realized that the data validation range from b3 to b12 was supposed to be horizontal along row 3 from range CD3:EE3, with blank cells in between as well as other cells with data validation cells with a different source list. And uh, if I wanted to change F7:XX7 range to E3:XX3, I could just change all instances of F7 in the code to E3 right? Does the same apply for starting from F12 to starting from EN7? Would the formula I need be something like this?

Please Login or Register  to view this content.

[jindon]

Assuming the Filter formula in EN7 is correctly working.

Please Login or Register  to view this content.

[Hein Htut Oo]

Got a runtime error 1004: Application defined or user defined error? the filter function in BN7 is as follows:

Please Login or Register  to view this content.

and it is working correctly horizontally as expected. Could defining the set rng range to CD3:EE3 solve the issue?

[jindon]

That line is within the error trap, so I don't know why you are getting such error.

[Hein Htut Oo]

I tried moving the data validation range to the same rows in the example and got the same error, application defined or object-defined error, I think we have to limit RNG to just CD3:EE3 to avoid overlap with other data?

[jindon]

I said I don't know.

How can I know the error in workbook that I didn't see?

Upload your workbook.

[Hein Htut Oo]

Please Login or Register  to view this content.

This is the line of the code that got yellowed, I tried hovering above it but it wouldn't give any value.

[Hein Htut Oo]

Sorry! uploading in a second!

[Hein Htut Oo]

Here it is! Sorry it took a while

[jindon]

OMG,

Why are you applying data validation to F3:O3? What is the need for that?
And this workbook is not setting like you explained in your post #12.

What is the real problem?
I'm shutting down my pc, so next response will be sometime tomorrow.

[Hein Htut Oo]

I tested prior and data validation to f3:o3 works fine prior to adjusting rng to rows(3), but it's not necessary at the moment so I disabled it again. I sincerely apologize for troubling you to change the code at the last moment due to my lack of foresight in having to have the data validation selection range horizontal instead of vertical like I asked initially, and on the same row as Manual Data Entry Range. So, B3:B12 becomes CD3:EE3, and F7:F12 becomes E3:BV3, I think it is them both being on the same row that is causing the problem? Or the blank cells and cells with other data between CD3 and EE3? Please take your time and enjoy tomorrow!

[Keebellah]

As for me, I'm unfollowing this post.
It's a waste of precious time,

[jindon]

Change to

Please Login or Register  to view this content.

[Hein Htut Oo]

The code is working nice! I moved it to the sheet I wanted to apply and changed some code to fit. However, the code takes about 5 seconds to execute, and I was wondering if the execution time could be shortened by defining manual data entry range from E3:BV3, and data selection range from CD3:EE3? Attached below is worksheet and the data selection range from cd3:ee3 consists of "Combo"s of 2 or 3 selections from manual data entry range E3:BV3. The code is working correctly but only slightly taking a little longer than desired. This was my first time asking help in any forum so please forgive me for any inconvenience caused. The next time I ask for any help in any forum, I would do well to mention specifically where I want the solution applied and attach both application sheet and a simpler sample sheet to work code on. If you could shorten the code execution time in the application sheet to about 1 second or so, that would be neat!

[jindon]

Despite of unrealistic/unconstructive data layouts, it took only a couple of seconds, so no idea.