Consulting

Results 1 to 15 of 15

Thread: Substitute Value of ComboBox to go to TextBox

  1. #1

    Substitute Value of ComboBox to go to TextBox

    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.

  2. #2
    You don't need the textbox or the ComboBox1_Change macro.


    Option Explicit
    
    'Enter button
    Private Sub EnterBut_Click()
    
    '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
        Select Case ComboBox1.value
            Case "Requires further investigation":
                FillBM "Reason", "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":
                FillBM "Reason", "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
            Case "Manageable risk":
                FillBM "Reason", "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
            Case "For Noting Only":
                FillBM "Reason", "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."
            Case Else
        End Select
        Unload Me
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Sub UserForm_initialize()
    Dim myArray As Variant
        'Redefine list for reason for proposed further review
        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
    
    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
    You cannot however haveTextBox2 and ComboBox1 writing to the same bookmark, unless you combine the texts from both e.g.
     FillBM "Reason", TedxtBox2.text & chr(32) & "Lorem ipsum dolor sit amet, consectetur adipiscing elit. etc"
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    Quote Originally Posted by gmayor View Post
    You don't need the textbox or the ComboBox1_Change macro.


    Option Explicit
    
    'Enter button
    Private Sub EnterBut_Click()
    
    '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
        Select Case ComboBox1.value
            Case "Requires further investigation":
                FillBM "Reason", "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":
                FillBM "Reason", "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
            Case "Manageable risk":
                FillBM "Reason", "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
            Case "For Noting Only":
                FillBM "Reason", "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."
            Case Else
        End Select
        Unload Me
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Sub UserForm_initialize()
    Dim myArray As Variant
        'Redefine list for reason for proposed further review
        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
    
    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
    You cannot however haveTextBox2 and ComboBox1 writing to the same bookmark, unless you combine the texts from both e.g.
     FillBM "Reason", TedxtBox2.text & chr(32) & "Lorem ipsum dolor sit amet, consectetur adipiscing elit. etc"

    Bloomin' annoying that this site keeps going down!

    Thanks for the previous code suggestion. Nearly worked, but not quite. I am hoping that the text from the 'Case' populates the userform in TextBox2, prior to pressing the "Enter" button and populating BookMark 'CaseReview'. hoping that this is possible?

    Here is my full code for the UserForm.

    Option Explicit
    
    'Cancel button
    Private Sub CancelBut_Click()
        Unload Me
    End Sub
    
    'Reset button
    Private Sub ResetBut_Click()
        Dim ctl         As MSForms.Control
        
        For Each ctl In Controls
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = ""
                Case "CheckBox", "OptionButton", "ToggleButton"
                    ctl.Value = True
                Case "ComboBox", "ListBox"
                    ctl.ListIndex = 0
            End Select
        Next ctl
    End Sub
    
    'Enter button
    Private Sub EnterBut_Click()
        Dim oThreat     As Range, oHarm As Range, oOpportunity As Range
        Dim oRisk       As Range, oReason        As Range, oCaseReview        As Range
        
        'Check required fields are filled out
        
        If ComboBox2.ListIndex = 0 Then
            MsgBox "Select threat level", vbCritical, "Triage Hub"
            ComboBox2.SetFocus
            Exit Sub
        End If
        
        If ComboBox3.ListIndex = 0 Then
            MsgBox "Select harm level", vbCritical, "Triage Hub"
            ComboBox3.SetFocus
            Exit Sub
        End If
        
        If ComboBox4.ListIndex = 0 Then
            MsgBox "Select opportunity level", vbCritical, "Triage Hub"
            ComboBox4.SetFocus
            Exit Sub
        End If
        
        If ComboBox5.ListIndex = 0 Then
            MsgBox "Select risk level", vbCritical, "Triage Hub"
            ComboBox5.SetFocus
            Exit Sub
        End If
        
        
        'use FillBM function to write to bookmarks
        FillBM "CaseReview", TextBox1.Text
        FillBM "Reason", TextBox2.Text
        FillBM "Threat1", TextBox3.Text
        FillBM "Harm1", TextBox4.Text
        FillBM "Opportunity1", TextBox5.Text
        FillBM "Risk1", TextBox6.Text
        FillBM "Threat", ComboBox2.Value, ComboBox2.BackColor
        FillBM "Harm", ComboBox3.Value, ComboBox3.BackColor
        FillBM "Opportunity", ComboBox4.Value, ComboBox4.BackColor
        FillBM "Risk", ComboBox5.Value, ComboBox5.BackColor
        
        Set oThreat = Nothing
        Set oHarm = Nothing
        Set oOpportunity = Nothing
        Set oRisk = Nothing
        Set oReason = Nothing
        Set oCaseReview = Nothing
        Unload Me
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Sub ComboBox2_Change()
        With ComboBox2
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Sub ComboBox3_Change()
        With ComboBox3
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Sub ComboBox4_Change()
        With ComboBox4
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Sub ComboBox5_Change()
        With ComboBox5
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    'Select threat level
    Dim myArray()       As String
    'Create list of grades for threat
    myArray = Split("- Select -|High|Medium|Low", "|")
    'Use List method to populate listbox
    ComboBox2.List = myArray
    ComboBox2.ListIndex = 0
    'Use List method to populate listbox
    ComboBox3.List = myArray
    ComboBox3.ListIndex = 0
    'Use List method to populate listbox
    ComboBox4.List = myArray
    ComboBox4.ListIndex = 0
    'Use List method to populate listbox
    ComboBox5.List = myArray
    ComboBox5.ListIndex = 0
    
    lbl_Exit:
    Exit Sub
    End Sub
    'Create text for review
    Select Case ComboBox1.Value
        Case "Requires further investigation":
            FillBM "Reason", "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":
            FillBM "Reason", "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
        Case "Manageable risk":
            FillBM "Reason", "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
        Case "For Noting Only":
            FillBM "Reason", "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."
        Case Else
    End Select
    Unload Me
    lbl_Exit:
    Exit Sub
    End Sub
    
    Private Sub UserForm_initialize()
        Dim myArray     As Variant
        'Redefine list for reason of proposed further review
        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
    
    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
    Thanks again!

  4. #4
    I've realised that I haven't said what is not working.

    ComboBox 2 to 5 are blank when trying to select an option. I cannot see why as the code appears exactly as previously advised. ComboBox 1 has its dropdown list populated, but doesn't select the alternate text and put it into Textbox 2.

    Thanks!

  5. #5
    You have an assortment of code unconnected with any sub in the userform, some of which should be in the Userform_Initialize sub and some of it should be in the EnterBut_Click sub.

    'Select threat level
    Dim myArray() As String
    'Create list of grades for threat
    myArray = Split("- Select -|High|Medium|Low", "|")
    'Use List method to populate listbox
    ComboBox2.List = myArray
    ComboBox2.ListIndex = 0
    'Use List method to populate listbox
    ComboBox3.List = myArray
    ComboBox3.ListIndex = 0
    'Use List method to populate listbox
    ComboBox4.List = myArray
    ComboBox4.ListIndex = 0
    'Use List method to populate listbox
    ComboBox5.List = myArray
    ComboBox5.ListIndex = 0
    
    lbl_Exit:
    Exit Sub
    End Sub
    'Create text for review
    Select Case ComboBox1.value
        Case "Requires further investigation":
            FillBM "Reason", "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":
            FillBM "Reason", "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
        Case "Manageable risk":
            FillBM "Reason", "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
        Case "For Noting Only":
            FillBM "Reason", "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."
        Case Else
    End Select
    Unload Me
    lbl_Exit:
    Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    I'm sorry, but I am not understanding. It's probably me being slow on the uptake!

    I've used the ComboBox color change routine as suggested here:- http://www.vbaexpress.com/forum/showthread.php?67697-Change-Background-Colour-Of-ComboBox-Depending-On-What-Option-Is-Selected

    This is to highlight the threat, harm, risk and opportunity levels selected from ComboBox 2 through 5.

    Then added your suggestion for Case / ComboBox so that a shorter description is selected from the ComboBox 1, with the alternate wording appearing 'live' in Textbox 2. This is so that the user can then add to or make minor alterations to this text before commiting it to the form when pressing the 'Enter' button.

    Thanks again, gmayor.

  7. #7
    You cannot have random bits of orphan code in a module. The section I highlighted is unconnected to any of the subs above it and will produce an error condition.

    To see the problem, click Debug > Compile Normal and watch the sparks fly.

    If you want to setup the form, the setup code goes in Sub Userform_Initialize

    If you want to process document from the form the code to goes in Sub EnterBut_Click

    Each sub should end at End Sub - you cannot add stuff below that that is not a sub in its own right, which is why it is not working.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  8. #8
    I think that I'm nearly there as I've managed to lose the errors. Unfortunately the ComboBoxes are all empty when the UserForm is run.


    Option Explicit
    
    'Cancel button
    Private Sub CancelBut_Click()
        Unload Me
    End Sub
    
    'Reset button
    Private Sub ResetBut_Click()
        Dim ctl         As MSForms.Control
        
        For Each ctl In Controls
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = ""
                Case "CheckBox", "OptionButton", "ToggleButton"
                    ctl.Value = True
                Case "ComboBox", "ListBox"
                    ctl.ListIndex = 0
            End Select
        Next ctl
    End Sub
    
    'Enter button
    Private Sub EnterBut_Click()
        Dim oThreat     As Range, oHarm As Range, oOpportunity As Range
        Dim oRisk       As Range, oCaseReview        As Range, oReason        As Range
        
        'Check required fields are filled out 
        If TextBox2.Text = "" Then
            MsgBox "Provide reason For further review", vbCritical, "Triage Hub"
            TextBox2.SetFocus
            Exit Sub
        End If
        
        If ComboBox2.ListIndex = 0 Then
            MsgBox "Select threat level", vbCritical, "Triage Hub"
            ComboBox2.SetFocus
            Exit Sub
        End If
        
        If ComboBox3.ListIndex = 0 Then
            MsgBox "Select harm level", vbCritical, "Triage Hub"
            ComboBox3.SetFocus
            Exit Sub
        End If
        
        If ComboBox4.ListIndex = 0 Then
            MsgBox "Select opportunity level", vbCritical, "Triage Hub"
            ComboBox4.SetFocus
            Exit Sub
        End If
        
        If ComboBox5.ListIndex = 0 Then
            MsgBox "Select risk level", vbCritical, "Triage Hub"
            ComboBox5.SetFocus
            Exit Sub
        End If
        
        'Create text for review
        'use FillBM function to write to bookmarks
        Dim myArray     As Variant
        'Redefine list for reason for proposed further review
        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
        
        Select Case ComboBox1.Value
            Case "Requires further investigation":
                FillBM "Reason", "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":
                FillBM "Reason", "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
            Case "Manageable risk":
                FillBM "Reason", "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
            Case "For Noting Only":
                FillBM "Reason", "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."
            Case Else
        End Select
        
    lbl_Exit:
    End Sub
    
    
     'Redefine list for threat level
    'Select threat level
    myArray()           As String
    'Create list of grades for threat
    myArray = Split("- Select -|High|Medium|Low", "|")
    'Use List method to populate listbox
    ComboBox2.List = myArray
    ComboBox2.ListIndex = 0
    'Use List method to populate listbox
    ComboBox3.List = myArray
    ComboBox3.ListIndex = 0
    'Use List method to populate listbox
    ComboBox4.List = myArray
    ComboBox4.ListIndex = 0
    'Use List method to populate listbox
    ComboBox5.List = myArray
    ComboBox5.ListIndex = 0
    
    lbl_Exit:
    Exit Sub
    End Sub
    
    'use FillBM function to write to bookmarks
    FillBM "CaseReview", TextBox1.Text
    FillBM "Reason", TextBox2.Text
    FillBM "Threat1", TextBox3.Text
    FillBM "Harm1", TextBox4.Text
    FillBM "Opportunity1", TextBox5.Text
    FillBM "Risk1", TextBox6.Text
    FillBM "Threat", ComboBox2.Value, ComboBox2.BackColor
    FillBM "Harm", ComboBox3.Value, ComboBox3.BackColor
    FillBM "Opportunity", ComboBox4.Value, ComboBox4.BackColor
    FillBM "Risk", ComboBox5.Value, ComboBox5.BackColor
    
    Set oThreat = Nothing
    Set oHarm = Nothing
    Set oOpportunity = Nothing
    Set oRisk = Nothing
    Set oReason = Nothing
    Set oCaseReview = Nothing
    Unload Me
    lbl_Exit:
    Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox2_Change()
        With ComboBox2
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox3_Change()
        With ComboBox3
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox4_Change()
        With ComboBox4
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox5_Change()
        With ComboBox5
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    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

  9. #9
    You have lost the Userrform_Initialize sub that populated them and you still have the orphan code below the first part of which should be in that that should be in the missing sub. The rest should be in the enter button sub. Macros start with Sub and end with End Sub (or Function and end with End Function). You have two End Subs with no Sub.

     'Redefine list for threat level
    'Select threat level
    myArray()           As String
    'Create list of grades for threat
    myArray = Split("- Select -|High|Medium|Low", "|")
    'Use List method to populate listbox
    ComboBox2.List = myArray
    ComboBox2.ListIndex = 0
    'Use List method to populate listbox
    ComboBox3.List = myArray
    ComboBox3.ListIndex = 0
    'Use List method to populate listbox
    ComboBox4.List = myArray
    ComboBox4.ListIndex = 0
    'Use List method to populate listbox
    ComboBox5.List = myArray
    ComboBox5.ListIndex = 0
    
    lbl_Exit:
    Exit Sub
    End Sub
    
    'use FillBM function to write to bookmarks
    FillBM "CaseReview", TextBox1.Text
    FillBM "Reason", TextBox2.Text
    FillBM "Threat1", TextBox3.Text
    FillBM "Harm1", TextBox4.Text
    FillBM "Opportunity1", TextBox5.Text
    FillBM "Risk1", TextBox6.Text
    FillBM "Threat", ComboBox2.Value, ComboBox2.BackColor
    FillBM "Harm", ComboBox3.Value, ComboBox3.BackColor
    FillBM "Opportunity", ComboBox4.Value, ComboBox4.BackColor
    FillBM "Risk", ComboBox5.Value, ComboBox5.BackColor
    
    Set oThreat = Nothing
    Set oHarm = Nothing
    Set oOpportunity = Nothing
    Set oRisk = Nothing
    Set oReason = Nothing
    Set oCaseReview = Nothing
    Unload Me
    lbl_Exit:
    Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  10. #10
    Managed to sort out my error issues and the form in its current state is working.

    Option Explicit
    
    'Cancel button
    Private Sub CancelBut_Click()
        Unload Me
    End Sub
    
    'Reset button
    Private Sub ResetBut_Click()
        Dim ctl         As MSForms.Control
        
        For Each ctl In Controls
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = ""
                Case "CheckBox", "OptionButton", "ToggleButton"
                    ctl.Value = TRUE
                Case "ComboBox", "ListBox"
                    ctl.ListIndex = 0
            End Select
        Next ctl
    End Sub
    
    'Enter button
    Private Sub EnterBut_Click()
        Dim oThreat     As Range, oHarm As Range, oOpportunity As Range
        Dim oRisk       As Range, oCaseReview        As Range, oReason        As Range
        
        'Check required fields are filled out
        
        If TextBox2.Text = "" Then
            MsgBox "Provide reason For further review", vbCritical, "Triage Hub"
            TextBox2.SetFocus
            Exit Sub
        End If
        
        If ComboBox2.ListIndex = 0 Then
            MsgBox "Select threat level", vbCritical, "Triage Hub"
            ComboBox2.SetFocus
            Exit Sub
        End If
        
        If ComboBox3.ListIndex = 0 Then
            MsgBox "Select harm level", vbCritical, "Triage Hub"
            ComboBox3.SetFocus
            Exit Sub
        End If
        
        If ComboBox4.ListIndex = 0 Then
            MsgBox "Select opportunity level", vbCritical, "Triage Hub"
            ComboBox4.SetFocus
            Exit Sub
        End If
        
        If ComboBox5.ListIndex = 0 Then
            MsgBox "Select risk level", vbCritical, "Triage Hub"
            ComboBox5.SetFocus
            Exit Sub
        End If
        
        'use FillBM function to write to bookmarks
        FillBM "CaseReview", TextBox1.Text
        FillBM "Reason", TextBox2.Text
        FillBM "Threat1", TextBox3.Text
        FillBM "Harm1", TextBox4.Text
        FillBM "Opportunity1", TextBox5.Text
        FillBM "Risk1", TextBox6.Text
        FillBM "Threat", ComboBox2.Value, ComboBox2.BackColor
        FillBM "Harm", ComboBox3.Value, ComboBox3.BackColor
        FillBM "Opportunity", ComboBox4.Value, ComboBox4.BackColor
        FillBM "Risk", ComboBox5.Value, ComboBox5.BackColor
        
        Set oThreat = Nothing
        Set oHarm = Nothing
        Set oOpportunity = Nothing
        Set oRisk = Nothing
        Set oReason = Nothing
        Set oCaseReview = Nothing
        Unload Me
        lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox2_Change()
        With ComboBox2
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
        lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox3_Change()
        With ComboBox3
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
        lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox4_Change()
        With ComboBox4
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
        lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox5_Change()
        With ComboBox5
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
        lbl_Exit:
        Exit Sub
    End Sub
    
    Private Sub UserForm_initialize()
        'Create text for review
        'use FillBM function to write to bookmarks
        Dim myArray     As Variant
        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
        
        Select Case ComboBox1.Value
            Case "Requires further investigation":
                FillBM "Reason", "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":
                FillBM "Reason", "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
            Case "Manageable risk":
                FillBM "Reason", "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
            Case "For Noting Only":
                FillBM "Reason", "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."
            Case Else
        End Select
        
        'Redefine list for reason for threat level
        'Select threat level
        
        'Create list of grades for threat
        myArray = Split("- Select -|High|Medium|Low", "|")
        'Use List method to populate listbox
        ComboBox2.List = myArray
        ComboBox2.ListIndex = 0
        'Use List method to populate listbox
        ComboBox3.List = myArray
        ComboBox3.ListIndex = 0
        'Use List method to populate listbox
        ComboBox4.List = myArray
        ComboBox4.ListIndex = 0
        'Use List method to populate listbox
        ComboBox5.List = myArray
        ComboBox5.ListIndex = 0
        
        lbl_Exit:
        Exit Sub
    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
    The final piece of the jigsaw is that I'd like the option chosen in ComboBox 1 to populate TextBox 2 before it is commited with the 'Enter' button. The reason is that the substituted text might need to be added to first. Also, is it possible to be able to select more than one option from the ComboBox?

    Thanks!

  11. #11
    If you want to select more than one item, you need a list box instead. For the text box
    Private Sub ComboBox1_Change()
       If ComboBox1.ListIndex > 0 then
             Textbox2.text = ComboBox1.Value
       End If
        lbl_Exit:
       Exit Sub
    End Sub
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  12. #12
    Thanks, gmayor.

    When selecting ComboBox1, the entry from the dropdown populates TextBox2 instead of the 'case' value.

  13. #13
    That's what the code does ie. what you asked for. Change the ComboBox1_Change macro to perform the tasks according to the selection e.g

    Private Sub ComboBox1_Change()
            Select Case ComboBox1.value
                Case "Requires further investigation":
                    TextBox2.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":
                    TextBox2.Text = "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
                Case "Manageable risk":
                    TextBox2.Text = "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
                Case "For Noting Only":
                    TextBox2.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."
                Case Else
                    TextBox2.Text = ""
            End Select
    lbl_Exit:
        Exit Sub
    End Sub
    I am not sure why you have the similar code in the initialization macro as it is doing nothing there.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  14. #14
    Thanks for your patience gmayor. I think I did not explain very well what I was trying to achieve.


    When an option from Combobox1 is selected :-

    Requires further investigation, Urgent review, Manageable risk, For Noting Only

    It is the TextBox2 'change' text that is shown in the UserForm.

    "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." or

    "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper." etc.

    This is to enable the person to make some final tweaks to the text in the UserForm TextBox2 before committing it to the document.


    Option Explicit
    
    'Cancel button
    Private Sub CancelBut_Click()
        Unload Me
    End Sub
    
    'Reset button
    Private Sub ResetBut_Click()
        Dim ctl         As MSForms.Control
        
        For Each ctl In Controls
            Select Case TypeName(ctl)
                Case "TextBox"
                    ctl.Text = ""
                Case "CheckBox", "OptionButton", "ToggleButton"
                    ctl.Value = True
                Case "ComboBox", "ListBox"
                    ctl.ListIndex = 0
            End Select
        Next ctl
    End Sub
    
    'Enter button
    Private Sub EnterBut_Click()
        Dim oThreat     As Range, oHarm As Range, oOpportunity As Range
        Dim oRisk       As Range, oCaseReview        As Range, oReason        As Range
        
        'Check required fields are filled out
        
        If TextBox2.Text = "" Then
            MsgBox "Provide reason For further review", vbCritical, "Triage Hub"
            TextBox2.SetFocus
            Exit Sub
        End If
        
        If ComboBox2.ListIndex = 0 Then
            MsgBox "Select threat level", vbCritical, "Triage Hub"
            ComboBox2.SetFocus
            Exit Sub
        End If
        
        If ComboBox3.ListIndex = 0 Then
            MsgBox "Select harm level", vbCritical, "Triage Hub"
            ComboBox3.SetFocus
            Exit Sub
        End If
        
        If ComboBox4.ListIndex = 0 Then
            MsgBox "Select opportunity level", vbCritical, "Triage Hub"
            ComboBox4.SetFocus
            Exit Sub
        End If
        
        If ComboBox5.ListIndex = 0 Then
            MsgBox "Select risk level", vbCritical, "Triage Hub"
            ComboBox5.SetFocus
            Exit Sub
        End If
        
        'use FillBM function to write to bookmarks
        FillBM "CaseReview", TextBox1.Text
        FillBM "Reason", TextBox2.Text
        FillBM "Threat1", TextBox3.Text
        FillBM "Harm1", TextBox4.Text
        FillBM "Opportunity1", TextBox5.Text
        FillBM "Risk1", TextBox6.Text
        FillBM "Threat", ComboBox2.Value, ComboBox2.BackColor
        FillBM "Harm", ComboBox3.Value, ComboBox3.BackColor
        FillBM "Opportunity", ComboBox4.Value, ComboBox4.BackColor
        FillBM "Risk", ComboBox5.Value, ComboBox5.BackColor
        
        Set oThreat = Nothing
        Set oHarm = Nothing
        Set oOpportunity = Nothing
        Set oRisk = Nothing
        Set oReason = Nothing
        Set oCaseReview = Nothing
        Unload Me
    lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox2_Change()
        With ComboBox2
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox3_Change()
        With ComboBox3
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox4_Change()
        With ComboBox4
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    'Change colour of dropdown
    Private Sub ComboBox5_Change()
        With ComboBox5
            Select Case .ListIndex
                Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
                Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
                Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
                Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
            End Select
        End With
    lbl_Exit:
        Exit Sub
    End Sub
    
    Private Sub TextBox2_Change()
    
    End Sub
    
    Private Sub UserForm_initialize()
        'Create text for review
        'use FillBM function to write to bookmarks
        Dim myArray     As Variant
        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
        
        'Redefine list for reason for threat level
        'Select threat level
        
        'Create list of grades for threat
        myArray = Split("- Select -|High|Medium|Low", "|")
        'Use List method to populate listbox
        ComboBox2.List = myArray
        ComboBox2.ListIndex = 0
        'Use List method to populate listbox
        ComboBox3.List = myArray
        ComboBox3.ListIndex = 0
        'Use List method to populate listbox
        ComboBox4.List = myArray
        ComboBox4.ListIndex = 0
        'Use List method to populate listbox
        ComboBox5.List = myArray
        ComboBox5.ListIndex = 0
        
    lbl_Exit:
        Exit Sub
    End Sub
    Private Sub ComboBox1_Change()
        Select Case ComboBox1.Value
            Case "Requires further investigation":
                TextBox2.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":
                TextBox2.Text = "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
            Case "Manageable risk":
                TextBox2.Text = "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
            Case "For Noting Only":
                TextBox2.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."
            Case Else
                TextBox2.Text = ""
        End Select
        
        If ComboBox1.ListIndex > 0 Then
            TextBox2.Text = ComboBox1.Value
        End If
    lbl_Exit:
        Exit Sub
    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

  15. #15
    I've managed to get my act together and sort it out.


    Private Sub ComboBox1_Change()
        If ComboBox1.ListIndex > 0 Then
            TextBox2.Text = ComboBox1.Value
        End If
        Select Case ComboBox1.Value
            Case "Requires further investigation":
                TextBox2.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":
                TextBox2.Text = "Proin sed nisl enim. Cras in nisl tempus, scelerisque mi id, vulputate arcu. Duis nec mi ac lorem pretium semper."
            Case "Manageable risk":
                TextBox2.Text = "Fusce eu nisi sollicitudin, pharetra nibh sed, vestibulum neque. Praesent a auctor turpis. Mauris posuere vitae justo ac mollis."
            Case "For Noting Only":
                TextBox2.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."
            Case Else
                TextBox2.Text = ""
        End Select
        
    lbl_Exit:
        Exit Sub
    End Sub
    Huge thanks to gmayor!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •