Special action required for one item in a listbox

[emsa]

Can anybody please kindly help with the following request. I have a word user form that contains a command button, this displays a vba user form which contains a listbox of 20 items (country abbreviations) and two command buttons, Ok and Clear. A user can select multiple items which in turn are listed in a specific table cell on the word user form, the cell can also be cleared.
I now need to add another item, "other" to the array (the easy bit) and when a user selects this item, an input box pops up and will allow the user to type in text of their choice (country) and then for it to get listed in the same cell location as the other country abbreviations (the hard bit). I would need this to work in situ with the existing listbox items i.e. a user may need to select a few country abbreviations plus include a few countries of their own. Many thanks for any assistance.




Option Explicit
Private Sub CommandButton1_Click()
Dim i As Integer
Dim s As String
'ActiveDocument.ListBox1.Clear
s = ""
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) = True Then
s = s & Me.ListBox1.List(i) & ", "
End If
Next i
'
If Len(s) > 0 Then
s = Mid(s, 1, Len(s) - 2)
ActiveDocument.Unprotect
ActiveDocument.Tables(1).Cell(9, 2).Range.InsertAfter (s)
ActiveDocument.Protect (wdAllowOnlyFormFields)
Else
ClearCell
End If

Unload Me
End Sub
Private Sub CommandButton2_Click()
ClearCell
End Sub
Private Sub UserForm_Initialize()
Dim vArray As Variant
vArray = Array("cs", "da", "de", "et", "el", "en", "es", _
"fr", "it", "lv", "lt", "hu", "mt", "nl", "pl", "pt", "sk", _
"sl", "fi", "sv", "other")
Me.ListBox1.List = vArray

End Sub
Private Function ClearCell()
ActiveDocument.Unprotect
ActiveDocument.Tables(1).Cell(9, 2).Range.Delete
ActiveDocument.Protect (wdAllowOnlyFormFields)
End Function

edited..........line breaks added to code

[matthewspatrick]

Please edit your post and apply VBA tags to your code. That will make it much easier to read.

Two follow-ups:

If people need to add 'other' entries, do you want your form to "remember" those entries so that they appear in the list the next time you run this?
If the answer to #1 above is 'yes', have you considered storing the list in a Registry key or in a template, instead of hard-coding the list?

[fumei]

I am not quite understanding. The user selects a number of items. They then want to select "other". OK. Is what you want to do an adding of the "others" to the text already chosen?

Is the text already chosen already IN the cell, or is the "other" selection made before the text goes into the cell?

[emsa]

Hi Matthew,

The answer to your question is no, entries do not need to be remembered. Selections will mainly be made from the list but there will be times when a non-european member state will be required. I will attach the template

Thanks for your time

Kind Regards

Emsa

[matthewspatrick]

In that case, rather than having an "other" item, I would add a button to your userform that users would click to add an item to the list. The code for that button could use InputBox to prompt the user for the new entry, and then just use the AddItem method for the Listbox to add to the list.

[emsa]

Hi Fumei,

Possible situations,

Currently, a user can select one or a number of items from the list, select ok and they are entered into the table cell on the word user form. They can also be cleared.

A user can select, "other" from the list and then type in a required country.

A user can select items from the list and also other.

[emsa]

I've attached the template if it would help. Many thanks.