List Box Array and coding problem

[LittleAnn]

Hi, I am trying to get use to VBA and have got through alot of my queries
through this forum, so was hoping someone could help me with my last hurdle.

I have a report that I must create a Userform for people to input the
initial Data, that populates throughout the report in various areas, which I
have used REF fields to complete.

My problem is:
In my Userform I have 6 ListBoxes which I need to code so that the user can
pick various Departments/Sections/Disipline/Action etc. but that when they
choose their correct answer that the abbrieviation of that answer actually
appears in the report and not the whole word.

I have followed the "Load Userform Listbox" document that was on
http://gregmaxey.mvps.org but I keep getting errors or else I am putting the
code in wrong!!!

Can someone give me a hand please!!! Thanks

 

[Greg Maxey]

Here is another example:

Option Explicit
Private Sub CommandButton1_Click()
ActiveDocument.Range.InsertAfter Me.ListBox1.Value
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
With Me.ListBox1
.ColumnCount = 2 'Create 2 columns of data. One for the full word, one
for the abbreviation
.ColumnWidths = "30;0" 'show the first column and hide the second
.BoundColumn = 2 'Define the column of data to be used
For i = 1 To 5
.AddItem
.Column(0, i - 1) = Choose(i, "Apple", "Box", "Cup", "Dog", "Elf")
'populate first column
.Column(1, i - 1) = Choose(i, "A", "B", "C", "D", "E") 'populate second
column
Next
End With
End Sub

 

[LittleAnn]

Thanks Greg, but I still seem to be having problems, I have tried a few
things, and the listbox just seems to be inactive, I have followed your
coding exactly but nothing works.


My initial coding was as follows:
Option Explicit

Private Sub UserForm1_Initialize()
Dim myArray1 As Variant
Dim myArray2 As Variant
Dim i As Long
myArray1 = Split("Select Identifier|Company Name 1|Company Name 2|" _
& "Company Name 3|Company Name 4", "|")
myArray2 = Split(" |1|2|3|4", "|")
Identifier.ColumnWidths = "60;0"
For i = 0 To UBound(myArray1)
ListBox.AddItem
ListBox.List(i, 0) = myArray1(i)
ListBox.List(i, 1) = myArray2(i)
Next i
End Sub


Private Sub CmdOK_Click()
Dim oRng As Word.Range
Dim oBM As Bookmarks
Set oBM = ActiveDocument.Bookmarks
Set oRng = oBM("DocumentTitle").Range
oRng.Text = DocumentTitle.Text
oBM.Add "DocumentTitle", oRng
Me.Identifier.TextColumn = 2
Set oRng = oBM("Identifier").Range
oRng.Text = Identifier.Text
oBM.Add "Identifier", oRng
Set oRng = oBM("IssueDATE").Range
oRng.Text = IssueDATE.Text
oBM.Add "IssueDATE", oRng
Set oRng = oBM("IssueSTATUS").Range
oRng.Text = IssueSTATUS.Text
oBM.Add "IssueSTATUS", oRng
Me.Hide
End Sub


But for some reason the list box itself in the Userform is inactive, cant
click into it and it displays nothing. I have the properties set to the
following values:
(Name)= Identifier
BoundColumn= 2
ColumnCount= 2
ColumnHeads= False
ColumnWidths= 30pt; 0pt
Enabled= True
MatchEntry= 0 - fmMatchEntryFirstLetter
MultiSelect= 0- fmMultiSelectSingle
TabIndex= 1
TabStop= True
TextColumn= 30
Visible= True
Width= 55

Not sure if any of the above needs to be changed in order for the listbox to
become active.

Any ideas would be great.!!!

 

[Greg Maxey]

Send me your document and I will look at it.

Thanks Greg, but I still seem to be having problems, I have tried a
few things, and the listbox just seems to be inactive, I have
followed your coding exactly but nothing works.


My initial coding was as follows:
Option Explicit

Private Sub UserForm1_Initialize()
Dim myArray1 As Variant
Dim myArray2 As Variant
Dim i As Long
myArray1 = Split("Select Identifier|Company Name 1|Company Name 2|" _
& "Company Name 3|Company Name 4", "|")
myArray2 = Split(" |1|2|3|4", "|")
Identifier.ColumnWidths = "60;0"
For i = 0 To UBound(myArray1)
ListBox.AddItem
ListBox.List(i, 0) = myArray1(i)
ListBox.List(i, 1) = myArray2(i)
Next i
End Sub


Private Sub CmdOK_Click()
Dim oRng As Word.Range
Dim oBM As Bookmarks
Set oBM = ActiveDocument.Bookmarks
Set oRng = oBM("DocumentTitle").Range
oRng.Text = DocumentTitle.Text
oBM.Add "DocumentTitle", oRng
Me.Identifier.TextColumn = 2
Set oRng = oBM("Identifier").Range
oRng.Text = Identifier.Text
oBM.Add "Identifier", oRng
Set oRng = oBM("IssueDATE").Range
oRng.Text = IssueDATE.Text
oBM.Add "IssueDATE", oRng
Set oRng = oBM("IssueSTATUS").Range
oRng.Text = IssueSTATUS.Text
oBM.Add "IssueSTATUS", oRng
Me.Hide
End Sub


But for some reason the list box itself in the Userform is inactive,
cant click into it and it displays nothing. I have the properties
set to the following values:
(Name)= Identifier
BoundColumn= 2
ColumnCount= 2
ColumnHeads= False
ColumnWidths= 30pt; 0pt
Enabled= True
MatchEntry= 0 - fmMatchEntryFirstLetter
MultiSelect= 0- fmMultiSelectSingle
TabIndex= 1
TabStop= True
TextColumn= 30
Visible= True
Width= 55

Not sure if any of the above needs to be changed in order for the
listbox to become active.

Any ideas would be great.!!!

 

[LittleAnn]

Thanks for this Greg. What is the best way to send you the document for you
to view, as know I cannot attach it to this site.

 

[Graham Mayor]

He won't be up yet, but in the meantime email using using his forename
initial and surname without a space @mvps.org

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[LittleAnn]

Thanks for your help

He won't be up yet, but in the meantime email using using his forename
initial and surname without a space @mvps.org

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>