Un-protecting Word Doc by Excel VBA for fillin and upload data to workbook automatically

[SqnLdrMona]

I've a workbook with VBA to send data to a workbook which is stored in a network shared drive. I now prompt the users to re-send the data by using a MsgBox, and another feature of the Sub is to fill ta Word Doc and a PDF for record purposes. The Word Doc will be protected after filling.

I now want to,
1. to protect the word doc to prevent users to edit it before VBA sending data to it
2. make it to wait until the the workbook is closed and upload the data automatically; and
3. confirm the data have been uploaded

Would the big Bros here give me some ideas or update my code I posted below. Thank you.
xxx
_______________
Sub DataToHHDHL_Referral()

'Declare Variables for This Automation

Dim wd As Word.Application
Dim wdDoc As Word.Document
Dim sh As Worksheet

'Start Word and add a new document
Set wd = New Word.Application

'Set worksheet where table is avaialble
Set sh = ThisWorkbook.Sheets("Input")

If WorksheetFunction.CountA(Worksheets("Input").Range("B2,b4,b6,b8")) < 4 Then
MsgBox "Required info not filled"
Exit Sub
Else
End If

'Opening the word template where bookmarks have been added
Set wdDoc = wd.Documents.Open(ThisWorkbook.Path & "\HHDHL_Referral.docx")

wd.Visible = False

wd.Selection.GoTo What:=wdGoToBookmark, Name:="HotelAdd"
wd.Selection.TypeText Text:=sh.Range("E11").Value

'..........

'Clear the Bookmarks from this file

On Error Resume Next

wdDoc.Bookmarks("Picker").Delete

'...............

'wdDoc.SaveAs2 ("D:\YCCP\temp\word\" & Range("B10").Value & ".docx")
wdDoc.SaveAs2 ("C:\Users\mohqtf429\Desktop\HelpDeskLogTest\UserLog\" & Range("B10").Value & ".docx") 'save word copy to user desktop folder

Application.ScreenUpdating = False

Response = MsgBox("Do you need a printout??", vbYesNo)

If Response = vbYes Then
wdDoc.ActiveWindow.PrintOut , Copies:=1, Collate:=True
'Application.Wait Now = TimeValue("00:00:15")
Else
'Ret = Unprotect: "1234"
'MsgBox "File is Closed"
End If

'wdDoc.ExportAsFixedFormat "D:\YCCP\temp\pdf\" & Range("B10").Value & ".pdf", wdExportFormatPDF, True
wdDoc.ExportAsFixedFormat "Z:\HQTF MO Room\HCST Data\Data Testing\PDFLog\" & Range("B10").Value & ".pdf", wdExportFormatPDF, True



wdDoc.Protect Password:="1234", NoReset:=False, Type:=wdAllowOnlyFormFields


wdDoc.Close

Set wdDoc = Nothing

wd.Quit
Set wd = Nothing

MsgBox "The info have not been saved"

End Sub

Sub TransferDataToMasterBookn()

Dim wbMaster As Workbook
Dim wbLocal As Workbook
Dim MasterNextRow As Long
Dim Cell As Range
Dim Ret

If WorksheetFunction.CountA(Worksheets("Input").Range("B2,b4,b6,b8")) < 4 Then
MsgBox "Required info not filled"
Exit Sub
Else
End If

Response = MsgBox("Have you checked the information?")
If Response = vbYes Then
Application.ScreenUpdating = False
Ret = IsWorkBookOpen("Z:\HQTF MO Room\HCST Data\Data Testing\Masterdata.xlsx")
If Ret = True Then
MsgBox "Master file is being used! "
'Application.Wait Now = TimeValue("00:00:15")
Exit Sub
Else
'Ret = Unprotect: "1234"
'MsgBox "File is Closed"
End If

Application.ScreenUpdating = False

Set wbLocal = ThisWorkbook
Set wbMaster = Workbooks.Open("Z:\HQTF MO Room\HCST Data\Data Testing\Masterdata.xlsx")

Sheets("Data").Unprotect Password:="1234"


'----------------------

MasterNextRow = wbMaster.Worksheets("data").Range("A" & wbMaster.Worksheets("data").Rows.Count).End(xlUp).Offset(1).Row

'----------------------------------------

wbMaster.Worksheets("Data").Cells(MasterNextRow, 1).Value = wbLocal.Worksheets("Input").Range("B4").Text
wbMaster.Worksheets("Data").Cells(MasterNextRow, 2).Value = wbLocal.Worksheets("Input").Range("B6").Value

'-----------------------------------
Sheets("Data").Protect Password:="1234"
wbMaster.Close True

'--------------------------------------'-----------------------------------
'--------------------------------------

Sheets("Input").Unprotect Password:="1234"

wbLocal.Worksheets("Input").Range("B6").Value = ""
wbLocal.Worksheets("Input").Range("B8").Value = ""
'.............

Sheets("Input").Protect Password:="1234"

Application.ScreenUpdating = True

MsgBox "Data have been uploaded."
Else
Response = MsgBox("Upload Cancelled", cbYesNo)
End If

End Sub


Function IsWorkBookOpen(FileName As String)
Dim ff As Long, ErrNo As Long

On Error Resume Next
ff = FreeFile()
Open FileName For Input Lock Read As #ff
Close ff
ErrNo = Err
On Error GoTo 0

Select Case ErrNo
Case 0: IsWorkBookOpen = False
Case 70: IsWorkBookOpen = True
Case Else: Error ErrNo
End Select
End Function