Validation using VBA
 

I want to addd validation to cells on a spreadsheet using
VBA. I am setting up the spreadsheet with data and
formating using VBA, and want to addd validation to a
column during this process. The data for the list is on a
worksheet called "Contractors", and the validation cell is
R4 on whatever sheet is active at the time of running the
code. I have tried the following code:

For l = 1 To 20

Worksheets("Contractors").Select
Cells(l, 1).Select
contract = ActiveCell.Value
list(l) = contract

Next l

Worksheets(shtname).Select

test = list(1)
For l = 2 To 20

test1 = list(l)
test = test & "," & test1

Next l
With Range("R4").Validation

.Add Type:=xlValidateList, Formula1:=test

I get an error message saying "Application-defined or
object-defined error".

The range containing the data which is A1:A20, is also
named, but using the name doesn't help either.

Any help would be great.
Regards

David C

Validation using VBA
 

David

one way:

Sub AddValidation()
' assumes validation list is named as ValidList
' ValidList: =Contractors!$A$1:$A$20
'
With Range("R4").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=ValidList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Regards

Trevor


"David Coates" wrote in message
...
I want to addd validation to cells on a spreadsheet using
VBA. I am setting up the spreadsheet with data and
formating using VBA, and want to addd validation to a
column during this process. The data for the list is on a
worksheet called "Contractors", and the validation cell is
R4 on whatever sheet is active at the time of running the
code. I have tried the following code:

For l = 1 To 20

Worksheets("Contractors").Select
Cells(l, 1).Select
contract = ActiveCell.Value
list(l) = contract

Next l

Worksheets(shtname).Select

test = list(1)
For l = 2 To 20

test1 = list(l)
test = test & "," & test1

Next l
With Range("R4").Validation

.Add Type:=xlValidateList, Formula1:=test

I get an error message saying "Application-defined or
object-defined error".

The range containing the data which is A1:A20, is also
named, but using the name doesn't help either.

Any help would be great.
Regards

David C

Validation using VBA
 

Hi David
try recording a macro while doing the following manually:
- assign a defined name for your validation list source
- use this name as data source for your validation listbox

Use this recorded code


--
Regards
Frank Kabel
Frankfurt, Germany


David Coates wrote:
I want to addd validation to cells on a spreadsheet using
VBA. I am setting up the spreadsheet with data and
formating using VBA, and want to addd validation to a
column during this process. The data for the list is on a
worksheet called "Contractors", and the validation cell is
R4 on whatever sheet is active at the time of running the
code. I have tried the following code:

For l = 1 To 20

Worksheets("Contractors").Select
Cells(l, 1).Select
contract = ActiveCell.Value
list(l) = contract

Next l

Worksheets(shtname).Select

test = list(1)
For l = 2 To 20

test1 = list(l)
test = test & "," & test1

Next l
With Range("R4").Validation

.Add Type:=xlValidateList, Formula1:=test

I get an error message saying "Application-defined or
object-defined error".

The range containing the data which is A1:A20, is also
named, but using the name doesn't help either.

Any help would be great.
Regards

David C

Validation using VBA
 

Thanks heaps. Works well.

Found one thing though. I can't assign the name to the
range using VB 'Names.add Name:=" ", Refers
To:= "Contractors!$A$1:" & lastadddress ' as it takes the
value in the first cell in the range 'Refers to' as the
name of the range even though a name is assigned to
the 'name' value. I was hoping to assign the name to a
range which could vary in length.


-----Original Message-----
David

one way:

Sub AddValidation()
' assumes validation list is named as ValidList
' ValidList: =Contractors!$A$1:$A$20
'
With Range("R4").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=ValidList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Regards

Trevor


"David Coates"
wrote in message
...
I want to addd validation to cells on a spreadsheet
using
VBA. I am setting up the spreadsheet with data and
formating using VBA, and want to addd validation to a
column during this process. The data for the list is
on a
worksheet called "Contractors", and the validation cell
is
R4 on whatever sheet is active at the time of running
the
code. I have tried the following code:

For l = 1 To 20

Worksheets("Contractors").Select
Cells(l, 1).Select
contract = ActiveCell.Value
list(l) = contract

Next l

Worksheets(shtname).Select

test = list(1)
For l = 2 To 20

test1 = list(l)
test = test & "," & test1

Next l
With Range("R4").Validation

.Add Type:=xlValidateList, Formula1:=test

I get an error message saying "Application-defined or
object-defined error".

The range containing the data which is A1:A20, is also
named, but using the name doesn't help either.

Any help would be great.
Regards

David C




.

Validation using VBA
 

Thank you for your thoughts. I have now solved the
problem. It did rely on setting the data source as a
named range.
-----Original Message-----
Hi David
try recording a macro while doing the following manually:
- assign a defined name for your validation list source
- use this name as data source for your validation listbox

Use this recorded code


--
Regards
Frank Kabel
Frankfurt, Germany


David Coates wrote:
I want to addd validation to cells on a spreadsheet
using
VBA. I am setting up the spreadsheet with data and
formating using VBA, and want to addd validation to a
column during this process. The data for the list is
on a
worksheet called "Contractors", and the validation cell
is
R4 on whatever sheet is active at the time of running
the
code. I have tried the following code:

For l = 1 To 20

Worksheets("Contractors").Select
Cells(l, 1).Select
contract = ActiveCell.Value
list(l) = contract

Next l

Worksheets(shtname).Select

test = list(1)
For l = 2 To 20

test1 = list(l)
test = test & "," & test1

Next l
With Range("R4").Validation

.Add Type:=xlValidateList, Formula1:=test

I get an error message saying "Application-defined or
object-defined error".

The range containing the data which is A1:A20, is also
named, but using the name doesn't help either.

Any help would be great.
Regards

David C
.

Validation using VBA
 

David

not sure if this will help if you need everything in code:

Sub AddValidation()
' assumes validation list is named as ValidList
' ValidList: =OFFSET(Contractors!$A$1,,,COUNTA(Contractors!$A:$ A))
' to give a variable length list
'

ActiveWorkbook.Names.Add _
Name:="ValidList", _
RefersTo:= _
"=OFFSET(Contractors!$A$1,,,COUNTA(Contractors!$A: $A))"

With Range("R4").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=ValidList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Regards

Trevor


"David Coates" wrote in message
...
Thank you for your thoughts. I have now solved the
problem. It did rely on setting the data source as a
named range.
-----Original Message-----
Hi David
try recording a macro while doing the following manually:
- assign a defined name for your validation list source
- use this name as data source for your validation listbox

Use this recorded code


--
Regards
Frank Kabel
Frankfurt, Germany


David Coates wrote:
I want to addd validation to cells on a spreadsheet
using
VBA. I am setting up the spreadsheet with data and
formating using VBA, and want to addd validation to a
column during this process. The data for the list is
on a
worksheet called "Contractors", and the validation cell
is
R4 on whatever sheet is active at the time of running
the
code. I have tried the following code:

For l = 1 To 20

Worksheets("Contractors").Select
Cells(l, 1).Select
contract = ActiveCell.Value
list(l) = contract

Next l

Worksheets(shtname).Select

test = list(1)
For l = 2 To 20

test1 = list(l)
test = test & "," & test1

Next l
With Range("R4").Validation

.Add Type:=xlValidateList, Formula1:=test

I get an error message saying "Application-defined or
object-defined error".

The range containing the data which is A1:A20, is also
named, but using the name doesn't help either.

Any help would be great.
Regards

David C
.