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

AawwYeahh

New Member
Joined
Aug 10, 2017
Messages
33
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:

File-Copy-icon.png

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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Macropod

Retired Moderator
Joined
Aug 27, 2007
Messages
3,637
For example:
VBA Code:
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,637
You might want to change:
VBA Code:
.SaveAs Filename:="C:\MidSouth\PENDING\" & xlSht.Range("A92").Text & ".docm", AddToRecentFiles:=False
to:
VBA Code:
.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
 
Last edited:

AawwYeahh

New Member
Joined
Aug 10, 2017
Messages
33
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,637
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

New Member
Joined
Aug 10, 2017
Messages
33
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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,637
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:
VBA Code:
    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

New Member
Joined
Aug 10, 2017
Messages
33
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

VBA 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

Retired Moderator
Joined
Aug 27, 2007
Messages
3,637
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

New Member
Joined
Aug 10, 2017
Messages
33
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
 

Forum statistics

Threads
1,176,104
Messages
5,901,404
Members
434,890
Latest member
ababb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top