access to excel export table with a validation list 1

[rust]

I am exporting an Access table to Excel which will be emailed to the users. It will have a true false column.
For the ease of the users I would like to include a validation list will the export so rather than typing in true they can click the little combo box that excel creates with the validation list.

How do I do it?

 

[PHV]

Say you have instantiated objXL as an Excel application and opened the workbook, you can try something like this:
With objXL.Columns("E:E").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="True;False"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "Your input title"
.ErrorTitle = "Your error title"
.InputMessage = "Your input message"
.ErrorMessage = "Your error message"
.ShowInput = True
.ShowError = True
End With

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rust]

Hi PH
Thanks for being there.
You were close but your solution was actually funny - it put both choices into the cell!!!!

Being I was now awake I used the record macro which ended up working:

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="False,True"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Your help was really appreciated kept me on the right track. Thanks again.

 

[Kobayashi]

Perhaps I've missed something but how does the above actually work? Where is the code added?

I'm currently exporting an Access table into Excel using DoCmd.TransferSpreadsheet. Can anybody advise how I can add validation to the exported file?

Many thanks,

Adrian