Pulling check box info from Word to Excel

[bobgroove]

I would like to verify that a check box in Word is checked and if so, input the label of that checkbox into a field in excel.

Thanks in advance.

[RobDog888]

Welcome to the Forums.

Are you wanting to do this from within Words VBA Editor code or using VB6 to automate both Word and Excel?

[bobgroove]

I was planning to use VBA - that's what I have avaliable.

[RobDog888]

Ok, then first you need to get the checkbox referenced in code. Is it a checkbox from the activex controls
toolbox or the forms toolbox?

[bobgroove]

here is what i was starting with, it errors on the "activecell" line.... am i at least on the right path?

Sub Macro1()

Dim xApp As New Excel.Application
Dim xDoc As Excel.Workbook

Set xApp = CreateObject("Excel.Application")

Set xDoc = xApp.Workbooks.Open("C:\test\test.xls")
xApp.Visible = True
Range("f2").Select
ActiveCell = chkDoor01.Caption


End Sub

[RobDog888]

Ok, the "ActiveCell = chkDoor01.Caption" line is erroring because you can not reference the checkbox like you do in vb6.
This is why I asked which type of checkbox you are using because there are two types and each one is accessed differently.

[bobgroove]

sorry about that, i'm not all that well versed in the terminology, (mostly self taught.) I used the checkbox from the control toolbox.

[RobDog888]

No prob. I'm self taught too. :D

I like the ActiveX controls better then the Forms controls. They are easier to program against.
This is one method for accessing the checkbox's checked value.
ActiveSheet.OLEObjects("CheckBox1").Object.ValueTrue for checked and False for unchecked.

[bobgroove]

actually the check box is on the Word document not on the Excel. I guess part of my question is, how will VBA know which app it is working with at any given time? or how do i distingish between Word and Excel.

[RobDog888]

Oops I got it backwards. :blush: Thought you had the checkbox in Excel.
So from Word you want to create an instance of Excel and place the the word label text into a cell in excel.
Ok, your on the right track. You can keep track of the app by setting object variables to excel with excel naming like oXlApp, etc.

[bobgroove]

so it doesn't like the OLEObject protion of that code string you gave me earlier. "Method or data member not found" am I missing something else?

[RobDog888]

This is what I got, but the dumb InlineShape is giving me problems getting its text property.
Sub Macro1()
'Behind a word document
'Add a reference to MS Excel xx.0 Object Library
Dim oXlApp As New Excel.Application
Dim oWb As Excel.Workbook

Set oXlApp = CreateObject("Excel.Application")

Set oWb = oXlApp.Workbooks.Open("C:\Book1.xls")
oXlApp.Visible = True
'oWb.Sheets(1).Cells.Range("F2").Value = ActiveDocument.InlineShapes("CheckBox1").TextEffect.Text

End Sub

[bobgroove]

!!!!! i did it! finally got it figured out! here is the change....

Private Sub txtPONum_Change()
Dim oXlApp As New Excel.Application
Dim oWb As Excel.Workbook

Set oXlApp = CreateObject("Excel.Application")

Set oWb = oXlApp.Workbooks.Open("C:\test\test.xls")
oXlApp.Visible = True
oWb.Sheets(1).Cells.Range("F2").Value = txtPONum.Text

End Sub


Thanks so much for you help!

[RobDog888]

No prob, but where's the checkbox?

[bobgroove]

Good Point! I kind of went on a tangent but the it basically the same for my checkbox needs.

oWb.Sheets(1).Cells.Range("f3").Value = chkDoor01.Caption

thanks again.