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]

For example:

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.Open(Filename:="C:\Mem1\Custom Office Templates\Installation Agreement.docm", _
    AddToRecentFiles:=False, ReadOnly:=True)
  With wdDoc
    xlSht.Range("A1:G92").Copy
    .Range.Characters.Last.Paste
    .SaveAs Filename:="C:\MidSouth\PENDING\" & xlSht.Range("A92").Text & ".docm", AddToRecentFiles:=False
    .Close False
  End With
  .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing: Set xlSht = Nothing
End Sub

 

[Macropod]

You might want to change:

.SaveAs Filename:="C:\MidSouth\PENDING\" & xlSht.Range("A92").Text & ".docm", AddToRecentFiles:=False

to:

.SaveAs Filename:="C:\MidSouth\PENDING\" & xlSht.Range("A92").Text & ".doc", FileFormat:=wdFormatDocument, AddToRecentFiles:=False

Also cross-posted at: Create (word) User form for signature and date using excel VBA

 

[AawwYeahh]

Thanks! That helps solve my naming conundrum!
Any ideas on how to restrict editing to just the 2 cells?
in word you would click view restrict editing read only select cells permissions everyone and assign password then enable (but not sure how to pull this off in VBA)

 

[Macropod]

Any ideas on how to restrict editing to just the 2 cells?

Since the code I posted doesn't unprotect anything, why do you need that? In any event, the normal approach would be to apply editing protection to the entire worksheet, then unprotect only the two cells you're interested in.

BTW, why are you opening a macro-enabled document rather than creating a new file from a template? There is nothing about the process discussed in this thread that warrants the use of a macro-enabled document. Moreover, saving in either that format or the older .doc format leaves the macros in the document. If you used a template and/or saved in the docx format, that wouldn't occur.

 

[AawwYeahh]

Since the code I posted doesn't unprotect anything, why do you need that? In any event, the normal approach would be to apply editing protection to the entire worksheet, then unprotect only the two cells you're interested in.

BTW, why are you opening a macro-enabled document rather than creating a new file from a template? There is nothing about the process discussed in this thread that warrants the use of a macro-enabled document. Moreover, saving in either that format or the older .doc format leaves the macros in the document. If you used a template and/or saved in the docx format, that wouldn't occur.

That was my original format of my template I did correct that to a .dotx. And vba works correctly. Contract sheet is protected in excel with only A85 & G85 unlocked. But when you open in word entire document is editable not just the 2 cells?

 

[Macropod]

In Word, you can apply 'No changes (read only)' editing restrictions to any range you want, but that only works with .docx and .docm documents. In that case, you could insert code like:

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

before the .SaveAs line. This will protect the entire document, though, except for those two cells. Obviously, if you want other ranges to be editable, you'd have to apply '.Range.Editors.Add wdEditorEveryone' to those, too. As indicated in the code, you can apply a protection password.

 

[AawwYeahh]

Macropod, Thanks for helping, however, something is still not functioning correctly it opens/save/quits but when you open document in word ALL cells are still editable. I cannot determine what the problem is (BTW I rearranged contract so that signature and date lite are last lines of contract (I even attempted signature box) but alll still show as editable

here is my current code

Sub CreateWordReport()
Dim wordApp As New Word.Application, wordDoc As New Word.Document, xlSht As Excel.Worksheet
Set xlSht = ActiveWorkbook.Sheets("Contract")
With wordApp
  .Visible = True
  Set wdDoc = .Documents.Open(filename:="C:\MemphisCAC\Custom Office Templates\Installation Agreement.dotx", _
    AddToRecentFiles:=False, ReadOnly:=True)
  With wdDoc
    xlSht.Range("A1:G91").Copy
    .Range.Characters.Last.Paste
    .SaveAs filename:="C:\MidSouth\PENDING\" & xlSht.Range("A92").Text & ".doc", FileFormat:=wdFormatDocument, AddToRecentFiles:=False
    .Close False
  With .Tables(.Tables.Count)
      .Cell(3, 91).Range.Editors.Add wdEditorEveryone
      .Cell(7, 91).Range.Editors.Add wdEditorEveryone
    End With
    .Protect Password:="", Type:=wdAllowOnlyReading
  .Quit
End With
Set wdDoc = Nothing: Set wdApp = Nothing: Set xlSht = Nothing
End With
End Sub

[/CODE]

 

[Macropod]

It's no working because you're still saving the document in the .doc format. As I said in post #7:

that only works with .docx and .docm documents

 

[AawwYeahh]

It's no working because you're still saving the document in the .doc format. As I said in post #7:

Macropod, I have modified formula to use .docx. But CANNOT seem to get the code to point to correct cells in word
I found a working vba for word that shows cell address ( in word address shows for 1st cell as column 2 row 85 and 2nd row as column 4 row 85) in excel cells show as 1st C91 and 2nd as G91)
(I could not get the one published by you to run, kept showing errors)
And still I run into the same problem when new .docx is created all cells are editable and cannot seem to find the correct cells to allow edit for everyone