View Single Post
 
Old 05-14-2020, 11:31 PM
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 Multiple entries from ListBox

This is the interface created (I know, the colour!)






And this is the overall coding for the page:


Private Sub CommandButtonCreateCoverPage_Click()
InsertExistingBuildingBlock

Me.Repaint
EUMSCoverPageData.hide
End Sub

Private Sub ListBoxMarking_Click()

End Sub

Private Sub Userform_initialize()
ComboBoxCoverPageOriginator.List = Array("EUMS", "MPCC")
ComboBoxDocumentType.List = Array("WORKING DOCUMENT", "ADMINISTRATIVE DOCUMENT")
ComboBoxEEASNrYYYY.List = Array("2017", "2018", "2019", "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")
ComboBoxPhrase.List = Array("", "Silence Procedure", "Comments", "Compilation of Comments", "Outcome", "EUMC Presentation")
End Sub
Private Function Marking() As String

Dim x As Integer
Marking = ""
For x = 0 To Me.ListBoxMarking.ListCount - 1
If Me.ListBoxMarking.Selected(x) Then
If Marking = "" Then
Marking = Me.ListBoxMarking.List(x)
Else: Marking = "Releasable to " & Marking & ", " & Me.ListBoxMarking.List(x)
End If
End If
Next x
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

Would your solution fit in here?


Thanks a milllion.


Päscal DANIELS
Attached Images
File Type: png Userform.PNG (33.8 KB, 12 views)
Attached Files
File Type: dotm Versatile_Cover_Page_EUR.dotm (188.0 KB, 7 views)
Reply With Quote