PDA

View Full Version : [SOLVED] Using a Variable in creating a Validation List



DaveGib
02-15-2014, 07:08 AM
Hi All,
I have a problem that has got me beat, and I am hoping someone can please help:help

What I am trying to do - is create a 2nd validation list, dependant on the result of the 1st validation list, which puts the result in a formula to look up a particular range item and get its details.

What I have - are several sheets (15 actually) that each contain information about a particular plant range ( machinery not botanical). I have named each sheet by the prefix of each plant range, i.e. - sheet ("BL") contains the information regarding the plant items that begin with BL - i.e. BL101, BL102, .... BL120etc.
In each sheet I have created a Table with the same name as its prefix, i.e. "Tbl_BL", with a named range called R_BL, which is the lookup range I use to find the item detail.
I can get this all to work with static information but not using a variable.
Here is what I have....
Dave



' This code creates the first validation list and works ok
Range("D10").Select
With Selection.Validation ' Create Validation List for Plant Prefix
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PlantPrefix" ' PlantPrefix is a named range of all the prefixes
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

' **** This is where I have a problem....

Dim p As Variant ' I have tried String as well, I have also tried Range but do not know what 'Range' I should Set

If Range("D10").Value = "BL" Then
p = "BL"
ElseIf Range("D10").Value = "BR" Then
p = "BR"
ElseIf Range("D10").Value = "CS" Then
p = "CS"
' ElseIf Range("D10").Value = "DO" Then '.... etc
' p = "DO"
End If

' If I put a watch on p I get whatever is selected in D10 in quotations e.g. "BL"
' The line below that I have commented out works ok, but the line above it
' where I try to use the variable does not
Range("K10").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=IF(D10=""p"",R_" & p & ","""")" ' <=========HERE
' xlBetween, Formula1:="=IF(D10=""CS"",R_CS,"""")"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

' *** and in case it is of any interest to someone else here is the code that I use
' to look up the detail, - which all works ok


Dim SearchRange As Range
Dim Make As Variant
Dim Model As Variant
Dim Descripton As Variant
Dim ChassisNumber As Variant
Dim EngineNumber As Variant
Dim VinNumber As Variant
Dim PlantNumber As Variant
Dim Prefix As String
Dim PlantList As Range


Prefix = Cells(10, 4).Value ' Plant Prefix in Validation List in D10

Sheets(Prefix).Select
'Sheets("CS").Select

Set PlantList = Sheets(Prefix).Range("R_" & Prefix & "")
Set SearchRange = Sheets(Prefix).Range("Tbl_" & Prefix & "")
' Set SearchRange = Sheets("CS").Range("A1:G4")

Sheets("Requisition").Select

Range("K10").Select ' Create Validation List for Plant Number
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=PlantList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With



PlantNumber = Cells(10, 11).Value ' Plant Number in Validation list in K10


Make = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 2, False)
Model = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 3, False)
Description = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 4, False)
ChassisNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 5, False)
EngineNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 6, False)
VinNumber = Application.WorksheetFunction.VLookup(PlantNumber, SearchRange, 7, False)

' I then place the information in a sheet......

Sheets("Requisition").Select
Range("C12").Value = Description
Range("C13").Value = Make
Range("C14").Value = Model
Range("L12").Value = ChassisNumber
Range("L13").Value = EngineNumber
Range("L14").Value = VinNumber

mancubus
02-15-2014, 09:59 AM
hi.

what you need is "dependent data validation."

see: http://www.contextures.com/xlDataVal15.html

you can download the demo file at the bottom of the page.

DaveGib
02-16-2014, 01:38 AM
Hi mancubus
Thanks for the very useful link, it has helped to sort out my problem.......