Utilising Excel combo/check boxes from within Access

[Sech]

I have a database which stores various fields of information. There is a section which populates these fields on various locations within several different Word and Excel documents. The Word ones work fine but I am having trouble with the Excel one.

I have Excel, the workbook and worksheet open as object variables. I know how to select cells in the spreadsheet and update them to field values. However many of the fields are either combo boxes or check boxes that have been added within Excel as shapes. Does anyone know the lines of code required to select a combo box and then allocate it a value from an Access text/number field? Also how do I take the value from an Access yes/no field and set an Excel check box to it?

 

[PHV]

Have you tried the Excel macrorecorder ?

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

 

[Sech]

Yea unfortunately the macro recorder only seems to record the line of code which highlights the box, but nothing when selecting different values from it.

 

[Sech]

OK Ive worked out how to set check boxes using code in Access -

objExcel.ActiveSheet.DrawingObjects("Check Box 57").Value = 1

However when I try a similar thing with a combo box it won't allow me to set the value. Does anyone know why not and if there is another method?

 

[PHV]

If the combo is bounded to a cell you have to modify the cell value instead of the combo.

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

 

[Sech]

The combo boxes are linked to Input Ranges of values, but have no cell links. How can I get the combo to automatically select one of the values by matching to a field value in an Access table? If there is a way of putting the selected cell from the input range into the box as some sort of default then I can run a search through the correct range of input cells to find the correct one.

 

[PHV]

I wonder if you can play with the ListIndex property of the combo.

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