adding data validation using vba

Question

hi all

i am trying to add data validation using vba but cant get passed the Formula1 := .

im trying to use a named as the source of the list and it i record the code it seems to work with what looks like a string"=contract"

however i would like the code to be able to change the name. to match the column the code is on. so i thought using a variable

i have tried myform = "=contract" and myform="=" & name.name

 this gives me myform with a value which looks like "=contract" the same as when recorded

leaving me with formula1:=myform

but this errors

Dim myform As String

    myform = "=contract"

    With Selection.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:=myform

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .ShowInput = True

        .ShowError = True

    End With

End Sub

is there a special way of putting the Equals sign into the variable

Answer 1

Hi Pete

As HansV says, you can not refer to a list in another workbook.

The one way to solve this is to build a list in a variable based on the content of the range in the master workbook. The only thing is that you have to update all validation lists if the list in MasterBook is changed!

Dim Master as Workbook

Dim ListRng As Range

Dim MyList As String

Set Master=Workbooks("MasterBook.xlsm")

Set ListRng = Master.Range("f1:f4")

For Each cell In ListRng

    MyList = MyList & cell.Value & ","

Next

MyList = Left(MyList, Len(MyList) - 1)

 With Selection.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:=MyList

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .ShowInput = True

        .ShowError = True

    End With

Answer 2

hi per

thanks for your response im afraid its more complicated than that. i have responded to Hanv with a bit more detail

i fear im trying to run befor i can walk

Answer 3

hi thanks for the response

i had tried this in isolation so there is a lesson learnt

maybe i should give you more of the story

i have a master workbook (new format master.xls) which has a number of lists on a sheet called list

the code runs from this workbook and as i need to create multiple copies creats a new workbook using

Set newlog = Workbooks.Add(xlWBATWorksheet)

it then adds names to the new workbook using

namescount = Workbooks("new format master.xls").Names.Count

For namescounter = 1 To namescount

myname = Workbooks("new format master.xls").Names.Item(namescounter).Name

newlog.ActiveSheet.Names.Add Name:=myname, RefersTo:="='new format master.xls'!" & myname

Next namescounter

it then adds sheets to the new work book renames them and adds headings in row 1

If ActiveSheet.Previous Is Nothing Then

Set NewSheet = Workbooks(newlog.Name).Worksheets.Add

NewSheet.Name = aname

For Each colhead In columnheadings

headingcount = headingcount + 1

NewSheet.Cells(1, headingcount) = columnheadings(headingcount, 1)

Next colhead

it is at this point i am trying to add the validation and am wondering if it is because the code is not running in the workbook the names are in anyway ill keep playing but any pointers will be apreciated.

Answer 4

Your code works, if you have a range named 'Contract' in the workbook.

Look at this:

Dim myform As String

Select Case ActiveCell.Column

Case 1 'Column A'

    myform = "=UserID"

Case 2

    myform = "=contract"

Case 3

    myform = "=OtherNamedRange"

Case Else

    Exit Sub

End Select

    With Selection.Validation

        .Delete

        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

        xlBetween, Formula1:=myform

        .IgnoreBlank = True

        .InCellDropdown = True

        .InputTitle = ""

        .ErrorTitle = ""

        .InputMessage = ""

        .ErrorMessage = ""

        .ShowInput = True

        .ShowError = True

    End With

End Sub

Answer 5

I created a range named contract, then ran your code. It correctly added validation to the selected cells, with the contract range used for the dropdown list.

So check very carefully that you have used the correct name, spelled exactly as defined.