6 Replies - 958 Views - Last Post: 08 May 2013 - 02:43 AM Rate Topic: -----

#1 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Displaying data in a Listbox

Posted 06 May 2013 - 03:04 AM

Hi
I have written a piece of code in vb excel windows 7, that gets all the data from column A in the active worksheet and displays the data in a listbox. The user can then select which ever entry they want and use this to search column B and C for other information. The way it works at the moment is that the codes displays all data in column A eg Fault1, Fault2, Fault3 etc what I was woundering is the any way of showing only one Fault1 no matter how many Fault1's there is in the whole column A. In other words if there was 10 Fault1's in column A I would like the list box to show only 1 Fault1 and not all 10.
Does anyone know if this can be done.

Is This A Good Question/Topic? 0
  • +

Replies To: Displaying data in a Listbox

#2 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3459
  • View blog
  • Posts: 11,724
  • Joined: 12-December 12

Re: Displaying data in a Listbox

Posted 06 May 2013 - 03:12 AM

You need to show some code.

However, as you are adding to the listbox, you could check whether the current value already exists in the listbox, and if it does don't add it.
Was This Post Helpful? 0
  • +
  • -

#3 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Re: Displaying data in a Listbox

Posted 06 May 2013 - 01:25 PM

Hope this helps
Dim nrow As Integer
Range("A1").Select
Do While ActiveCell.Value <> ""
Let nrow = ActiveCell.Row
ActiveCell.Offset(1, 0).Select

ActiveCell.EntireRow.Select
Let nrow = nrow + 1
new_error = "A" & nrow
ListBox1.AddItem Range(new_error).Value
Loop

End Sub

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3459
  • View blog
  • Posts: 11,724
  • Joined: 12-December 12

Re: Displaying data in a Listbox

Posted 06 May 2013 - 01:47 PM

It is easy to wrap your code in code tags; just highlight the code and press the [ CODE ] button in the panel above where you are typing. Press the Preview Post button until it is correct.

Dim nrow As Integer
Range("A1").Select
Do While ActiveCell.Value <> ""
    Let nrow = ActiveCell.Row
    ActiveCell.Offset(1, 0).Select

    ActiveCell.EntireRow.Select
    Let nrow = nrow + 1
    new_error = "A" & nrow
    ListBox1.AddItem Range(new_error).Value
Loop

End Sub

Was This Post Helpful? 0
  • +
  • -

#5 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Re: Displaying data in a Listbox

Posted 07 May 2013 - 12:48 PM

Sorry. Any way I can just get the listbox to show one fault1 and not every fault1
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3459
  • View blog
  • Posts: 11,724
  • Joined: 12-December 12

Re: Displaying data in a Listbox

Posted 07 May 2013 - 02:34 PM

    Dim r As Range
    Dim bExists As Boolean
    Dim x As Integer
    
    Application.ScreenUpdating = False
    ListBox1.Clear
    For Each r In Range("A1", Range("A1").End(xlDown))
        bExists = False
        For x = 0 To ListBox1.ListCount - 1
            If ListBox1.List(x) = r.Value Then
                bExists = True
                Exit For
            End If
        Next x
        If Not bExists Then
            ListBox1.AddItem r.Value
        End If
    Next r
    Application.ScreenUpdating = True

Was This Post Helpful? 0
  • +
  • -

#7 nocando  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 29-March 13

Re: Displaying data in a Listbox

Posted 08 May 2013 - 02:43 AM

Thank you very much I will try that and see how i get on. Thanks again
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1