Listbox Headers

charllie · Nov 14, 2005

Hi Folks,

I am using the following code to place information into a listbox within a userform. Please could someone explain to me how i need to set it so that the listbox shows headers.

The headers are contained in row 4.

I have tried to search this forum and looked on the net but unable to find anything that i fully understand in order to do this. I have also had a play myself but no luck and i am not really sure with listboxes yet.

Code:

Private Sub UserForm_Initialize()
    'This code places the information into the Schedule ListBox1
    Dim PRL As Worksheet
    Dim I As Long
    Dim LastRow As Long
    Dim MyArray
    
        Set PRL = Sheets("Priority List")
        
        LastRow = PRL.Range("A65536").End(xlUp).Row
        
        ReDim MyArray(LastRow - 3, 4)
    
        'The 1st list box contains 3 data columns
        ListBox1.ColumnCount = 5
        
        For I = 0 To LastRow - 4
            MyArray(I, 0) = PRL.Range("A" & I + 4) 'Order Number
            
            MyArray(I, 1) = PRL.Range("B" & I + 4) 'Quantity to Pack
                    
            MyArray(I, 2) = PRL.Range("C" & I + 4) 'Title
            
            MyArray(I, 3) = PRL.Range("D" & I + 4) 'Due
            
            MyArray(I, 4) = PRL.Range("F" & I + 4) 'Status
        Next I
        
        ListBox1.List() = MyArray
End Sub

I would appreciate the help

Thanks

BrianB · Nov 14, 2005

The listbox gets its headings from the row above that set for the RowSource property. So :-

Code:

Private Sub UserForm_Initialize()
    Dim PRL As Worksheet
    Dim LastRow As Long
    Dim ListRange As String
    '--------------------------------------------
    Set PRL = Sheets("Priority List")
    LastRow = PRL.Range("A65536").End(xlUp).Row
    ListRange = "'Priority List'!A5:E" & LastRow
    '-------------------------------------------
    ListBox1.ColumnCount = 5
    ListBox1.ColumnHeads = True
    ListBox1.RowSource = ListRange
End Sub

charllie · Nov 14, 2005

Hi Brian,

How are you?

Thanks for your help.

I tried your code so my new code is as below. However if i now try to open the userform that contains the listbox i get the "Runtime error 70, Permission Denied. It then highlights the commandbutton code (.Show rtc) that is used to open the userform.

It only happens when this line of your code is active:

Code:

HandPackListBox.RowSource = ListRange

Any ideas whats happening?

Code:

'This code places the information into the Schedule HandPackListBox
    Dim PRL As Worksheet
    Dim I As Long
    Dim LastRow As Long
    Dim ListRange As String
    Dim MyArray
    
        Set PRL = Sheets("Priority List")
        
        LastRow = PRL.Range("A65536").End(xlUp).Row
        ListRange = "'Priority List'!A5:F" & LastRow

        
        ReDim MyArray(LastRow - 3, 4)
    
        'The 1st list box contains 5 data columns
        HandPackListBox.ColumnCount = 5
        HandPackListBox.ColumnHeads = True
        HandPackListBox.RowSource = ListRange
        
        For I = 0 To LastRow - 4
            MyArray(I, 0) = PRL.Range("A" & I + 4) 'Order Number
            
            MyArray(I, 1) = PRL.Range("B" & I + 4) 'Quantity to Pack
                    
            MyArray(I, 2) = PRL.Range("C" & I + 4) 'Title
            
            MyArray(I, 3) = PRL.Range("D" & I + 4) 'Due
            
            MyArray(I, 4) = PRL.Range("F" & I + 4) 'Status
        Next I
        
        HandPackListBox.List() = MyArray

Thanks

BrianB · Nov 14, 2005

You have added code of your own. My code works Ok as is.

charllie · Nov 14, 2005

Hi Brian,

If you meant remove my code altogether and just use yours then i have tried that and i get the error "Object Required" and it highlights the same line of code as stated above.

I am not sure whats is going on.

Thanks

BrianB · Nov 15, 2005

Strange. Could be a naming difference, corruption of the module. Perhaps my range is not the same as yours. Try a quick experiment by making a new userform with just a single listbox on it (It will automatically be called listbox1) and copy/paste my code in the initialize event. Run it with the F5 key. What version of Excel are you using ?

charllie · Nov 15, 2005

Hi Brian,

Try a quick experiment by making a new userform with just a single listbox on it (It will automatically be called listbox1) and copy/paste my code in the initialize event

Tried that and it works perfectly, just as i needed. I am using Office 2003.

I have had a look but nothing sticks out as to the cause of this.

Thanks

charllie · Nov 15, 2005

Hi Brian,

I think i solved it. I created a test copy of the workbook, deleted that userform and then rebuilt a new one incorporating your code. Silly thing to do maybe but it didn't take long and it seems to have worked. Going to run a few more tests but looking good.

Will let you know results.

Thanks

BrianB · Nov 15, 2005

Glad you solved it. Probably a corrupted code module. Happens all the time.

charllie · Nov 15, 2005

Thanks Brian,

Yep, it all seems ok now and working as required.

Thanks for all your help.

Listbox Headers

charllie

Well-known Member

Excel Facts

BrianB

Well-known Member

charllie

Well-known Member

BrianB

Well-known Member

charllie

Well-known Member

BrianB

Well-known Member

charllie

Well-known Member

charllie

Well-known Member

BrianB

Well-known Member

charllie

Well-known Member

Similar threads

Forum statistics

Share this page

Listbox Headers

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock