IN VBA create a Data Vaildation List from a Case Selection

[Red Salerno]

is it possible to create a data Validation List from a Case Selection

ie


With Range("E9").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="= Case X"
'the X comes from Case
Dim SectorType As Integer
Worksheets("Input").Select
SectorType = Range("T34").Value
Select Case SectorType
Case 1 = "='WORKSHEET#1'!$A$8:$J$8"
Case 2 = "='WORKSHEET#2!$A$8:$J$8"
End Select

I'm struggling with this one

[JKwan]

how about this:


Sub CreateValidation(CellLocation As Range, _
ValidationList As Variant, _
Optional sInputTitle As String, _
Optional sErrorTitle As String, _
Optional sInputMessage As String, _
Optional sErrorMessage As String)
With Range(CellLocation.Address).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(ValidationList, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = sInputTitle
.ErrorTitle = sErrorTitle
.InputMessage = sInputMessage
.ErrorMessage = sErrorMessage
.ShowInput = True
.ShowError = True
End With
End Sub
Sub test()
Dim ValList As Variant
Dim lInput As Long

lInput = InputBox("Enter a Number", , 1)
Select Case lInput
Case Is = 1
ValList = Array("apple", "banana")

Case Is = 2
ValList = Array("beets", "squash")
End Select
CreateValidation ActiveSheet.Range("E9"), ValList
End Sub

[snb]

Sub M_snb()
Select Case InputBox("Enter a Number", , 1)
Case 1
c00 = "apple,banana"
Case 2
c00 = "beets,squash"
End Select
ActiveSheet.Range("E9").validation.modify 2,c00
End Sub

or

Sub M_snb()
on error resume next
ActiveSheet.Range("E9").validation.modify 2,choose(InputBox("Enter a Number", , 1),"apple,banana","beets,squash")
End Sub

[p45cal]

JKwan, I see you have the likes of:
Sub CreateValidation(CellLocation As Range, _ ...
where CellLocation is a range.

Later you have:
With Range(CellLocation.Address).Validation

and I've seen this a few times today in other people's code.

So why not:
With CellLocation.Validation

I can see a reason for the longer version (if the validation is to be applied to the active sheet range rather than a specific sheet range, for example), but was that the intention here? …maybe I'm missing a trick.

[Red Salerno]

Sub CreateValidation(CellLocation As Range, _
ValidationList As Variant, _
Optional sInputTitle As String, _
Optional sErrorTitle As String, _
Optional sInputMessage As String, _
Optional sErrorMessage As String)
With Range(CellLocation.Address).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(ValidationList, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = sInputTitle
.ErrorTitle = sErrorTitle
.InputMessage = sInputMessage
.ErrorMessage = sErrorMessage
.ShowInput = True
.ShowError = True
End With
Dim SectorType As Long
Dim ValList As Variant
SectorType = Range("T34")
Select Case SectorType
Case Is = 1
ValList = Range("T41,T45")
Case Is = 2
ValList = Range("U41,U45")

End Select
CreateValidation ActiveSheet.Range("S41"), ValList
End Sub

**** I want the list to come from a range on the sheet ****

[Red Salerno]

This accomplished what I was looking to do!!!!!!!!


Sub Trythis()

Dim rList As String
Dim SectorType As Long
SectorType = Range("T34")
Select Case SectorType
Case Is = 1
rList = "$S$41:$S$45"

Case Is = 2
rList = "$T$41:$T$45"

End Select
With Range("s40")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
End Sub

[JKwan]

JKwan, I see you have the likes of:
Sub CreateValidation(CellLocation As Range, _ ...
where CellLocation is a range.

Later you have:
With Range(CellLocation.Address).Validation

and I've seen this a few times today in other people's code.

So why not:
With CellLocation.Validation

I can see a reason for the longer version (if the validation is to be applied to the active sheet range rather than a specific sheet range, for example), but was that the intention here? …maybe I'm missing a trick.

Why that is the case is that I removed the reference of the WORKSHEET. I did not think the op needed it, so I removed the code. I can pass a worksheet to create the Validation......

[mikerickson]

Why use VBA, you can do this with names. Define a Name

Name: myList ReferTo: =IF(Input!$A$4=1, WORKSHEET1!$A$8:$J$8, WORKSHEET2!$A$8:$J$8)

And then set the List Source of the validation to =myList.

[Aussiebear]

@mikerickson. LOL Sometimes we forget that Microsoft programmers have already invented the wheel. ( Almost all of it )

[p45cal]

Why that is the case is that I removed the reference of the WORKSHEET. I did not think the op needed it, so I removed the code. I can pass a worksheet to create the Validation......but an object variable such as CellLocation doesn't need a worksheet ref., it already is a range on a specific sheet. So what is the advantage of Range(CellLocation.Address). over CellLocation. apart from the former being less robust being dependent on which sheet happens to be active at the time the code is run (unless this code is in a sheet's code module - in which case the longer version is superfluous anyway).
At the moment I see no reason to detach the information that is within CellLocation (the worksheet) by using CellLocation.Address which contains no sheet information, to specify a range. Using the .Address property just seems to complicate matters. (Perhaps it's the inclusion in this variable's name of the word location which is implying (wrongly) that it is only a location of a range on a sheet rather than being the range itself?)

To crystallise, would:
With CellLocation.validation
do the job?
If yes, why not use it?

I must be missing something.

[snb]

if

Sub Trythis()
Dim rList As String
Dim SectorType As Long
SectorType = Range("T34")
Select Case SectorType
Case Is = 1
rList = "$S$41:$S$45"

Case Is = 2
rList = "$T$41:$T$45"

End Select
With Range("s40")
With .Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
End Sub

accomplishes what you want, this will acomplish it too:


Sub M_snb()
Range("s40").Validation.Add 3, 1, , "=" & range("$R$41:$R$45").offset(,Range("T34")).address
End Sub


Eventually you will need:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$T$34" Then Range("s40").Validation.Modify 3, 1, , "=" & Range("$R$41:$R$45").Offset(, Range("T34")).Address
End Sub