VBA Populating Combobox With Alternative Text

Question

New to VBA so please forgive what may seem a simple question.

Using MS Word I have produced a simple form. I have a ComboBox that is populated via an array. This works fine. What I am trying to achieve is when an option is selected in this ComboBox, an alternative text entry is actually placed into the document at a Bookmark named Advice.

I am using Bookmarks as placemarkers in the Word document. At the moment the value already defined in the ComboBox is being place in the document, instead of the alternative.

Here is my code.

    Dim myArray1() As String
  'Use Split function to return a zero based one dimensional array
  myArray1 = Split("advice for option one|advice for option two|" _
             & "advice for option three", "|")
  'Use List method to populate listbox
  ComboBox2.List = myArray1 
  Exit Sub
  
  If ComboBox2.List = "advice for option one" Then
        Advice.Text = "This piece of text for option one. It's much longer than that in the DropBox, but it is what is needed."
        
    ElseIf ComboBox2.List = "advice for option two" Then
        Advice.Text = "This piece of text for option two. It's much longer than that in the DropBox, but it is what is needed."
        
    ElseIf ComboBox2.List = "advice for option three" Then
        Advice.Text = "This piece of text for option three. It's much longer than that in the DropBox, but it is what is needed."
        
    Else
        Advice.Text = ""
        
    End If
  
lbl_Exit:
  Exit Sub
End Sub

I'm sure I'm doing something really silly that is stopping this from working.

Thanks!

Sorry, I've just realised that I had missed the key part. Unsurprisingly this still doesn't work.

I've provided the rest of it and included your suggestion.

Private Sub CancelBut_Click()
UserForm.Hide
End Sub

Private Sub EnterBut_Click()
Dim number As Range
Set number = ActiveDocument.Bookmarks("number").Range
number.Text = Me.TextBox1.Value
Dim Name As Range
Set Name = ActiveDocument.Bookmarks("Name").Range
Name.Text = Me.TextBox2.Value
Dim Name1 As Range
Set Name1 = ActiveDocument.Bookmarks("Name1").Range
Name1.Text = Me.TextBox2.Value
Dim Address As Range
Set Address = ActiveDocument.Bookmarks("Address").Range
Address.Text = Me.TextBox3.Value
Dim ReportDate As Range
Set ReportDate = ActiveDocument.Bookmarks("ReportDate").Range
ReportDate.Text = Me.TextBox4.Value
Dim Location As Range
Set Location = ActiveDocument.Bookmarks("Location").Range
Location.Text = Me.TextBox5.Value
Dim Reason As Range
Set Reason = ActiveDocument.Bookmarks("Reason").Range
Reason.Text = Me.ComboBox1.Value
Dim Advice As Range
Set Advice = ActiveDocument.Bookmarks("Advice").Range
Advice.Text = Me.ComboBox2.Value
Dim Office As Range
Set Office = ActiveDocument.Bookmarks("Office").Range
Office.Text = Me.TextBox6.Value
Me.Repaint
UserForm.Hide
End Sub

Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
ComboBox2.Visible = True
Else
ComboBox2.Visible = False
End If
End Sub

Private Sub UserForm_Initialize()
Dim myArray() As String
  'Use Split function to return a zero based one dimensional array
  myArray = Split("problem1|problem2|problem3|problem4|" _
             & "problem5|problem6|problem7|problem8|problem9|" _
             & "problem10|problem11|problem12|problem13|problem14", "|")
  'Use List method to populate listbox
  ComboBox1.List = myArray
 
Dim myArray1() As String
  'Use Split function to return a zero based one dimensional array
  myArray1 = Split("advice for option one|advice for option two|" _
             & "advice for option three", "|")
  'Use List method to populate listbox
  ComboBox2.List = myArray1
  
  End Sub
  
  Private Sub ComboBox2_Change()
    Dim Advice As Range
    
    If ActiveDocument.Bookmarks.Exists("Advice") = True Then
        Set Advice = ActiveDocument.Bookmarks("Advice").Range
        
        Select Case ComboBox2.Value
            Case "advice for option one":
                Advice.Text = "This piece of text for option one."
            Case "advice for option two":
                Advice.Text = "This piece of text for option two."
            Case "advice for option three":
                Advice.Text = "This piece of text for option three."
        End Select
        ActiveDocument.Bookmarks.Add "Advice", Advice
    End If
End Sub

You haven't shown the context in which the code is run. What's the sub, and how is it getting called? And you haven't what type of object the Advice variable is or how it got set. That's relevant info. — Peter Constable

Peter Constable Peter Constable · Accepted Answer · 2020-06-22T18:17:22

You haven't shown the context in which the code is run. What's the sub, and how is it getting called? And you haven't what type of object the Advice variable is or how it got set. Your code seems to be setting the combo items and trying to act on a combo selection in the same sub. That won't work.

You should create an event procedure within the form to populate the combo when the dialog is initialized.

Private Sub UserForm_Initialize()
Dim myArray1() As String
  'Use Split function to return a zero based one dimensional array
  myArray1 = Split("advice for option one|advice for option two|" _
             & "advice for option three", "|")
  'Use List method to populate listbox
  ComboBox1.List = myArray1
End Sub

The have another event procedure within the form for a combo change event:

Private Sub ComboBox1_Change()
    Dim Advice As Range
    
    If ActiveDocument.Bookmarks.Exists("advice") = True Then
        Set Advice = ActiveDocument.Bookmarks("advice").Range
        
        Select Case ComboBox1.Value
            Case "advice for option one":
                Advice.Text = "This piece of text for option one."
            Case "advice for option two":
                Advice.Text = "This piece of text for option one."
            Case "advice for option three":
                Advice.Text = "This piece of text for option one."
        End Select
    End If
End Sub

This will replace the bookmark placeholder text with the indicated text. Note: the replacement will get rid of the bookmark as well, so it will only work once unless you reset the bookmark. If you want the bookmark to remain, you need to recreate it. The range object hasn't changed, so you can use that to create the new bookmark:

Private Sub ComboBox1_Change()
    Dim Advice As Range
    
    If ActiveDocument.Bookmarks.Exists("advice") = True Then
        Set Advice = ActiveDocument.Bookmarks("advice").Range
        
        Select Case ComboBox1.Value
            Case "advice for option one":
                Advice.Text = "This piece of text for option one."
            Case "advice for option two":
                Advice.Text = "This piece of text for option two."
            Case "advice for option three":
                Advice.Text = "This piece of text for option three."
        End Select
        ActiveDocument.Bookmarks.Add "advice", Advice
    End If
End Sub

So now, after you select an option in the combo box, the text in the doc will be updated, and the bookmark will be reset to the new text. So, you can make a different choice, and the text will be updated again.

VBA Populating Combobox With Alternative Text

3 Answers