Limit drop down list based on cell value

[ohmbru]

Is there a way to limit the choices in a drop down list based on some other value. The other value could be a cell or another drop down.

The dependant cell has a value of 1 thru 7. If the value = 1, then the drop down should be available to the user. Otehrwise, they should not be able to select any value.

Any assistance is appreciated.



Brian

 

[RobSchultz]

You can add this to your worksheet code. There isn't a readonly or true lock property on the listbox control. The .Locked property only keeps the user from modifying the control.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Address = "$A$1" Then
        Dim oShp As Shape
        Set oShp = ActiveSheet.Shapes("List Box 1") 'modify this to be your list box control
        
        If Range(Target.Address) = 1 Then
            oShp.Visible = msoCTrue
        Else
            oShp.Visible = msoFalse
        End If
    End If
    
End Sub

HtH,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[ohmbru]

What if I applied this to a cell property instaed of a control? Can I lock/unlock a cell?



Brian

 

[RobSchultz]

You can lock/unlock the cell or the control but you will have to use the .Unprotect method to unprotect the sheet before you can change the .Locked status (and then .protect it back). I'm not real sure it actually locks it from user interaction but could merely lock it from user editing.

You may consider using a userform with a listbox. It has a .Enabled property.

Regards,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[KenWright]

Sounds like you are looking for the functionality you would get from a dependent list in Data validation:-

eg You hit a dropdown and select a car model, then the next dropdown is the model choices for that car only.

http://www.contextures.com/xlDataVal02.html

Regards
Ken.................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------