Create (word) User form for signature and date using excel VBA

[AawwYeahh]

Ok I am stymied. Please help!
I have gotten code to:
Open word
Open template
Copy cells (from excel)
Paste cells into word

HOWEVER
I want to create name for word document (cell A92 in excel)
Save word document as filename (cell A92 in excel)
As .doc
In folder C:\MidSouth\PENDING

Lock all cells against editing
EXCEPT one cell for signature (in excel cell is A85)
AND one cell for date (in excel cell is G85)

Then close document.



Here is my current code:

Sub CreateWordReport()
Dim WordApp As Word.Application
Set WordApp = New Word.Application

With WordApp
.Visible = True
.Activate
.Documents.Open ("C:\Mem1\Custom Office Templates\Installation Agreement.docm")

Sheets("Contract").Unprotect Password:=""
Range("A1:G92").Select
Selection.Copy
.Selection.Paste

Crossposted at https://www.excelguru.ca/forums/show...sing-excel-VBA

 

[Macropod]

Oops! I mixed up the row/column references. Try:

    With .Tables(.Tables.Count)
      .Cell(85,1).Range.Editors.Add wdEditorEveryone
      .Cell(85,7).Range.Editors.Add wdEditorEveryone
    End With
    .Protect Password:="", Type:=wdAllowOnlyReading

For code to verify the output cell addresses, see: Identifying Table Cell Addresses

 

[AawwYeahh]

Oops! I mixed up the row/column references. Try:

    With .Tables(.Tables.Count)
      .Cell(85,1).Range.Editors.Add wdEditorEveryone
      .Cell(85,7).Range.Editors.Add wdEditorEveryone
    End With
    .Protect Password:="", Type:=wdAllowOnlyReading

For code to verify the output cell addresses, see: Identifying Table Cell Addresses

Paul I tried this current code but run into errors and when document opens it is still editable

I have previously tried your code for verifying cell addresses (not sure why but will not run)
I think I am going to throw in the towel on this idea.
I think instead (since I only need 4-5 cell values for contract) I may pursue a mail merge approach
(Complete entire contract via word)
Any idea on how to reference mail merge list via VBA?
PS I REALLY APPRECIATE YOUR HELP

 

[Macropod]

Paul I tried this current code but run into errors and when document opens it is still editable

What error(s)?

I have previously tried your code for verifying cell addresses (not sure why but will not run)

Did you actually select a cell in the Word table?

 

[AawwYeahh]

Paul thank you for your help on this endeavor. I am going to abandon this approach and try for a mail merge solution

 

[Macropod]

A mailmerge will not provide any form of document protection...

 

[AawwYeahh]

A mailmerge will not provide any form of document protection...

Well hell! Thanks again for your help! (on now to the next "re-invention of the wheel")

 

[Macropod]

On re-checking your code in post #8, I see you've added the protection code after closing the document instead of before saving the document as advised! Once the document is closed, there's nothing available to protect... Little wonder you'd be getting errors at that point in the code.

Try:

Sub CreateWordReport()
Dim wdApp As New Word.Application, wdDoc As New Word.Document, xlSht As Excel.Worksheet
Set xlSht = ActiveWorkbook.Sheets("Contract")
With WordApp
  .Visible = False
  Set wdDoc = .Documents.Add("C:\Mem1\Custom Office Templates\Installation Agreement.dotx")
  With wdDoc
    xlSht.Range("A1:G92").Copy
    .Range.Characters.Last.Paste
    With .Tables(.Tables.Count)
      .Cell(85, 1).Range.Editors.Add wdEditorEveryone
      .Cell(85, 7).Range.Editors.Add wdEditorEveryone
    End With
    .Protect Password:="", Type:=wdAllowOnlyReading
    .SaveAs Filename:="C:\MidSouth\PENDING\" & xlSht.Range("A92").Text & ".docx", FileFormat:=wdFormatXMLDocument, AddToRecentFiles:=False
    .Close False
  End With
  .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing: Set xlSht = Nothing
End Sub

 

[AawwYeahh]

MacroPod when attempting to run Debugger shows

 

[AawwYeahh]

MacroPod when attempting to run Debugger shows

View attachment 67463

If I change to Application.Visible = True
it then registers
(I did confirm file path)

 

[Macropod]

I messed up the wdApp reference - use wdApp instead of wordApp
That's what comes of trying to correct someone's adaptation of one's own code...

I also didn't notice you'd changed the template path. Use:
Set wdDoc = .Documents.Add(Template:="C:\MemphisCAC\Custom Office Templates\Installation Agreement.dotx")