Excel 2010 Power Programming with VBA by John Walkenbach

Author:John Walkenbach
Language: eng
Format: epub
Publisher: Wiley Publishing

FIGURE 14-13: The buttons allow the user to move items up or down in the ListBox.

The event-handler procedures for the two CommandButtons follow:

Private Sub MoveUpButton_Click()

Dim NumItems As Integer, i As Integer, ItemNum As Integer

Dim TempItem As String, TempList()

If ListBox1.ListIndex <= 0 Then Exit Sub

NumItems = ListBox1.ListCount

Dim TempList()

ReDim TempList(0 To NumItems - 1)

‘ Fill array with list box items

For i = 0 To NumItems - 1

TempList(i) = ListBox1.List(i)

Next i

‘ Selected item

ItemNum = ListBox1.ListIndex

‘ Exchange items

TempItem = TempList(ItemNum)

TempList(ItemNum) = TempList(ItemNum - 1)

TempList(ItemNum - 1) = TempItem

ListBox1.List = TempList

‘ Change the list index

ListBox1.ListIndex = ItemNum - 1

End Sub

Private Sub MoveDownButton_Click()

Dim NumItems As Integer, i As Integer, ItemNum As Integer

Dim TempItem As String, TempList()

If ListBox1.ListIndex = ListBox1.ListCount - 1 Then Exit Sub

NumItems = ListBox1.ListCount

Dim TempList()

ReDim TempList(0 To NumItems - 1)

‘ Fill array with list box items

For i = 0 To NumItems - 1

TempList(i) = ListBox1.List(i)

Next i

‘ Selected item

ItemNum = ListBox1.ListIndex

‘ Exchange items

TempItem = TempList(ItemNum)

TempList(ItemNum) = TempList(ItemNum + 1)

TempList(ItemNum + 1) = TempItem

ListBox1.List = TempList

‘ Change the list index

ListBox1.ListIndex = ItemNum + 1

End Sub

Working with multicolumn ListBox controls

A normal ListBox has a single column for its contained items. You can, however, create a ListBox that displays multiple columns and (optionally) column headers. Figure 14-14 shows an example of a multicolumn ListBox that gets its data from a worksheet range.

This example, named listbox multicolumn1.xlsm, is available on the companion CD-ROM.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.