PDA

View Full Version : Drop down lists problem



SPND
09-04-2007, 07:10 AM
Hi,

This is my first post on this forum. Unfortunately i could not find a solution to my current problem in the exisiting posts although I am sure it is a easy one.

What I am trying to achieve is to get cell values change based on the selection of a drop down list. My drop downlist is in cell E6 and if the user selects option 1 in the drop down list then cell values in cell E13 to E23 should become 0 and not allow the user to change it without changing the drop down option.

If the user selects Option 2 in the drop down list, cell values in E13 to E23 should allow the user to input values ranging from 1 to 5 but not 0.

Hopefully I have been able to explain my problem clearly.

All help will be greatly appreciated.

Rgds,
SPND

mdmackillop
09-04-2007, 02:19 PM
Hi SPND,
Welcome to VBAX
Give this a try.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Range("E13:E23")

Select Case Range("E6").Text
Case "Option 1"
Range("E13:E23") = 0
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="0"
.InCellDropdown = False
.ShowError = True
.ErrorTitle = "Options"
.ErrorMessage = "You must change the Option to enter a number"
End With
Case "Option 2"
With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="1,2,3,4,5"
.InCellDropdown = False
.ShowError = True
.ErrorTitle = "Options"
.ErrorMessage = "You must change the Option to enter 0"
End With
End Select
End Sub

SPND
09-05-2007, 02:15 AM
Hi Mdmackillop

Validation for range e13 to E22 based on text E6 is working perfectly. Thank you, however I would also like to validate ranges E25:E34 and range E37:E46 based on text of E6.

In addition the sheet should allow multiple tasks and therefore ranges F13:F22 ; F25:F34 ; F37:F46 need to be validated based on entry of text of F6. and so on for columns G, H etc.

I am very very new to VB programming therefore I do not know how to set up multiple validations is the same code.

I am attaching a copy of the sheet based on your solution and my existing dilemma. Thanks again for taking interest in my problem,

SPND