Microsoft Office Forums

Go Back   Microsoft Office Forums > >

 
 
Thread Tools Display Modes
Prev Previous Post   Next Post Next
  #6  
Old 05-15-2020, 05:23 AM
Paskie_be Paskie_be is offline Multiple entries from ListBox Windows 10 Multiple entries from ListBox Office 2016
Novice
Multiple entries from ListBox
 
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
 



Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple entries from ListBox ListBox with multiple columns Kaatje Word VBA 2 02-27-2016 09:37 AM
Link listbox (with multiple columns) to bookmark based on selection dsjk9190 Word VBA 5 01-29-2015 11:28 PM
This is a Userform LIstbox queston: A variable does not set to the value of a listbox CatMan Excel Programming 14 08-18-2014 08:14 PM
Multiple entries from ListBox This is a Userform LIstbox queston: A variable does not set to the value of a listbox CatMan Excel 1 08-08-2014 09:41 AM
Multiple entries from ListBox Delete Multiple Entries dudeabides Office 1 07-04-2011 02:49 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:43 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft