emsa
09-03-2006, 03:07 AM
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
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