Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

Converting to Listview from Listbox vba excel

exce 2003 vba...

I have the following userform loading a "listbox" from a sheet range...

I want to convert to ListView...to make the sorting easier

I used\
ListView1.RowSource = "List!" & usedRng.Address

But I just got a blank grid ?

Thanks
fordraiders


Public Sub LoadData()
On Error Resume Next
      Dim usedRng As Range
      Sheets("List").Activate
      
      
    Cells.Select
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
      
      
      
      
      
      ' get the rnge from the spreadsheet
         DetermineUsedRange usedRng
           ' select the range for the listbox
            ListBox1.RowSource = "List!" & usedRng.Address
           
End Sub
Sub DetermineUsedRange(ByRef theRng As Range)
Dim FirstRow As Integer, FirstCol As Integer, _
   LastRow As Integer, LastCol As Integer
On Error GoTo handleError
FirstRow = Cells.Find(What:="*", _
     SearchDirection:=xlNext, _
     SearchOrder:=xlByRows).Row
FirstCol = 1
'FirstCol = Cells.Find(What:="1", _
'     SearchDirection:=xlNext, _
'     SearchOrder:=xlByColumns).Column

LastRow = Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
     SearchDirection:=xlPrevious, _
     SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
   Cells(LastRow, LastCol))
handleError:
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Fordraiders

ASKER

rorya, Thanks, So I was debugging.. The column Number and Row count will be determined always by the code correct...?

Thanks
fordraiders
Thanks for the workbook example...
The code loads the contiguous range from A1 on the current sheet, if that's what you mean?
OK Thanks
Thanks