Auto Insert Data Validation Based On Cell Data

DKTRL

Board Regular
Joined
Jul 21, 2013
Messages
153
Hi,

I have create a Order Form and need some help. this order form is for customer to choose the items that they are going to buy. and for certain items we giving them 1 or 2 free gift but not for all.

MySheets1 ("A19") is for choosing the products "Data Validation List"

What i want is that, base on difference products that the customer select in ("A19") i want Cell A25 or A25 & A26 to add data validation list from Sheets2

if Sheets1 ("A19") select = "Products 1" then Sheets1 ("A25") automatically add data validation list from sheets2 A1:A20

if Sheets1 ("A19") select = "Products 2" then Sheets1 ("A25") & ("A26") automatically add data validation list from sheets2 B1:B20 & C1:C20

if Sheets1 ("A19") select = "Products 3" then Sheets1 ("A25") No data validation added

if Sheets1 ("A19") select = "" then Sheets1 ("A25") No data validation added



Any Help Will Be Appreciate.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Place this code in the worksheet code module.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A19")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Products 1"
            With Range("A25")
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=sheet2!$A$1:$A$20"
                End With
            End With
        Case "Products 2"
            With Range("A25")
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=sheet2!$B$1:$B$20"
                End With
            End With
            With Range("A26")
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=sheet2!$C$1:$C$20"
                End With
            End With
        Case "Products 3", ""
            With Range("A25:A26")
                With .Validation
                    .Delete
                End With
            End With
    End Select
End Sub
 
Upvote 0
Dear mumps

Wish you a very Happy New Year 2014. and many thanks for your help, the code that you have provide it work well and really help me a lot. but now i have another question and need your help.

i need to know how can i modify it for multiple select. cause i have tried to modify it but it is not work.

what i need is, For example,

1) if ("A19").select <> "Please Select Here" or <> ""

then i want ("A20") to add data validation

2) if ("A20").select <> "Please Select Here" or <> ""

then i want ("A21") to add data validation and ("A19") enable=lock

3) if ("A21").select <> "Please Select Here" or <> ""

then i want ("A19") and ("A20") enable=lock

Thanks in advance !


Place this code in the worksheet code module.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A19")) Is Nothing Then Exit Sub
    Select Case Target.Value
        Case "Products 1"
            With Range("A25")
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=sheet2!$A$1:$A$20"
                End With
            End With
        Case "Products 2"
            With Range("A25")
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=sheet2!$B$1:$B$20"
                End With
            End With
            With Range("A26")
                With .Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=sheet2!$C$1:$C$20"
                End With
            End With
        Case "Products 3", ""
            With Range("A25:A26")
                With .Validation
                    .Delete
                End With
            End With
    End Select
End Sub
 
Upvote 0
I need a little clarification. You've changed the cells where you want data validation from A25:A26 to A20:A21. Is this correct? Are you still using Products 1, Products 2 and Products 3 ? What are all the items in the dropdown list in A19? Are the data validation lists references still on Sheet 2 in ranges A1:C20?
 
Upvote 0
Dear mumps

Thanks for your fast respond. everything is still the same cell, the only difference is that, the first example i have quoted for products selection i am using only A19. but i am trying to expand it from A19 to A21 for more option.

For example, if my customer he has make products 2 selected in A19, than he is entitle to have 2 free gift. that is why A25 & A26 is automatically add data validation list when products 2 is selected.

So right now what i want is that, If ("A19") is selected, than i want the data validation of A19 to expand another cell in A20 for the select of Products 4 to products 6.
And if A20 also has been selected, than i want A19 to be lock and the data validation of A20 to continual expand to another cell in A21 for further selection of Products 7 to products 9.

***So far i have no issue to expand A19 to A20, but from A20 expand to A21 it seem like the code is cannot work.***


I need a little clarification. You've changed the cells where you want data validation from A25:A26 to A20:A21. Is this correct? Are you still using Products 1, Products 2 and Products 3 ? What are all the items in the dropdown list in A19? Are the data validation lists references still on Sheet 2 in ranges A1:C20?
 
Upvote 0
By the way, i have discover another error, if i use the delete key to delete A19, VBA show Run-Time error 13 Type mismatch. may i know how to avoid this error.


Thanks
 
Upvote 0
I'm still a little confused. Here is what I need to know: What are all the items in the drop down list in A19? I need a detailed description of exactly what you want to happen when you choose each item. For example, when the first item is chosen, what needs to happen? ... when the second item is chosen, what needs to happen? and so on for all the items in the list in A19. Then I would need the same description for all the items in A20 and A21. Without this information, I will not be able to complete the code. Let's start with this information and we'll worry about the error later. By the way, when I used the delete key to delete A19, I didn't get any error.
 
Upvote 0
I'm still a little confused. Here is what I need to know: What are all the items in the drop down list in A19? I need a detailed description of exactly what you want to happen when you choose each item. For example, when the first item is chosen, what needs to happen? ... when the second item is chosen, what needs to happen? and so on for all the items in the list in A19. Then I would need the same description for all the items in A20 and A21. Without this information, I will not be able to complete the code. Let's start with this information and we'll worry about the error later. By the way, when I used the delete key to delete A19, I didn't get any error.



Dear mumps, sorry to confused you.

Please allowed me to explain. for this order form, the idea here is that we let the customer to select the products and forward back to us for the process of sales. but for some promotion items that the customer selected, we are giving them free gift. but not for all. some items we are giving 1 free gift and some we are giving 2.

How it work

1st Stage
1) The customer has to select an items in the list. Here we refer to A19 "In total there are about 20 difference items"
2) If the selected items is a promotion item with 1 free gift, then A25 will add a "Data Validations List" for customer to choose.
3) If the selected items is a promotion item with 2 free gift then A25 and A26 will add "Data Validations List" for customer to choose for 2 free gift item.

2nd Stage
1) If customer has completed 1st stage selection in A19, the order form will than automatically add another selection list in A20 "CLICK HERE FOR SELECT" for customer to select.
2) If customer has do he selection in A20, then i want A19 to be Locked to prevent further change. cause all the information in this order form will be transfer to a Userform for further detail add. for example shipping cost. etc. than Userform will update data to the sales directory workbook. if the products selection is not in sequence. if customer after select products in A20 and than delete data in A19 than there will be a error.
3) If the selected items is a promotion item with 1 free gift then A27 will add a "Data Validations List" for customer to choose.
4) If the selected items is a promotion item with 2 free gift then A27 and A28 will add "Data Validations List" for customer to choose for 2 free gift item.

3th Stage (Final Stage)

1) If customer has completed 2nd stage selection in A20, the order form will than automatically add another selection list in A21 "CLICK HERE FOR SELECT" for customer to select.
2) If customer has do he selection in A21, then i want A20 to be Locked to prevent further change.
3) If the selected items is a promotion item with 1 free gift then A27 will add a "Data Validations List" for customer to choose.
4) If the selected items is a promotion item with 2 free gift then A27 and A28 will add "Data Validations List" for customer to choose for 2 free gift item.


Share The Same Data Validation List.
1) A20 & A21 = A19
2) A27 & A29 = A25
3) A28 & A30 = A26


Remark:
If customer try to key in any word into A19, A20,or A21, a MsgBox will prompt "Please select items from the drop-down list"


Hope the above explanation will help you to understand.


Thanks
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top