Filling Userform in Word with Excel cell value.

[AW]

I posted this to the Excel.Programming group but haven't had any joy there
yet. Perhaps this group is more representative of the query. I hope so.

I am opening a Word document from Excel. I then wish to take a cell value
from excel and populate a textbox on a userform in Word.
Using the code below, the TextBox isn't populating and I'm getting a Run
Time error(438). Please can anyone point to the cause of the error.

MyDoc.UserForm("frmCCS").TextBox("txtCostNon").Value =
Worksheets("Sheet1").Cells(25, 5).Value


Thanks

Al@n

 

[Ken Laws [MSFT]]

Hi Alan,

The 438 error is being generated because "UserForm" is not a property or
method of Microsoft Word's document object.

To set the value of a userform's text box from automation I would suggest
the following solution.

1. Add a module to the Word document and then add a subroutine similar to
the code below.

Sub ShowForm(s As String)
frmCSS.txtCostNon.Value = s
frmCSS.Show
End Sub

2. From your VBA code in Excel execute the a line of code similar to the
following once the Word document is open.


WordApp.Run "ShowForm",Worksheets("Sheet1").Cells(25, 5).Value

where WordApp is the Microsoft Word application variable.

I hope this helps!

If you have any questions please let me know via the posting.

Regards,

Ken Laws
Microsoft Support


This posting is provided "AS IS" with no warranties, and confers no rights.

 

[AW]

Ken

Thanks for the reply

I've entered the code as you suggested-see below, however it falls over when
I run it.

The User Form "frmCCS" is shown but the xl cell value isn't shown in the
textbox "txtCostNon", I then get a runtime 424 error followed by an
automation error (runtime 440) . When debgging it points to:

"appWD.Run "ShowForm", Worksheets("Sheet1").Cells(25, 5).Value"

I have done what you have suggested in a new module in the Word Template.
Does the string "s" need to be quantified anywhere?

Please excuse my lack of knowledge but am new to Automation and cross
application working.

Private Sub cmdCCA_Click()
Dim appWD As Object
Dim MyDoc As Object
Set appWD = CreateObject("Word.Application")
With appWD
Set MyDoc = .documents.Add(Template:= _
"C:\Documents and Settings\Alan\Desktop\Test1\CCS2.dot", _
NewTemplate:=False, DocumentType:=0)
MyDoc.Activate
appWD.Run "ShowForm", Worksheets("Sheet1").Cells(25, 5).Value
MyDoc.Application.Visible = True
End With
End Sub

Thanks again for your assistance

Alan

 

[AW]

Ken

I Think I've resolved an issue with the autonew module in word calling for
frmCCS to open as well as "ShowForm"

Things seem to be working. Please disregard the last post and thanks again

Alan