Changing values for a checkbox on an excel form

Hi,

I'm using VB6 to populate an Excel file that I've created. The Excel file has a few controls on it, including some checkboxes. This is the code I'm using to populate the checkboxes:
Dim Index As Integer
Index = 1
Dim obExcelApp As Excel.Application
Dim obWorkBook As Excel.Workbook
Set obExcelApp = CreateObject("Excel.Application")
Set obWorkBook = obExcelApp.Workbooks.Open("C:\WINDOWS\SYSTEM\Sales Order Template.xlt")

obWorkBook.Worksheets(Index).chkCheque.Value = True

' Save as a temporary file to save the template and show the temp file
obWorkBook.SaveAs ("C:\tempsales.xls")
obExcelApp.Visible = True

' clear the variables
Set obWorkBook = Nothing
Set obExcelApp = Nothing
When the excel file opens though, the checkboxes don't visually appear to be checked. They are checked, however, if I messagebox their values. Also, once the form is open, the first time that I try to check (enable) them, they flash checked but remain unchecked. After that they work normally (going from checked to unchecked)....It's as if the form is not being refreshed when it first loads.

If you can help me out, it would be greatly appreciated.

Thanks a bunch,

Tom

You could try getting the workbook to refresh (obWorkBook.RefreshAll) - - can't try it myself at the moment though :(

Thanks for the suggestion but unfortunately it didn't work.

Any other ideas?

Is there a way to force the checkboxes to appear visually correct?...maybe a 2-step process even?

Any help greatly appreciated.

Thanks!

Anyone?

Heres the work around I ended up with for the same problem:

1) Pick a row that will not be used for your work book
2) Right Click on the Checkbox
3) Select Format Control
4) On the control tab you'll see a cell link field
5) Assign the checkbox to a cell in the row you chose
6) Do this for each checkbox giving them each a different cell in the row
7) Now you can put the value True or False into the cells
8) The checkboxes will respond accordingly


Just to note I selected the row and hid it so the user would not see the value. I just change the values in the hidden row using VB and the correct checkboxes become highlighted. It works great for me.

That worked great! I didn't even think of using Form controls. I was using controls from the Control Toolbox because I was trying to refer to their names through code. Using the Form controls and then just linking them with cells on the form is a great idea!

Thanks a bunch!

Tom