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.

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.Value

True 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. 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.