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.
Declan
Don't forget to mark your Thread as resolved.
Take a moment to rate posts that you think are helpful
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.
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.
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.
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.
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.
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
End If ' if there are other controls need to check that they are the checkboxes
Next
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
do you have other ole objects on you worksheet other than the checkboxes?
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
' 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
Last edited by westconn1; Sep 1st, 2006 at 05:21 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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 do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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")
Last edited by westconn1; Sep 2nd, 2006 at 04:00 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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")
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.
[RESOLVED] Mirroring Checkbox states
Reply With Quote
Originally Posted by Harddisk
