[RESOLVED] Mirroring Checkbox states

I have a VB application that has a userform to fill. The data being entered is submitted to an Access dB. I have a print button to print the data, however, because of reasons too long to explain, I have to print from my form template in excel.

I have checkboxes on my VB form and checkboxes on the excel worksheet. How do I go about getting the excel checkboxes to mirror the states of the VB app when I engage the load & print subroutine?

Do the matched pairs of checkboxes have the same name in the excel ssheet and in the VB app?
If so, you could pass the name and value of each VB chkbox into a 2D array and then loop through the Excel chkboxes, setting the value to that stored in the array.

Here is what I'm attempting, with no success. Everything works but the checkboxes.

VB Code:

If SSTab1.Tab = 1 Then
        Set oXLApp = New Excel.Application  'Create a new instance of Excel
        Set oXLBook = oXLApp.Workbooks.Open(App.Path & "\TRW_Template.xls") 'Open Daily Data Template
        oXLApp.Visible = False              'Hide Excel Spreadsheet
'************************************************************************************************
        Worksheets("Safety Flash Report").Range("A3").Value = Date
'************************************************************************************************
        For DataIndex = 0 To 11
            If chkFlash(DataIndex).Value = vbChecked Then
                Worksheets("Safety Flash Report").CheckBox(DataIndex + 1).Value = vbChecked    'checkboxes
            End If
        Next
'************************************************************************************************
        FlashDateRaw = CDate(cboFlash(0).Text & "/" & cboFlash(1).Text & "/" & cboFlash(2).Text)
 etc....

I'm not sure what's missing but I've stated the appropriate sheet the checkboxes...what do I change on the Excel chkboxes? This is my first time with the VBA implementation of VB.

VBA dosn't support Control Array as VB does. Therefore, I assume the problem you are haveing relates to the following line:

Worksheets("Safety Flash Report").CheckBox(DataIndex + 1).Value = vbChecked


Right?

Is placing the CheckBox's on a UserForm an option?

No I can't use a VBA Userform. The worksheet is a report print-out, I am simply filling out the worksheet with my program data so I have a consistent looking report.

So how do I go about getting this to work? Remember I have very little VBA experience.

Here is what I am trying to do, file attached, second worksheet.

vbchecked doesn't seem valid, try true or false

Quote:

---

CheckBox An integer value indicating whether the item is selected:
Null Indicates the item is in a null state, neither selected nor cleared.
–1 True. Indicates the item is selected.
0 False. Indicates the item is cleared.

Copyright(c) 1996 Microsoft Corporation.

---

vbchecked returns null

pete

westconn1 nailed it! :)

Quote:

---

Originally Posted by Harddisk

westconn1 nailed it! :)

---

That would be true if he was exclusively using VBA. However, he is using VB (as stated in post #1) and loading an instance of Excel (from within VB).
Therefore - vbChecked is valid.

The issue is there are no Control Arrays in VBA, therefore you cannot pass thru them with an index etc.

But you see, what determines the value that the property Value (pardon the pun) is on the control itself, despite the programming languages you use.

From what I see, in Excel, the controls are probably based on Form 2.0, which is an older version of Form Controls if you compared them to the controls on VB6 (can't remember what Form version they are using, probably 2.1?) itself.

Hence, by assigning True/False directly towards the control via the Excel object, it might just work. :)

Quote:

---

But you see, what determines the value that the property Value (pardon the pun) is on the control itself, despite the programming languages you use.

---

regardless of whether vbchecked has the right value (as it has in this case, but not in vba)
you can not check the checkbox without addressing it individually
sheet1.checkbox1.value = true (or vbchecked)
sheet1.checkbox2.value = true (or vbchecked)
sheet1.checkbox3.value = true (or vbchecked)
sheet1.checkbox4.value = true (or vbchecked)
sheet1.checkbox5.value = true (or vbchecked)
sheet1.checkbox6.value = true (or vbchecked)
etc...etc.

pete

Quote:

---

The issue is there are no Control Arrays in VBA, therefore you cannot pass thru them with an index etc.

---

ok... you can loop through the checkboxs on a sheet, they are part of the oleobjects collection and can be checked or unchecked as part of a for loop code

VB Code:

With Sheet4
For i = 1 To .OLEObjects.Count
If Not InStr(.OLEObjects(i).Name, Check) = 0 Then
.OLEObjects(i).Object.Value = True
End If
Next
End With

, or for the specific code in this post

VB Code:

For dataindex = 0 To 11
            If chkFlash(dataindex).Value = vbchecked Then
                Worksheets("Safety Flash Report").OLEObjects(dataindex).Object.Value = vbchecked     'checkboxes
            End If      ' if there are other controls need to check that they are the checkboxes
        Next

I understand where you are going with this, but what do I have to configure on the Excel template work sheet?

So I have a number of checkboxes named Checkbox1,Checkbox2.....
They each have the same formula "=EMBED("Forms.CheckBox.1","")"

And I have this:

VB Code:

For DataIndex = 0 To 11
            If chkFlash(DataIndex).Value = vbChecked Then
                Worksheets("Safety Flash Report").OLEObjects(DataIndex).Object.Value = vbChecked
            End If
        Next

What am I missing?

Now this works:

VB Code:

If chkFlash(DataIndex).Value = vbChecked Then
                Worksheets("Safety Flash Report").CheckBox1.Value = vbChecked
            End If

Is there a way to select my index, add 1 to it and add it to the end of "Checkbox"?

do you have other ole objects on you worksheet other than the checkboxes?

Quote:

---

For DataIndex = 0 To 11
If chkFlash(DataIndex).Value = vbChecked Then
Worksheets("Safety Flash Report").OLEObjects(DataIndex).Object.Value = vbChecked
End If
Next

---

basically relies on there being no other olebjects except for the checkboxes
otherwise you need to extract the right part of the checkbox name ie.the number and use that as the index

i think this will work, it is using the name of the checkbox to represent the index from your vb

VB Code:

With Worksheets("Safety Flash Report")
For i = 1 To .OLEObjects.Count
If Not InStr(.OLEObjects(i).Name, Check) = 0 Then
    myindex = Right(.OLEObjects(i).Name, Len(.OLEObjects(i).Name) - Len("Checkbox"))
    If chkFlash(myindex).value = vbChecked Then
                .OLEObjects(i).object.value = vbChecked
            Else: .OLEObjects(i).object.value = False
            End If
                      ' or .oleobjcts(i).object.value = chkflash(myindex).value
End If
Next
End With

so CheckBox11 mirrors chkFlash(11)
if you have a chkFlash(0), you will need to put myindex -1 when testing the value of chkFlash, then CheckBox11 will mirror chkFlash(10)


Edit: fix error

Lets try just one!

VB Code:

If chkFlash(1).Value = vbChecked Then
            Worksheets("Safety Flash Report").OLEObjects("CheckBox1").object.Value = vbChecked
        End If

What am I doing wrong here?

it should work, as close as i can test it does
at this point, what is the value of vbchecked?
it has to be true, or false, if it is empty it will give grayed check

what does it do? is there an error?

do you want to post your project

i don't know why the methods are not exposed to vb program, but i have tested this from a vb6 project, with some checkboxes. it works and the correct value is saved, but sometimes the checkboxes show checked when they should be unchecked or vise versa even though it's value is correct.

sorry if this is confusing, on reopening the workbook the checkboxes are correct

here is the code i used

VB Code:

Dim xlapp As Object
Dim mybook As Object
Dim myindex
On Error GoTo MyError
    Set xlapp = GetObject(, "Excel.Application")
    If TypeName(xlapp) = "Nothing" Then
        Set xlapp = CreateObject("Excel.Application")
    End If
      Set mybook = xlapp.workbooks.Open("C:\Documents and Settings\peter\Application Data\Microsoft\Excel\XLSTART\test.xls")
    xlapp.Visible = True
    
With mybook.sheets("Data")
For i = 1 To .Shapes.Count
If Not InStr(.Shapes(i).Name, "Check") = 0 Then
    myindex = Right(.Shapes(i).Name, Len(.Shapes(i).Name) - Len("Checkbox"))
        .Shapes(i).OLEFormat.object.object.Value = Check1(myindex - 1).Value
End If
Next
 
End With
    
mybook.Save
mybook.Close
xlapp.Quit False
 
Set xlapp = Nothing

I actually got it working 5 min before leaving for the long weekend on friday. I put up a post but I guess it didn't go through. Here is how I solved it using your help:

VB Code:

If SSTab1.Tab = 1 Then
        Set oXLApp = New Excel.Application  'Create a new instance of Excel
        Set oXLBook = oXLApp.Workbooks.Open(App.Path & "\TRW_Template.xls") 'Open Daily Data Template
        Set oXLSheet = oXLBook.Worksheets("Safety Flash Report")
        oXLApp.Visible = False              'Hide Excel Spreadsheet
    '********************************************************************************************
        oXLSheet.Range("A3").Value = Date
    '********************************************************************************************
Dim Ctrl As Object
 
        For Each Ctrl In oXLSheet.OLEObjects
            If Ctrl.OLEType = 2 Then
                For DataIndex = 1 To 12
                    If Replace(Ctrl.Name, "CheckBox", "") = DataIndex Then
                        If chkFlash(DataIndex).Value = vbChecked Then
                            If Ctrl.object.Value = False Then Ctrl.object.Value = True
                        End If
                        Exit For
                    End If
                Next
            End If
        Next
    '********************************************************************************************

I used the code from above, and I also added "Set oXLSheet = oXLBook.Worksheets("Safety Flash Report")" because I had been getting a number of runtime errors from the Worksheet object not being 100% defined.

Thanks for the help West.