I have a ComboBox that provides various options using short descriptions. What I'm trying to achieve is that when one of these options are selected from the ComboBox, this will read the longer version from the 'change' ComboBox state and populate TextBox2, ready for insertion at the Bookmark defined as 'Reason' when the 'Enter' button is pressed.
Here is what I have at the moment.
'Enter button
Private Sub EnterBut_Click()
Dim oThreat As Range, oHarm As Range, oOpportunity As Range
Dim oRisk As Range, oReason As Range
'check required fields are filled first
If TextBox2.Text = "" Then
MsgBox "Provide reason for further review", vbCritical, "Triage Hub"
TextBox2.SetFocus
Exit Sub
End If
'use FillBM function to write to bookmarks
FillBM "Reason", TextBox2.Text, ComboBox1.Value
Set oReason = Nothing
Unload Me
lbl_Exit:
Exit Sub
End Sub
Private Sub UserForm_initialize()
'Redefine list for reason for proposed further review
Dim myArray = Split("- Select -|Requires further investigation|Urgent review|" _
& "Manageable risk|For Noting Only", "|")
'Use List method to populate listbox
ComboBox1.List = myArray
ComboBox1.ListIndex = 0
lbl_Exit:
Exit Sub
End Sub
'Create text for proposed further review
Private Sub ComboBox1_Change()
Dim Reason As Range
If ActiveDocument.Bookmarks.Exists("Reason") = TRUE Then
Set Reason = ActiveDocument.Bookmarks("Reason").Range
Select Case ComboBox1.Value
Case "Requires further investigation":
Reason.Text = "Lorem ipsum dolor sit amet, consectetur adipiscing elit. In rhoncus, lorem et fringilla dictum, orci orci posuere lacus, ut auctor libero neque non purus. Fusce a commodo ligula."
Case "Urgent review":
Reason.Text = "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
Case "Manageable risk":
Reason.Text = "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
Case "For Noting Only":
Reason.Text = "Sed eu eros ipsum. Ut posuere id magna eu sollicitudin. Nunc suscipit tempor egestas. Class aptent taciti sociosqu ad litora torquent per conubia nostra, per inceptos himenaeos."
End Select
End If
End Sub
Private Sub FillBM(strbmName As String, strValue As String, Optional lngColor As Long = &H80000005)
'Graham Mayor - http://www.gmayor.com
Dim oRng As Range
With ActiveDocument
On Error GoTo lbl_Exit
If .Bookmarks.Exists(strbmName) = TRUE Then
Set oRng = .Bookmarks(strbmName).Range
oRng.Text = strValue
oRng.Font.Color = lngColor
oRng.Bookmarks.Add strbmName
End If
End With
lbl_Exit:
Set oRng = Nothing
Exit Sub
End Sub
Thank you.