[resolved] Validating list in excel

[Siddharth Rout]

Lets say there are two lists. list one has "apple,oranges,grapes" and list two has "in stock,out of stock, delay". i have validated cell A1 and B1 so that user can select items from list 1 and list 2 respectively. The question is if the user selects "apple" in A1 then B1 should display "expected" which of course is not in list 2 and the cell B1 should get locked. however when the user selects "oranges" or "grapes" in A1 then he should be able to select the items from list 2 in B1 and the cell should not get locked. I hope I make sense....

Can anyone help me?

[Webtest]

How many different 'exceptions' are there going to be? You have one now ... will there be more? Or will it be "Some items from A use the one and only B list and all others get "expected" and are locked. How are you going to determine which A items use the B list and which items just get "expected"?

[Siddharth Rout]

Hi

There will be no other exceptions. like i said when the 1st option in list 1 i.e "apple" is clicked then only B1 should display "expected" and should get locked. if any other item is selected from list 1 in A1, for example "oranges", then i should be able to choose any item from the 2nd list in B1 and the cell B1 should remain unlocked.

alternatively lets say the 1st list has "apple,oranges,grapes" and the 2nd list has "expected, in stock,out of stock, delay".

now when i choose apple from list 1 in A1 then B1 should display "expected" and the cell should get locked(which means i cannot change values in cell B1). if i choose any other from list 1 say "grapes" then i should be able to select item from list 2 in B1(which means B2 is unlocked).

thankx once again for replying.

[DKenny]

Try the following _Change event procedure for the sheet with the validation.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$1" Then
        If UCase(Target.Value) = "APPLE" Then
            With Range("B1")
                .Validation.Delete
                .Value = "expected"
                .Locked = True
            End With
        Else
            With Range("B1")
                .Locked = False
                .Value = ""
                .Validation.Delete
                .Validation.Add Type:=xlValidateList, _
                                Formula1:="in stock,out of stock, delay"
            End With
        End If
    End If
End Sub

[Webtest]

Declan ...

With Range("B1")
.Locked = True <<< False???

[DKenny]

good catch -editing now...

[Webtest]

I tried to rate your post ... but you're getting too famous! (I already rated your earlier post today). I'm glad you got back on this one because I know nothing about lists. I guess I'll have to learn! I'll add your post to my "Cookbook".

[DKenny]

I use validation lists all the time, in many cases they are much easier to implement than adding a combobox control.

Cheers for the rep

[Siddharth Rout]

Kool, it works just fine. However when I password protect the sheet, it gives me an error.... how can i fix it?

[DKenny]

Within the change event you can unprotect the sheet before making the changes, then re-protect after the changes are made.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$1" Then
        'First unprotect the worksheet
        ActiveSheet.Unprotect ("YourPassword")
            
        'make the changes
        If UCase(Target.Value) = "APPLE" Then
            With Range("B1")
                .Validation.Delete
                .Value = "expected"
                .Locked = True
            End With
        Else
            With Range("B1")
                .Locked = False
                .Value = ""
                .Validation.Delete
                .Validation.Add Type:=xlValidateList, _
                                Formula1:="in stock,out of stock, delay"
            End With
        End If
        
        'Reprotect the worksheet
        ActiveSheet.Protect ("YourPassword")
    End If
End Sub

[Siddharth Rout]

Hi Dkenny

I did as you mentioned but it gives me the following error.

Runtime error 1004
"unable to set the unprotect property of the worksheet class"

i have protected the sheet thru the tools menu. and in the above code i have used the same password to unprotect & reprotect but whenever i try to change the cell it prompts for a password and when i key in the password it gives the above error message.

thankx

[DKenny]

1/ have you changed "YourPassword" in the above to your password?
2/ IF the workbook is protected as weel as the worksheet, you will need to
a) unprotect the workbook first,
b) then unprotect the sheet
c) make changes
d) reprotect the sheet
e) reprotect the workbook

[Siddharth Rout]

1. I have changed "yourpassword" to my password which i used to protect the sheet thru tool menu.
2. workbook is not protected.

thanks

[DKenny]

Can you upload your .xls file?

[Siddharth Rout]

I am mystified!!!

I am at home now. So i had to create a new file and guess what??? it works just fine here!!!! something wrong with my office pc i guess????

Thanks once again for all the trouble.

PS: unable to load the file as .xls is not an approved format. I am also rating your post once i submit this message.