Customized ComboBox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg Maxey - 08 Sep 2007 12:00 GMT

I have been playing around with some code to allow a user to type in their
own data in a combobox and then make that entry permanently a part of the
combobox list (i.e., whenever the form is called in the future) .

My idea (which may be a stupid one) is to store the user defined entries as
variables in the document. Here is the code:

Private Sub UserForm_Initialize() '
Dim oVar As Word.Variable
Dim oVars As Word.Variables
Set oVars = ActiveDocument.Variables
With Me.ComboBox1
.AddItem "Greg"
.AddItem "Susan"
For Each oVar In oVars
If InStr(oVar.Name, "AI") = 1 Then
.AddItem oVar.Value
End If
Next oVar
SortList
End With
End Sub

Sub SortList()
Dim myArray() As String
Dim i As Long
Dim lngCount As Long
lngCount = Me.ComboBox1.ListCount
ReDim myArray(lngCount)
For i = 0 To Me.ComboBox1.ListCount - 1
myArray(i) = Me.ComboBox1.List(i)
Next i
Me.ComboBox1.Clear
WordBasic.SortArray myArray
Me.ComboBox1.List = myArray
End Sub

Private Sub CommandButton1_Click()
Dim i As Long
Dim bNewItem As Boolean
Dim oVar As Word.Variable
Dim oVars As Word.Variables
Dim lngCount As Long
bNewItem = True
With Me.ComboBox1
For i = 0 To .ListCount - 1
If .Value = .List(i) Then
bNewItem = False
Exit For
End If
Next i
If bNewItem Then
.AddItem .Value
Set oVars = ActiveDocument.Variables
For Each oVar In oVars
If InStr(oVar.Name, "AI") = 1 Then lngCount = lngCount + 1
Next oVar
oVars.Add "AI" & lngCount + 1, .Value
End If
End With
SortList
MsgBox Me.Combobox1.Value
Me.hide
End Sub

It seems to be working but I was wondering if I am driving a tack with a
sledge hammer here. Does anyone know of a more efficient method of doing
this? I also have a question about the SortList routine. At first I tried:

Sub SortList()
Dim myArray
myArray = Me.ComboBox1.List
Me.ComboBox1.Clear
WordBasic.SortArray myArray
Me.ComboBox1.List = myArray
End Sub

I didn't get any errors, but the list isn't sorted either.

I then added a few message boxes and I am seeing some behaviour that I
really don't understand. When I step through this code the first message
box correctly reports the number of items I expect to be in the array.
However, the next line generates a run time error 9 "subscript out of range"

Since the code above doesn't generate an error and does repopulate the
combobox with an "unsorted" list, it seems that the values in the array are
valid so I don't see why and error occurs in the following code. Maybe
someone can tell me why?

Sub SortList()
Dim myArray
myArray = Me.ComboBox1.List
Me.ComboBox1.Clear
WordBasic.SortArray myArray
MsgBox UBound(myArray)
MsgBox myArray(UBound(myArray))
Me.ComboBox1.List = myArray
End Sub

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Reply to this Message

Helmut Weber - 09 Sep 2007 12:15 GMT

Hi Greg,

nothing wrong with storing some values
in doc-variables, I think.

The strange thing I came across is
that assigning a list to a variant
creates a 2 dimensional array!

All I could find was a small hint there by David Sisson
http://tinyurl.com/yoelvj

Never heard about that before. But its true.

Private Sub CommandButton3_Click()
Dim myArray
myArray = Me.ComboBox1.List
' MsgBox myArray(1) ' no way
' MsgBox myArray(2) ' no way
MsgBox myArray(1, 0) ' !!!
MsgBox myArray(2, 0) ' !!!
End Sub

Don't ask me how to sort a 2 dimensional array
using wordbasic. I usually do the sorting myself,
using very much frowned on bubble sort,
but it is reliable and entirely under my control.

Viel Spass!
(Lot of fun.)

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

Jay Freedman - 09 Sep 2007 14:22 GMT

Hi Helmut,

I hadn't thought about it before, but the dimension of the array does
make sense when you know that the .List of a combobox or listbox can
have multiple columns. That means the .List must be two-dimensional.
In the usual case when you assign a one-dimensional list to it, the
second dimension is defined as "0 to 0", but it's still there. Then
when you assign the .List to a Variant variable, the second dimension
is just carried along.

To sort a two-dimensional array, you have to pick one of the columns
to sort (usually the first) and use that element of each row in the
swap comparisons of the sorting algorithm.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

>Hi Greg,
>
[quoted text clipped - 26 lines]
>Viel Spass!
>(Lot of fun.)

Reply to this Message

Greg Maxey - 09 Sep 2007 14:48 GMT

Jay, Helmut

Yes, after looking at Jonathan West's article on SortArray I think I
understand why .List doesn't work with the WordBasic.SortArray method.
Looking at the VBA help, .List returns a variant two dimensional array. As
Jonathan explains, SortArray doesn't work with variants.

I did figure out a way to use .List in my code with the Bubble Sort
Algorithm. Thanks.

Private Sub UserForm_Initialize() '
Dim oVar As Word.Variable
Dim oVars As Word.Variables
Set oVars = ActiveDocument.Variables
With Me.ComboBox1
.AddItem "Greg"
.AddItem "Susan"
.AddItem "Doria"
For Each oVar In oVars
If InStr(oVar.Name, "AI") = 1 Then
.AddItem oVar.Value
End If
Next oVar
'SortList
Me.ComboBox1.List = BubbleSort(Me.ComboBox1.List)
End With
End Sub

Function BubbleSort(TempArray As Variant) As Variant
Dim Temp As Variant
Dim i As Integer
Dim bolExchange As Integer
Do
bolExchange = False
'Loop through each element in the array.
For i = LBound(TempArray) To UBound(TempArray) - 1
'If element > next element then exchange the two elements.
If LCase(TempArray(i, 0)) > LCase(TempArray(i + 1, 0)) Then
bolExchange = True
Temp = TempArray(i, 0)
TempArray(i, 0) = TempArray(i + 1, 0)
TempArray(i + 1, 0) = Temp
End If
Next i
Loop While bolExchange
BubbleSort = TempArray
End Function

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Hi Helmut,
>
[quoted text clipped - 40 lines]
>> Viel Spass!
>> (Lot of fun.)

Reply to this Message

Helmut Weber - 09 Sep 2007 21:34 GMT

Hi Jay,

>the .List of a combobox or listbox can have multiple columns.

sure.

But if it has only one column,
why then create a two dimensional array?

Does a 2 column listbox create a 3 dimensional array?

I could try it out myself, but I'm kind of exhausted
and may be you just happen to know.

Signature

Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Reply to this Message

Greg Maxey - 09 Sep 2007 23:05 GMT

Helmut, Jay

The help file says the row and column are required in the .List syntax.
Syntax
object.List( row, column ) [= Variant]

The List property syntax has these parts:

Part Description
object Required. A valid object.
row Required. An integer with a range from 0 to one less than the
number of entries in the list.
column Required. An integer with a range from 0 to one less than the
number of columns.
Variant Optional. The contents of the specified entry in the ListBox
or ComboBox.

So even though you can use a one dimensional array and .List to populate a
combobox. The value returned by .List is two dimensional:

Private Sub UserForm_Initialize()
Dim myArray() As String
Dim i as Long
Dim myArray1
myArray = Split("A|B|C|D|E", "|")
Me.ComboBox1.List = myArray 'Populate the combobox with a one dimensional
array
myArray1 = Me.ComboBox1.List 'Get the value of the list
For i = LBound(myArray) To UBound(myArray)
MsgBox myArray1(i, 0) 'If you don't use the , 0 then you get a subscript
out of range error.
Next i
End Sub

The help also says to: Use List to copy an entire two-dimensional array of
values to a control. Use AddItem to load a one-dimensional array or to load
an individual element.,

Still, I alwasy use .List to load a one one-dimensional array. E.g.,

Private Sub UserForm_Initialize()
Dim myArray() As String
myArray = Split("A|B|C|D|E", "|")
Me.ComboBox1.List = myArray
End Sub

Other than stepping through each member of the the array I don't see how
.AddItem could be used to load a one dimensional array.

Private Sub UserForm_Initialize()
Dim myArray() As String
Dim i As Long
myArray = Split("A|B|C|D|E", "|")
For i = LBound(myArray) To UBound(myArray)
Me.ComboBox1.AddItem myArray(i)
Next i
End Sub

Am I missing something?

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Hi Jay,
>
[quoted text clipped - 9 lines]
> I could try it out myself, but I'm kind of exhausted
> and may be you just happen to know.

Reply to this Message

Doug Robbins - Word MVP - 10 Sep 2007 04:24 GMT

Hi Greg,

Take a look at the sorting routines in the Training Tracker Template that I
sent to you on 26 January this year.

Signature

Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

> Helmut, Jay
>
[quoted text clipped - 69 lines]
>> I could try it out myself, but I'm kind of exhausted
>> and may be you just happen to know.

Reply to this Message

Greg Maxey - 10 Sep 2007 12:46 GMT

Thanks Doug. Have you ever located a sample source document for use with
this template. The coding examples are excellent, but sometimes difficult
to follow since I can't step through the process without the sourcedocument.

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Hi Greg,
>
[quoted text clipped - 80 lines]
>>> I could try it out myself, but I'm kind of exhausted
>>> and may be you just happen to know.

Reply to this Message

Jay Freedman - 10 Sep 2007 04:28 GMT

>Am I missing something?

No, you're not -- it's just the Help writers giving some bogus advice.

I wouldn't consider using .AddItem just because the data was a
one-dimensional list. Compared to assigning an array to the .List
property, it's extra work. I would use it when adding a single entry,
for example when using a combo box and the user has typed an entry
that doesn't exist in the list yet.

On the other hand, since .AddItem can't handle multiple columns in one
step, you have to use .List (or .Column, which does a transpose of the
rows and columns) in that case, or else do a lot of extra work to
stuff in the extra columns after the first.

Cheers,
Jay

>Helmut, Jay
>
[quoted text clipped - 55 lines]
>
>Am I missing something?

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

Reply to this Message

Jay Freedman - 10 Sep 2007 04:19 GMT

>Hi Jay,
>
[quoted text clipped - 9 lines]
>I could try it out myself, but I'm kind of exhausted
>and may be you just happen to know.

No 3-dimensional array, it's always 2-dimensional. If you store a
1-dimensional array of values in it, the result is a 2-dimensional
array where the only valid index of the second dimension is 0. This
'looks' like a 1-dimensional array, but it can't be addressed with
only one index -- the ,0 is required.

The 'why' is that it was easier for the creators of VBA to treat all
lists as 2-dimensional; they didn't have to write extra code to handle
the special 1-dimensional case. Instead, they stuck us with the job.

Cheers,
Jay

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit.

Reply to this Message

Jonathan West - 10 Sep 2007 10:38 GMT

> Hi Helmut,
>
> I hadn't thought about it before, but the dimension of the array does
> make sense when you know that the .List of a combobox or listbox can
> have multiple columns. That means the .List must be two-dimensional.

Exactly. When 1-col listbox whose list property is assigned to a Variant
containing an array, the array created is 2-dimensional, but with only one
column. In other words, a 5-item listbox will be converted to items (0, 0)
to (4, 0) of an array.

> In the usual case when you assign a one-dimensional list to it, the
> second dimension is defined as "0 to 0", but it's still there. Then
> when you assign the .List to a Variant variable, the second dimension
> is just carried along.

Strictly speaking, when assigning an array to a List property of a listbox,
what happens is that the program is clever enough to decide that a 1-d array
can be treated as a 2-d array with 1 column. If you assign the List property
immediately back to another array, you will get a 2-D array of one column.

> To sort a two-dimensional array, you have to pick one of the columns
> to sort (usually the first) and use that element of each row in the
> swap comparisons of the sorting algorithm.

There are plenty of sorting algorithms available on the net. Just plug VB
Quicksort into a search engine.

Signature

Regards
Jonathan West - Word MVP
www.intelligentdocuments.co.uk
Please reply to the newsgroup

Reply to this Message

Ian Bayly - 14 Sep 2007 03:01 GMT

A bit late in the thread but a sort technique I have used which is
particularly useful when sorting complex list box contents, is to write the
list contents out as temporary text file and read back in using MS ODBC text
driver.
This will accept standard SQL queries and is great where sort involves a
date (although displayed as string) for example.

Cheers

Ian B

>I have been playing around with some code to allow a user to type in their
>own data in a combobox and then make that entry permanently a part of the
[quoted text clipped - 96 lines]
> Me.ComboBox1.List = myArray
> End Sub

Reply to this Message

Rate this thread: