Dynamic List box

[Danny]

I would like to find out if it is possible in Excel 2003 to build a
list box that does the following

If a field Cell A1 is populated with cc=23456 or cc=12345 or cc=45678
(CC is Cost Center)
Is it possible to get this into a list box using vba in this format

cc=23456
cc=12345
cc=45678

If so how?

The second issue is the field Cell A1 that the list box pulls from can
be populated with different numbers of costcenters and values
example
cc=23456 or cc=12345 or cc=45678 or cc67890 Which is 4 different cc's
the above example only has 3. There could be 100 cc's

The logic is always the same format cc=5 digits or cc=5digits and has
or seperating them etc...

Is it possible to use VBA to list into a listbox the above scenarios?

If you need more information please let me know.

Thanks

 

[Nigel]

If you have hundreds of CCs and you wish to ListBox them, where is the list
stored?

 

[Danny]

If you have hundreds of CCs and you wish to ListBox them, where is the list
stored?

--

Regards,
Nigel
(e-mail address removed)












- Show quoted text -

The information is stored in the format above in a database. I am
extracting the data into Excel. Why? Is it not possible to write vba
with the logic above doing a loop and adding the results to a listbox?

 

[Nigel]

The issue is that your database will contain duplicates and your original OP
said the value in cell A1 suggested a single value not a list.

So if the list is in column A (sheet1) then use this code to populate the
list box.....

Sub CCList()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in A1 to last row in column A
With Sheets(1)
Set AllCells = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!

On Error Resume Next
For Each Cell In AllCells
If Not Cell.EntireRow.Hidden Then
NoDupes.Add Cell.Value, CStr(Cell.Value)
End If
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, before:=j
NoDupes.Add Swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
With Sheets(1).ListBox1
.Clear
For Each Item In NoDupes
.AddItem Item
Next Item
End With
End Sub

--

Regards,
Nigel
(e-mail address removed)

If you have hundreds of CCs and you wish to ListBox them, where is the
list
stored?

--

Regards,
Nigel
(e-mail address removed)












- Show quoted text -

The information is stored in the format above in a database. I am
extracting the data into Excel. Why? Is it not possible to write vba
with the logic above doing a loop and adding the results to a listbox?

 

[Danny]

The issue is that your database will contain duplicates and your original OP
said the value in cell A1 suggested a single value not a list.

So if the list is in column A (sheet1) then use this code to populate the
list box.....

Sub CCList()
    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item

'   The items are in A1 to last row in column A
    With Sheets(1)
      Set AllCells = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With

'   The next statement ignores the error caused
'   by attempting to add a duplicate key to the collection.
'   The duplicate is not added - which is just what we want!

    On Error Resume Next
    For Each Cell In AllCells
        If Not Cell.EntireRow.Hidden Then
        NoDupes.Add Cell.Value, CStr(Cell.Value)
        End If
    Next Cell

'   Resume normal error handling
    On Error GoTo 0

'   Sort the collection (optional)
    For i = 1 To NoDupes.Count - 1
        For j = i + 1 To NoDupes.Count
            If NoDupes(i) > NoDupes(j) Then
                Swap1 = NoDupes(i)
                Swap2 = NoDupes(j)
                NoDupes.Add Swap1, before:=j
                NoDupes.Add Swap2, before:=i
                NoDupes.Remove i + 1
                NoDupes.Remove j + 1
            End If
        Next j
    Next i

'   Add the sorted, non-duplicated items to a ListBox
    With Sheets(1).ListBox1
     .Clear
     For Each Item In NoDupes
       .AddItem Item
     Next Item
    End With
End Sub

--

Regards,
Nigel
(e-mail address removed)





The information is stored in the format above in a database.  I am
extracting the data into Excel.  Why?  Is it not possible to write vba
with the logic above doing a loop and adding the results to a listbox?- Hide quoted text -

- Show quoted text -

Nigel,

I really appreciate your response. Forgive me but I am not sure I
follow the code above. I tried it out and it doesn't seem to fit what
I am trying to do.


I may have confused the issue. In Cell A1 the data that is populated
into A1 and only cell A1 as CC=12345 Or CC=34567 this information is
dynamic and changes in length A1 could be CC=12345 or CC=34567 or
CC=23456

Example 1.
so cell A1 could have
CC=12345 or CC=34567 in one instance

Example 2
In another instance cell A1 could have
CC=12345 or CC=34567 or CC=23456
So what I want to do is list in a listbox the following information

From Example 1 above
CC=12345
CC=34567

From Example 2
CC=12345
CC=34567
CC=23456

I hope this makes sense. It is kind of tought to follow. But the
database can have all of these values in one field for that 1 record
and that is what the query retrieves.

Thanks again for your response.

 

[Danny]

Nigel,

I really appreciate your response.  Forgive me but I am not sure I
follow the code above. I tried it out and it doesn't seem to fit what
I am trying to do.

I may have confused the issue.  In Cell A1 the data that is populated
into A1 and only cell A1 as CC=12345 Or CC=34567 this information is
dynamic and changes in length A1 could be CC=12345 or CC=34567 or
CC=23456

Example 1.
so cell A1 could have
CC=12345 or CC=34567 in one instance

Example 2
In another instance cell A1 could have
CC=12345 or CC=34567 or CC=23456
So what I want to do is list in a listbox the following information

From Example 1 above
CC=12345
CC=34567

From Example 2
CC=12345
CC=34567
CC=23456

I hope this makes sense.  It is kind of tought to follow.  But the
database can have all of these values in one field for that 1 record
and that is what the query retrieves.

Thanks again for your response.- Hide quoted text -

- Show quoted text -

I may have been going about this all wrong. I relooked at what I was
trying to do. Maybe text to columns would work better. So if A1 has
CC=12345 or CC=34567 it would parse it out to be
A1 value is 12345
B1 Value is or
C1 value is 34567.

Or if A1 has CC=12345 or CC=34567 or CC=27689

Then it would parse out to
A1 Value is 12345
B1 Value is or
C1 value is 34567
D1 value is or
E1 value is 27689

1. With this in mind is there away to loop in a text to columns to
parse it out until there isn't a value to parse out in Field A1?
2. Then take all of those fields that are populated and select those
fields that are populated with values to go into a list box?

Thanks
Gosh I hope this makes sense