Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorted ListBox and CursorWait (XL2002)

    I have been using WordVBA more than ExcelVBA and I have a question to you Excel sharks in here...

    I want to sort the content of a listbox - in WordVBA I can use an old WordBasic method like this:

    (fill in all itmes from the listbox in a string-array)
    WordBasic.sortarray myArray()
    Listbox1.List = myArray()

    This works great in Word, but I cannot make it work in Excel - I have tried to include a reference to Microsoft Word 10.0 Object Library, but that doesn't make any difference.

    brgds
    Jesper

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorted ListBox and CursorWait (XL2002)

    I don't know whether WordBasic.SortArray still works in Word XP. It does in Word 97. The following works in Excel 97 (with a reference to the Microsoft Word 8.0 Object Library set). The code checks if Word is already active; if so, it uses the running instance, otherwise, it starts Word (and quits it afterwards).

    Private Sub UserForm_Initialize()
    Dim myArray(0 To 4) As String
    Dim appWord As Word.Application
    Dim blnFlag As Boolean

    myArray(0) = "And"
    myArray(1) = "Now"
    myArray(2) = "Something"
    myArray(3) = "Completely"
    myArray(4) = "Different"

    On Error Resume Next
    Set appWord = GetObject(, "Word.Application")
    If appWord Is Nothing Then
    Set appWord = CreateObject("Word.Application")
    blnFlag = True
    End If
    On Error GoTo Err_Initialize

    appWord.WordBasic.SortArray myArray
    ListBox1.List = myArray

    Exit_Initialize:
    On Error Resume Next
    If blnFlag = True Then
    appWord.Quit
    End If
    Set appWord = Nothing
    Exit Sub

    Err_Initialize:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Initialize
    End Sub

    If this doesn't work in XP, it isn't difficult to write your own sort routine. If you do a search for "sort array" in the VBA forum, you'll find examples.

  3. #3
    New Lounger
    Join Date
    Jan 2001
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted ListBox and CursorWait (XL2002)

    Hi Hans

    Thanx for your answer - I will try your solution. I thought there might be an equivalent solution in Excel for the WordBasic.SortArray, but I wil stop searching and either do the Word routine or do my own sorting.

    brgds
    Jesper

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •