From Access how to set validation dropdown in excel using VBA.

  • Thread starter Thread starter ras
  • Start date Start date
R

ras

I need to create an excel spread sheet from access that will be used
for data entry.


I will have a column of cells to enter the country for each row. I
would like to validate these cells against a list of countries. This
will be a named range else where in the sheet.

I am already formating the columnwidth of cells so just need the
method to attach validation to the cells in question.

TIA
 
Look up the Validation object in Excel VBA help. All you need do is set
the properties of the Validation property of the range you are working
with.

Often when working in Excel or Word it helps to record a macro as you
perform an operation manually; the code produced by the macro can then
be modified to work on a Range rather than the selection, and then
modified a bit more to work from Access.
 
Thanks for the reply. Still having trouble.

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


When I get to the xlValidateList, I get a compile error that the
variable is not defined. It seems that this excel constant is not
found. Does this have something to do with binding. If so how do I
bind excel to the application. The routine works to create the excel
file and write the recordset.

Are there any good examples of who to do this on the web. I have been
searching for a couple of days now.
 
As you say it's a binding matter. You have the choice of
-early binding, in which you set a reference to the Excel object library
and can declare Excel objects as (e.g.)
Dim raR As Excel.Range
and use Excel constants such as xlValidateList, or

-late binding in which you declare Excel objects like this:
Dim raR As Object 'This will be an Excel.Range
and use the actual numeric values instead of the Excel constants (or
declare your own, e.g.
Const my_xlValidateList = 3

Early binding means the code usually runs a little faster, and it can
make writing the code much easier because the Intellisense in the VB
Editor picks up the Excel stuff just as it does the Access stuff.

Late binding means you're much less likely to have problems if you move
the database to another computer or another version of Office.

So many people start off with early binding for convenience, and then
once the code is working properly convert it to late binding for
resilience.

See Help for more on early and late.
 
Thanks for the help. I was able to do this with late binding. I was
unable to figure out how to bind. the dim for excel.range does not
work.
 
Back
Top