Changing values for a checkbox on an excel form

03-12-2003, 02:11 PM

TomGuy

Junior Contributor

Join Date: Sep 2001

Location: Igloo # 7, Canada

Posts: 332

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:

Code:

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

__________________
If vegetarians eat vegetables, watch out for humanitarians...

03-12-2003, 04:37 PM

Legend

Contributor

Join Date: Dec 2002

Posts: 542

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

03-13-2003, 06:49 AM

TomGuy

Junior Contributor

Join Date: Sep 2001

Location: Igloo # 7, Canada

Posts: 332

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

Any other ideas?

__________________
If vegetarians eat vegetables, watch out for humanitarians...

03-13-2003, 10:53 AM

TomGuy

Junior Contributor

Join Date: Sep 2001

Location: Igloo # 7, Canada

Posts: 332

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

Any help greatly appreciated.

Thanks!

__________________
If vegetarians eat vegetables, watch out for humanitarians...

03-14-2003, 06:18 AM

TomGuy

Junior Contributor

Join Date: Sep 2001

Location: Igloo # 7, Canada

Posts: 332

Anyone?

__________________
If vegetarians eat vegetables, watch out for humanitarians...

03-14-2003, 01:19 PM

nighthawk

Junior Contributor

Join Date: Jun 2001

Location: WV

Posts: 280

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.

03-14-2003, 01:37 PM

TomGuy

Junior Contributor

Join Date: Sep 2001

Location: Igloo # 7, Canada

Posts: 332

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

__________________
If vegetarians eat vegetables, watch out for humanitarians...