ComboBox Variable AND list

Kindyr · #1 08-16-2020, 09:25 AM

I am making a form that I want to auto populate if the names have already been put in at some other point. I am using comboboxes to populate the prefixes of the names such as Mr.|Ms.|Dr. etc using a Split () array. How can I make my combobox set default as the variable and still keep the rest of the list available? For some reason, my code only wants me to have either the list or the variable, not both.

Private Sub UserForm_Initialize()
Dim myPrefixes() As String
myPrefixes = Split(" |Mr. |Ms. |Dr.", "|")
Prefix1.List = myPrefixes

End Sub

Guessed · #2 08-16-2020, 04:44 PM

Your code is filling the values which show in the list. It isn't setting the default value because you haven't asked it to.
You set the default by populating the value after the list is applied

Code:

Private Sub UserForm_Initialize()
Dim myPrefixes() As String
myPrefixes = Split(" |Mr. |Ms. |Dr.", "|")
Prefix1.List = myPrefixes
Prefix1 = "Mr. "
End Sub

It appears you are somewhat inconsistent with your spaces. Is there meant to be a space after Dr.?

Kindyr · #3 08-16-2020, 04:56 PM

Funny story... that is exactly how I had it set up and it was returning an error to me 5 hours ago. I closed it, went about other business, came back to check if what suggested might be in any way different from what I had already put, and it was working with no issues.

As for the inconsistent spaces, I had trimmed the list to make it simplified. The list was much longer and I chopped out about 7 other prefixes, they all have the space after them because it will print to the document with the space between the prefix and the person's name.

Thank you for your help, it actually worked and just made me look at what was already there again instead of scrapping what I had.

Private Sub UserForm_Initialize()

Dim myPrefixes() As String
myPrefixes = Split(" |Mr. |Ms. |Det. |Dr. |Atty. |Rabbi |Ofc. |Sgt. |Cpl. |Maj. ", "|")
Prefix1.List = myPrefixes

Prefix1 = ActiveDocument.Variables("Prefix1")

End Sub

Kindyr · #4 08-16-2020, 05:03 PM

Oh, now I see the problem. If there IS no Prefix1 set, then the code breaks.

Guessed · #5 08-16-2020, 06:39 PM

You may also need to test whether the value in the variable matches one of the available prefix list entries.

Including the space can make this harder to debug and test.

Kindyr · #6 08-16-2020, 06:50 PM

That was my original question, how would I do that? This is the best I've come up with and I HAVE to have the space before |Mr. or the whole thing breaks when there isn't a value created for Prefix1.

Private Sub UserForm_Initialize()
Dim myPrefixes() As String
myPrefixes = Split(" |Mr. |Ms. |Det. |Dr. |Atty. |Rabbi |Ofc. |Sgt. |Cpl. |Maj. ", "|")
Prefix1.List = myPrefixes

If Prefix1.Value = Text Then
Prefix1 = ActiveDocument.Variables("Prefix1")
Else: ActiveDocument.Variables("Prefix1") = "Mr. "
End If

End Sub

gmayor · #7 08-16-2020, 08:22 PM

Personally I would leave out the spaces and add the space when you write to the document variable e.g.

Code:

Private Sub UserForm_Initialize()
Dim myPrefixes() As String
Dim strPrefix As String
Dim i As Integer
    myPrefixes = Split("[Select Item]|Mr.|Ms.|Det.|Dr.|Atty.|Rabbi|Ofc.|Sgt.|Cpl.|Maj.", "|")
    With Prefix1
        .List = myPrefixes
        On Error Resume Next 'the variable is missing from the document
        For i = 0 To .ListCount - 1
            If RTrim(ActiveDocument.Variables("Prefix")) = .List(i) Then
                .ListIndex = i
                Exit For
            End If
        Next i
    End With
End Sub

Private Sub CommandButton1_Click()
    If Prefix1.ListIndex <= 0 Then
        MsgBox "Select the prefix"
        Prefix1.SetFocus
        Exit Sub
    End If
    ActiveDocument.Variables("Prefix") = Prefix1.value & Chr(32)
    Unload Me
End Sub

Kindyr · #8 08-17-2020, 07:27 AM

I won't use the MsgBox there because, well, this is one tiny combobox on an entire userform. If every box had a MsgBox, the user would be clicking MsgBoxes all day

I've put a ControlTipText for each of the boxes so mouse over will show what the box is for.

Also, the Unload Me will cause the Userform to close as soon as it opens in this process since this is the UserForm_Initialize setup. This is just setting up the form. If the user has already set the prefix previously, then it should show it as set, if it isn't set, then it should show it as unset, but in both instances there should be the option to choose from the list as well.

What exactly does the RTrim do for the process?

Sorry if I sound obtuse, I'm still learning and am coming at VBA a little backwards and with no official knowhow.

Guessed · #9 08-17-2020, 04:12 PM

RTrim removes trailing spaces from the right side of a string

The MsgBox is there to ensure that the user has made a selection in that dropdown before clicking the CommandButton1. The user won't see the MsgBox unless they forgot to make a selection before running the macro. This is a validation step that would ensure you have correct data.

Graham gave you two separate macros. The Unload Me is NOT in the initialise macro. It is in the CommandButton1 macro that would be run by the user to transfer the option into the document variables and then close the userform. You could easily remove that line from the CommandButton1 code without changing the other functionality but Graham happened to include it as he probably assumed you would be finished with the userform if you were wanting to pass the values to the document.

The point of the two separate macros is there are two stages for userform functionality
1. Initialise sets the default values on the userform when the user first sees it.
The user can then interact with the userform and change values or click on things - this may or may not involve macros running.

2. The CommandButton action should then:
2.a Validate the user made changes to the form that are acceptable
2.b Transfer the user's provided information to whereever it needs to go
2.c Close the userform

Kindyr · #10 08-17-2020, 04:41 PM

Okay. OH! That makes so much sense. Thank you.

Now, the .SetFocus, does that literally put the cursor on that particular box?

I'm needing the space after the prefix since the form is going to be printing a full name in the end, from other variables along with the prefix. My next project is to figure out how to take a delineated item and throw it at a table. That one is going to be tougher...

gmayor · #11 08-17-2020, 08:09 PM

.SetFocus does indeed put the cursor on the named box.
You don't need the space in the combo-box entries.
If you need a space after the value when it is transferred to the document, add the space there as in my example- Chr(32) is a space or you could use " ".
Putting a value in a table is simple enough, provided you don't have merged cells in the table e.g.

Code:

Dim oTable As Table
Dim oCell As Range
    Set oTable = ActiveDocument.Tables(1)    ' the first table in the document
    Set oCell = oTable.Cell(1, 1).Range    'the first cell in the first row of the table
    oCell.End = oCell.End - 1    'remove the cell end character from the range
    oCell.Text = "The value you want to add to the cell"

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel Combobox List	VBA-User	Excel Programming	4	01-07-2016 02:46 AM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
combobox list gets reset....please help	Deepa Shrestha	Word VBA	1	09-29-2013 09:30 PM
Adding a long list of values to a combobox in Word 2003?	ll4u76	Word VBA	1	04-13-2012 03:37 AM
Populating ComboBox or Drop Down list with contents of a text field	Billy_McSkintos	Word VBA	1	09-13-2011 05:50 AM