Splashsmith
09-30-2014, 04:14 AM
Hi.
I am a complete novive to VBa, but am trying to design a Word Userform which contains a mixture of text fields and Listboxes. I want to be able to ensure a value is selected from both listboxes. I have coded in a message box which tells the user if the first listbox has no selected value, but when I do the same for the second it works but I end up with the value selected in the first listbox appearing twice at the bookmark in the word document. Is there a way to store the value already selected for the first listbox without it being repeated? My code is as follows:
Private Sub cmdok_Click()
Dim bmks As Bookmarks
Dim bmrange As Range
Dim oDOc As Word.Document
Dim lngIndex As Long
Set bmks = ActiveDocument.Bookmarks
Application.ScreenUpdating = False
Set bmrange = ActiveDocument.Bookmarks("Acty").Range
If lboActy.Value <> "" Then
bmrange.Text = Me.lboActy.Value
Else: MsgBox "You have not selected an Activity type.", vbOKOnly & vbExclamation, "Warning"
Exit Sub
End If
Set bmrange = ActiveDocument.Bookmarks("Ask").Range
If lboAsk.Value <> "" Then
bmrange.Text = Me.lboAsk.Value
Else: MsgBox "You have not selected a type of Ask.", vbOKOnly & vbExclamation, "Warning"
Exit Sub
End If
Set bmrange = ActiveDocument.Bookmarks("Cpgn").Range
bmrange.Text = Me.Campaign.Value
Set bmrange = ActiveDocument.Bookmarks("Mcode").Range
bmrange.Text = Me.Mailcode.Value
Set bmrange = ActiveDocument.Bookmarks("Cman").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Cman1").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Cman2").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Cman3").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Cman4").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Agency").Range
If Me.Agency = "" Then
bmrange.Text = "T.B.C"
Else: bmrange.Text = Me.Agency.Value
End If
Set bmrange = ActiveDocument.Bookmarks("Agency2").Range
If Me.Agency = "" Then
bmrange.Text = "T.B.C"
Else: bmrange.Text = Me.Agency.Value
End If
Set bmrange = ActiveDocument.Bookmarks("Agency3").Range
If Me.Agency = "" Then
bmrange.Text = "T.B.C"
Else: bmrange.Text = Me.Agency.Value
End If
Set oDOc = ActiveDocument
With oDOc
.SelectContentControlsByTitle("Update_Date").Item(1).Range.Text = Date
.SelectContentControlsByTitle("Brief_Date").Item(1).Range.Text = Brief_Date
.SelectContentControlsByTitle("Final_Brief_Date").Item(1).Range.Text = Final_Brief_Date
.SelectContentControlsByTitle("Data_Camp_Man").Item(1).Range.Text = Data_Camp_Man
.SelectContentControlsByTitle("Data_Admin_Date").Item(1).Range.Text = Data_Admin_Date.Value
.SelectContentControlsByTitle("Data_Dumps").Item(1).Range.Text = Data_Dumps.Value
.SelectContentControlsByTitle("Agency_Data").Item(1).Range.Text = Agency_Data.Value
If Late_Supp.Value = Date Then
.SelectContentControlsByTitle("Late_Supp").Item(1).Range.Text = "N/A"
Else: .SelectContentControlsByTitle("Late_Supp").Item(1).Range.Text = Late_Supp.Value
End If
.SelectContentControlsByTitle("Start_Date").Item(1).Range.Text = Start_Date.Value
End With
Application.ScreenUpdating = True
Unload Me
End Sub
Thanks
I am a complete novive to VBa, but am trying to design a Word Userform which contains a mixture of text fields and Listboxes. I want to be able to ensure a value is selected from both listboxes. I have coded in a message box which tells the user if the first listbox has no selected value, but when I do the same for the second it works but I end up with the value selected in the first listbox appearing twice at the bookmark in the word document. Is there a way to store the value already selected for the first listbox without it being repeated? My code is as follows:
Private Sub cmdok_Click()
Dim bmks As Bookmarks
Dim bmrange As Range
Dim oDOc As Word.Document
Dim lngIndex As Long
Set bmks = ActiveDocument.Bookmarks
Application.ScreenUpdating = False
Set bmrange = ActiveDocument.Bookmarks("Acty").Range
If lboActy.Value <> "" Then
bmrange.Text = Me.lboActy.Value
Else: MsgBox "You have not selected an Activity type.", vbOKOnly & vbExclamation, "Warning"
Exit Sub
End If
Set bmrange = ActiveDocument.Bookmarks("Ask").Range
If lboAsk.Value <> "" Then
bmrange.Text = Me.lboAsk.Value
Else: MsgBox "You have not selected a type of Ask.", vbOKOnly & vbExclamation, "Warning"
Exit Sub
End If
Set bmrange = ActiveDocument.Bookmarks("Cpgn").Range
bmrange.Text = Me.Campaign.Value
Set bmrange = ActiveDocument.Bookmarks("Mcode").Range
bmrange.Text = Me.Mailcode.Value
Set bmrange = ActiveDocument.Bookmarks("Cman").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Cman1").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Cman2").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Cman3").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Cman4").Range
bmrange.Text = Me.Manager.Value
Set bmrange = ActiveDocument.Bookmarks("Agency").Range
If Me.Agency = "" Then
bmrange.Text = "T.B.C"
Else: bmrange.Text = Me.Agency.Value
End If
Set bmrange = ActiveDocument.Bookmarks("Agency2").Range
If Me.Agency = "" Then
bmrange.Text = "T.B.C"
Else: bmrange.Text = Me.Agency.Value
End If
Set bmrange = ActiveDocument.Bookmarks("Agency3").Range
If Me.Agency = "" Then
bmrange.Text = "T.B.C"
Else: bmrange.Text = Me.Agency.Value
End If
Set oDOc = ActiveDocument
With oDOc
.SelectContentControlsByTitle("Update_Date").Item(1).Range.Text = Date
.SelectContentControlsByTitle("Brief_Date").Item(1).Range.Text = Brief_Date
.SelectContentControlsByTitle("Final_Brief_Date").Item(1).Range.Text = Final_Brief_Date
.SelectContentControlsByTitle("Data_Camp_Man").Item(1).Range.Text = Data_Camp_Man
.SelectContentControlsByTitle("Data_Admin_Date").Item(1).Range.Text = Data_Admin_Date.Value
.SelectContentControlsByTitle("Data_Dumps").Item(1).Range.Text = Data_Dumps.Value
.SelectContentControlsByTitle("Agency_Data").Item(1).Range.Text = Agency_Data.Value
If Late_Supp.Value = Date Then
.SelectContentControlsByTitle("Late_Supp").Item(1).Range.Text = "N/A"
Else: .SelectContentControlsByTitle("Late_Supp").Item(1).Range.Text = Late_Supp.Value
End If
.SelectContentControlsByTitle("Start_Date").Item(1).Range.Text = Start_Date.Value
End With
Application.ScreenUpdating = True
Unload Me
End Sub
Thanks