View Single Post
 
Old 05-15-2020, 05:23 AM
Paskie_be Paskie_be is offline Windows 10 Office 2016
Novice
 
Join Date: May 2020
Posts: 4
Paskie_be is on a distinguished road
Default

This is now the code, which works, except for one thing, as I get a message Run-time error '5941' on the line in red:
Private Sub CommandButtonCreateCoverPage_Click()
InsertExistingBuildingBlock

Me.Repaint
EUMSCoverPageData.hide
End Sub

Private Sub Userform_initialize()
ComboBoxCoverPageOriginator.List = Array("EUMS", "MPCC")
ComboBoxDocumentType.List = Array("WORKING DOCUMENT", "ADMINISTRATIVE DOCUMENT")
ComboBoxEEASNrYYYY.List = Array("2020", "2021", "2022", "2023", "2024", "2025")
'ComboBoxClass.List = Array("RESTREINT UE/EU RESTRICTED", "CONFIDENTIEL UE/EU CONFIDENTIAL", "SECRET UE/EU SECRET")
ListBoxMarking.List = Array("", "NATO", "The United Nations", "the African Union", "Troop Contributing States")
ListBoxToAcronym.List = Array("PSDC/CSDP", "EUMC", "PSC", "CY+EDA", "NCI")
ComboBoxDir.List = Array("", "Director General", "Deputy Director General", "External Relations", "Synchronisation", "Horizontal Coordination", "CONCAP Directorate", "Intelligence Directorate", "Operations Directorate", "Logistics Directorate", "CIS Directorate", "MPCC")
ComboBoxPrevDocYYYY.List = Array("2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020")
ComboBoxPhrase.List = Array("", "Silence Procedure", "Comments", "Compilation of Comments", "Outcome", "EUMC Presentation")
End Sub
Private Function Marking() As String
Dim lngIndex As Long
Dim arrMarking() As String
For lngIndex = 0 To ListBoxMarking.ListCount - 1
If ListBoxMarking.Selected(lngIndex) Then
Marking = Marking & ListBoxMarking.List(lngIndex) & "|"
End If
Next lngIndex
If Marking <> vbNullString Then
arrMarking = Split(Left(Marking, Len(Marking) - 1), "|")
Marking = vbNullString
Select Case UBound(arrMarking)
Case 0: Marking = arrMarking(0)
Case 1: Marking = arrMarking(0) & " and " & arrMarking(1)
Case Else
For lngIndex = 0 To UBound(arrMarking)
If Not lngIndex = UBound(arrMarking) Then
Marking = Marking & arrMarking(lngIndex) & ", "
Else
Marking = Marking & " and " & arrMarking(lngIndex)
End If
Next lngIndex
End Select
Marking = "Releasable to " & Marking
End If
lbl_Exit:
Exit Function
End Function

Private Sub ComboBoxPhrase_Change()
If ComboBoxPhrase.Value = "" Then
TextBoxSetPhrase.Value = ""
End If
If ComboBoxPhrase.Value = "Silence Procedure" Then
TextBoxSetPhrase.Value = "Delegations will find attached the " & TextBoxDocTitle.Value & ". The document is released under silence procedure expiring at " & DTDLHour & " on " & DTDLDate & "."
End If
If ComboBoxPhrase.Value = "Comments" Then
TextBoxSetPhrase.Value = "Delegations will find attached the " & TextBoxDocTitle & ". Member States' written comments are requested by " & DTDLHour & " on " & DTDLDate & "."
End If
If ComboBoxPhrase.Value = "Compilation of Comments" Then
TextBoxSetPhrase.Value = "Delegations will find attached the compilation of comments on the document in Reference, for discussion in the EUMCWG at " & DTDLHour & " on " & DTDLDate & "."
End If
If ComboBoxPhrase.Value = "Outcome" Then
TextBoxSetPhrase.Value = "Delegations will find attached the outcomes from the " & TextBoxDocTitle & " " & DTDLHour & " on " & DTDLDate & "."
End If
If ComboBoxPhrase.Value = "EUMC Presentation" Then
TextBoxSetPhrase.Value = "This document consists of XXX pages, including this cover page."
End If
End Sub

Private Function Acronym() As String
Dim x As Integer
Acronym = ""
For x = 0 To Me.ListBoxToAcronym.ListCount - 1
If Me.ListBoxToAcronym.Selected(x) Then
If Acronym = "" Then
Acronym = Me.ListBoxToAcronym.List(x)
Else: Acronym = Acronym & "," & Me.ListBoxToAcronym.List(x)
End If
End If
Next x
End Function

Sub InsertExistingBuildingBlock()
Select Case ComboBoxCoverPageOriginator.ListIndex
Case Is = 0
AutoTextToBM ("VCPBookMark01"), ActiveDocument.AttachedTemplate, "EUMSHeader"
AutoTextToBM ("VCPBookMark02"), ActiveDocument.AttachedTemplate, "DocType"
AutoTextToBM ("VCPBookMark03"), ActiveDocument.AttachedTemplate, "DocReferences"
AutoTextToBM ("VCPBookMark04"), ActiveDocument.AttachedTemplate, "AuthorDetails"
AutoTextToBM ("VCPBookMark05"), ActiveDocument.AttachedTemplate, "Page2Title"
Case Else
AutoTextToBM ("VCPBookMark01"), ActiveDocument.AttachedTemplate, "MPCCHeader"
AutoTextToBM ("VCPBookMark02"), ActiveDocument.AttachedTemplate, "DocType"
AutoTextToBM ("VCPBookMark03"), ActiveDocument.AttachedTemplate, "DocReferences"
AutoTextToBM ("VCPBookMark04"), ActiveDocument.AttachedTemplate, "AuthorDetails"
AutoTextToBM ("VCPBookMark05"), ActiveDocument.AttachedTemplate, "Page2Title"
End Select
With ActiveDocument
.Bookmarks("VCPDocYrHdr").Range.Text = ComboBoxEEASNrYYYY.Value
.Bookmarks("VCPDocNrHdr").Range.Text = TextBoxEEASNrNNNN.Value
.Bookmarks("VCPClassHdr").Range.Text = "RESTREINT UE / EU RESTRICTED"
.Bookmarks("VCPMarkingHdr").Range.Text = Marking
.Bookmarks("VCPDocYrFtr").Range.Text = ComboBoxEEASNrYYYY.Value
.Bookmarks("VCPDocNrFtr").Range.Text = TextBoxEEASNrNNNN.Value
.Bookmarks("VCPDirectorateFtr").Range.Text = ComboBoxDir.Value
.Bookmarks("VCPClassFtr").Range.Text = "RESTREINT UE / EU RESTRICTED"
.Bookmarks("VCPMarkingFtr").Range.Text = Marking
.Bookmarks("VCPBookMark02a").Range.Text = ComboBoxDocumentType.Value
.Bookmarks("VCPBookMark02b").Range.Text = DTDocDate.Value
.Bookmarks("VCPBookMark03a").Range.Text = ComboBoxEEASNrYYYY.Value
.Bookmarks("VCPBookMark03b").Range.Text = TextBoxEEASNrNNNN.Value
.Bookmarks("VCPBookMark03c").Range.Text = "RESTREINT UE / EU RESTRICTED"
.Bookmarks("VCPBookMark03d").Range.Text = Marking
.Bookmarks("VCPBookMark03e").Range.Text = Acronym
.Bookmarks("VCPBookMark03f").Range.Text = TextBoxDocTitle.Value
.Bookmarks("VCPBookMark03g").Range.Text = ComboBoxPrevDocYYYY.Value
.Bookmarks("VCPBookMark03h").Range.Text = TextBoxPrevDocNr.Value
.Bookmarks("VCPBookMark04a").Range.Text = ComboBoxDir.Value
.Bookmarks("VCPBookMark04b").Range.Text = TextBoxActionOfficer.Value
.Bookmarks("VCPBookMark04c").Range.Text = TextBoxSetPhrase.Value
.Bookmarks("VCPBookMark05a").Range.Text = TextBoxDocTitle.Value
End With

Dim LblEEASNrNNNN As Range
Set LblEEASNrNNNN = ActiveDocument.Bookmarks("VCPBookMark03b").Range
LblEEASNrNNNN.Text = Me.TextBoxEEASNrNNNN.Value
Dim LblDocTitle As Range
Set LblDocTitle = ActiveDocument.Bookmarks("VCPBookMark03f").Range
LblDocTitle.Text = Me.TextBoxDocTitle.Value
Dim LblPrevDocNr As Range
Set LblPrevDocNr = ActiveDocument.Bookmarks("VCPBookMark03h").Range
LblPrevDocNr.Text = Me.TextBoxPrevDocNr.Value
Dim LblActionOfficer As Range
Set LblActionOfficer = ActiveDocument.Bookmarks("VCPBookMark04b").Range
LblActionOfficer.Text = Me.TextBoxActionOfficer.Value
Dim LblSetPhrase As Range
Set LblSetPhrase = ActiveDocument.Bookmarks("VCPBookMark04c").Range
LblSetPhrase.Text = Me.TextBoxSetPhrase.Value


End Sub
Public Sub AutoTextToBM(strbmName As String, oTemplate As Template, strAutotext As String)
Dim orng As Range
On Error GoTo lbl_Exit
With ActiveDocument
Set orng = .Bookmarks(strbmName).Range
Set orng = oTemplate.AutoTextEntries(strAutotext).Insert(Wher e:=orng, RichText:=True)
.Bookmarks.Add Name:=strbmName, Range:=orng
End With
lbl_Exit:
Exit Sub
End Sub
Reply With Quote