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.
Printable View
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.
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?
I was planning to use VBA - that's what I have avaliable.
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?
here is what i was starting with, it errors on the "activecell" line.... am i at least on the right path?
VB Code:
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
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.
sorry about that, i'm not all that well versed in the terminology, (mostly self taught.) I used the checkbox from the control toolbox.
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.
True for checked and False for unchecked.VB Code:
ActiveSheet.OLEObjects("CheckBox1").Object.Value
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.
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.
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?
This is what I got, but the dumb InlineShape is giving me problems getting its text property.
VB Code:
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
!!!!! i did it! finally got it figured out! here is the change....
VB Code:
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!
No prob, but where's the checkbox?
Good Point! I kind of went on a tangent but the it basically the same for my checkbox needs.
VB Code:
oWb.Sheets(1).Cells.Range("f3").Value = chkDoor01.Caption
thanks again.