Linking Combobox to multiple bookmarks

scarda · #1 01-19-2015, 10:19 PM

I have set up numerous combo-boxes on my Userform. I connected the combo-box to the relevant bookmark in the document. However there are other bookmarks that I want to populate, based on the combo-box selection but with different wording.

As an example, I have a series of town names each with their own unique description. So when I select a particular town, the description will populate that particular bookmark.
Does anyone have suggestions on what sort of code to use?
Thank you in anticipation

gmayor · #2 01-20-2015, 04:10 AM

Call the following sub to fill your bookmarks with the data required. You can call it from your userform e.g. as follows, replacing the bookmark names and values as appropriate.

Code:

Select Case Me.ComboBoxName.ListIndex
        Case 0
               FillBM "Bookmarkname", "Value to put in bookmark"
               FillBM "AnotherBookmarkname", "Another value to put in bookmark"
         'etc
     Case 1
               FillBM "Bookmarkname", "Value to put in bookmark"
               FillBM "AnotherBookmarkname", "Another value to put in bookmark"
         'etc
     'Repeat for each list index as required
End Select

Code:

Public Sub FillBM(strBMName As String, strValue As String)
Dim orng As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        Set orng = .Bookmarks(strBMName).Range
        orng.Text = strValue
        orng.Bookmarks.Add strBMName
    End With
lbl_Exit:
    Exit Sub
End Sub

gmaxey · #3 01-20-2015, 07:15 AM

Graham has shown you one way. I'm going to Shanghai part of his code to show you another. If you define all of your variables in the combobox (multi-column) you can do it like this:

Code:

Private Sub UserForm_Initialize()
  With Me.ComboBox1
    .AddItem
    .List(.ListCount - 1, 0) = "Cleveland"
    .List(.ListCount - 1, 1) = "A city in northwestern Ohio, USA"
    .AddItem
    .List(.ListCount - 1, 0) = "Cincinnati"
    .List(.ListCount - 1, 1) = "A city in southwestern Ohio, USA"
  End With
End Sub
Private Sub CommandButton1_Click()
  On Error GoTo Err_UserDefined
   FillBM "bmCityName", Me.ComboBox1.Column(0)
   FillBM "bmDescription", Me.ComboBox1.Column(1)
lbl_Exit:
   Unload Me
   Exit Sub
Err_UserDefined:
  FillBM "bmCityName", Me.ComboBox1.Value
  FillBM "bmDescription", "Not defined"
  Resume lbl_Exit
End Sub
Public Sub FillBM(strBMName As String, strValue As String)
Dim oRng As Range
  With ActiveDocument
    On Error GoTo lbl_Exit
    Set oRng = .Bookmarks(strBMName).Range
    oRng.Text = strValue
    oRng.Bookmarks.Add strBMName
  End With
lbl_Exit:
  Exit Sub
End Sub

gmayor · #4 01-20-2015, 08:50 AM

If you are creating the combobox, then Greg's approach works well, but seeing that he has improved on my suggestion, I'll return the favour.

I would set the combobox to display only the first column, by setting the column count (here 2, but you can have more colums as required) and making all but the first column have a width of zero. As it is a combobox and not a list box, I would also add a prompt at the top of the list and set the listindex to that prompt i.e. 0

Code:

Private Sub UserForm_Initialize()
    With Me.ComboBox1
        .ColumnCount = 2
        .ColumnWidths = .Width & ",0"
        .AddItem
        .list(.ListCount - 1, 0) = "[Select City]"
        .list(.ListCount - 1, 1) = "" 
        .AddItem
        .list(.ListCount - 1, 0) = "Cleveland"
        .list(.ListCount - 1, 1) = "A city in northwestern Ohio, USA"
        .AddItem
        .list(.ListCount - 1, 0) = "Cincinnati"
        .list(.ListCount - 1, 1) = "A city in southwestern Ohio, USA"
        .ListIndex = 0
    End With
End Sub

scarda · #5 01-20-2015, 10:48 PM

Thanks Graham and Greg it's much appreciated, I used a combination of both your codes to work this one out.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Form updating Bookmarks - writes to the bookmarks multiple times	PeterPlys	Word VBA	13	01-14-2015 06:41 AM
Linking to Bookmarks	savo	Word	6	04-11-2013 11:04 AM
VBA code to extract specific bookmarks from multiple word files	Rattykins	Word VBA	4	06-27-2012 10:02 PM
Linking multiple choice questions to answers	Microsoftenquirer1000	Word	1	06-11-2012 06:53 AM
linking title across multiple documents	Foxtrot75	Word	1	03-02-2012 03:31 AM