Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    list box formatting (2003)

    The code below works up to a point. The only remaining problem is that the list box displays the entries obtained as one row through a series of columns rather than as multiple rows down a single column.

    Can someone please advise how to change the formating of the list so that users will see columns (1 in this case) of entries?

    Private Sub UserForm_Initialize()
    Dim i As Integer
    i = 1
    Dim SupplierArray() As String

    While Cells(i + 10, 53).Value <> ""
    ReDim Preserve SupplierArray(1 To i)
    SupplierArray(i) = Worksheets("TP1000").Cells(i + 10, 53).Value
    i = i + 1
    Wend

    UserForm1.ListBox1.MultiSelect = fmMultiSelectSingle
    UserForm1.ListBox1.ColumnCount = 1

    UserForm1.ListBox1.Column() = SupplierArray

    End Sub

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

    Re: list box formatting (2003)

    Column transposes the array. Use List instead:

    UserForm1.ListBox1.List = SupplierArray

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box formatting (2003)

    So simple when you know how!!!

    Thanks

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box formatting (2003)

    As an addendum to this query, I need to be able to pass the location of the cell where the program should paste the item selected in the text box as a variable to the form. To elaborate:

    I have the following code within the worksheet:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    CurrentSupplierRow = ActiveCell.Row
    If Not Intersect(ActiveCell, Range("M1:M150")) Is Nothing Then
    CurrentSupplierRow = ActiveCell.Row
    UserForm1.UserForm_Initialize 'This populates the user form with the array as per the code in the initial posting above
    UserForm1.Show
    End If
    Application.EnableEvents = True

    End Sub

    Once the above code is run the list box appears and the user can select a supplier and clicking on a relevant supplier activates the Listbox.click event. However I have so far failed to pass to the the click event the reference of the worksheet location where I need it to put the result. I have tried to use a Public variable that I have declared as CurrentSupplierRow within the worksheet code, and to pass the location to the click event by various routes. However neither of these methods have worked. Can you please advise on the best way to proceed?

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

    Re: list box formatting (2003)

    Why don't you just refer to ActiveCell?

    BTW you don't need to call UserForm_initialize, it will be run automatically when you show the userform.

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

    Re: list box formatting (2003)

    BTW Wouldn't Data | Validation with the List option be more efficient than a userform? (I may be missing the point of what you're trying to do)

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box formatting (2003)

    I wanted to use data validation because it would have avoided the need to include the form in the final version of the worksheet for exporting. However data validation on each row in the range 1 to 500 seemed to place a lot of overhead on the workbook - making it very large and difficult to email a workbook including a set of the sheets.

    Also the list of suppliers can change each time the workbook is exported and I was concerned as to how to change the range of data to be included in the validation. Is there a way that I can initialise data validation and set the range of data to to be included in the validation, instead of creating the form, within the if statement below:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    CurrentSupplierRow = ActiveCell.Row
    If Not Intersect(ActiveCell, Range("M1:M500")) Is Nothing And _
    Cells(ActiveCell.Row, 1) = 1 Then
    UserForm1.UserForm_Initialize
    UserForm1.Show
    End If
    Application.EnableEvents = True
    End Sub

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

    Re: list box formatting (2003)

    Do you want the range M1:M500 to be variable, or the range that is used to populate the list box or the validation list (BA11:BAn) ?

  9. #9
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box formatting (2003)

    That range can be fixed at 500.

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box formatting (2003)

    Sorry I miss read your email. The M range can remain fixed. The BA range is variable.

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

    Re: list box formatting (2003)

    You can define a dynamic range:
    - Select Insert | Name | Define...
    - Enter a name, for example Suppliers.
    - Enter the following formula in the Refers to box:
    <code>
    =OFFSET(TP1000!$BA$11,0,0,COUNTA(TP1000!$BA$11:$BA $5000),1)
    </code>
    - Click Add, then Close.

    Then use this in validation:
    - Select M1:M500.
    - Select Data | Validation...
    - Select List in the Allow dropdown list.
    - Enter =Suppliers in the Source box.
    - Provide appropriate messages in the Input Message and Error Alert tabs.
    - Click OK.

    The validation list will automatically grow/shrink as items are added/removed in column BA.

Posting Permissions

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