Adding values to listbox

vidario

New Member
Joined
Sep 27, 2012
Messages
6
Hello everyone, I'm a real newbie in vba, but I wish I could do this:
I have a spreadsheet like the one in the picture. In the first column I have dates, amounts in the second and third categories (which may or may not be included). What I would like is to get a listbox on a userform bringing me back each category once and the total of that category (as in the red box image)

How can I do?

thanks

 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Anyone? I thought it was simple... I don't know if I have to use an array or a collection...but I can't manage them
 
Upvote 0
look at this
Sheet1

*ABC
1DATAIMPORTOCATEGORIA
215/09/2012100CASA
316/09/2012300MACCHINA
422/09/2012200CASA
525/092012600LAVORO
626/09/2012300*
728/09/2012100ASILO
830/09/2012200MACCHINA
902/10/201250REGALI
10***
11***
12***
13CATEGORIAIMPORTO*
14CASA300*
15MACCHINA500*
16LAVORO600*
17ASILO100*
18REGALI50*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:75px;"><col style="width:66px;"><col style="width:78px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B14=SUMIF(C2:C9,A14,B2:B9)
B15=SUMIF(C2:C9,A15,B2:B9)
B16=SUMIF(C2:C9,A16,B2:B9)
B17=SUMIF(C2:C9,A17,B2:B9)
B18=SUMIF(C2:C9,A18,B2:B9)

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Last edited:
Upvote 0
If, for example, you'd like the code to be triggered via a CommandButton click, try the following code that needs to be placed in the UserForm code module. In the Project Explorer window (Ctrl+R), right-click the UserForm, select View Code, and copy/paste the code in the code module)...

Code:
[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click() [COLOR=#008000]'change the name of the CommandButton accordingly[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oDict [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aData() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Rng [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] RowCount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Set[/COLOR] Rng = Worksheets("Sheet1").Range("A1").CurrentRegion [COLOR=green]'change the sheet name accordingly[/COLOR]
    RowCount = Rng.Rows.Count
    [COLOR=darkblue]If[/COLOR] RowCount > 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oDict = CreateObject("Scripting.Dictionary")
        [COLOR=darkblue]ReDim[/COLOR] aData(1 [COLOR=darkblue]To[/COLOR] 2, 1 [COLOR=darkblue]To[/COLOR] RowCount)
        c = 0
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] RowCount
            [COLOR=darkblue]If[/COLOR] Rng(i, 3).Value <> "" [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] oDict.Exists(Rng(i, 3).Value) [COLOR=darkblue]Then[/COLOR]
                    c = c + 1
                    aData(1, c) = Rng(i, 3).Value
                    aData(2, c) = Rng(i, 2).Value
                    oDict.Add Rng(i, 3).Value, c
                [COLOR=darkblue]Else[/COLOR]
                    aData(2, oDict.Item(Rng(i, 3).Value)) = aData(2, oDict.Item(Rng(i, 3).Value)) + Rng(i, 2).Value
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
        [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aData(1 To 2, 1 To c)
        Me.ListBox1.List = Application.Transpose(aData)
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "No data is available...", vbInformation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]
[/FONT]

Alternatively, the code can be used in the Initialize event so that the ListBox gets updated when the UserForm is displayed. In this case, you would replace...

Code:
[FONT=Courier New][COLOR=#00008b]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click() [/FONT]

with

Code:
[FONT=Courier New][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()[/FONT]


By the way, a Collection could have been used instead of a Dictionary.
 
Upvote 0
If, for example, you'd like the code to be triggered via a CommandButton click, try the following code that needs to be placed in the UserForm code module. In the Project Explorer window (Ctrl+R), right-click the UserForm, select View Code, and copy/paste the code in the code module)...

Code:
[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click() [COLOR=#008000]'change the name of the CommandButton accordingly[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oDict [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aData() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Rng [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] RowCount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Set[/COLOR] Rng = Worksheets("Sheet1").Range("A1").CurrentRegion [COLOR=green]'change the sheet name accordingly[/COLOR]
    RowCount = Rng.Rows.Count
    [COLOR=darkblue]If[/COLOR] RowCount > 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oDict = CreateObject("Scripting.Dictionary")
        [COLOR=darkblue]ReDim[/COLOR] aData(1 [COLOR=darkblue]To[/COLOR] 2, 1 [COLOR=darkblue]To[/COLOR] RowCount)
        c = 0
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] RowCount
            [COLOR=darkblue]If[/COLOR] Rng(i, 3).Value <> "" [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] oDict.Exists(Rng(i, 3).Value) [COLOR=darkblue]Then[/COLOR]
                    c = c + 1
                    aData(1, c) = Rng(i, 3).Value
                    aData(2, c) = Rng(i, 2).Value
                    oDict.Add Rng(i, 3).Value, c
                [COLOR=darkblue]Else[/COLOR]
                    aData(2, oDict.Item(Rng(i, 3).Value)) = aData(2, oDict.Item(Rng(i, 3).Value)) + Rng(i, 2).Value
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
        [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aData(1 To 2, 1 To c)
        Me.ListBox1.List = Application.Transpose(aData)
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "No data is available...", vbInformation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]
[/FONT]

Alternatively, the code can be used in the Initialize event so that the ListBox gets updated when the UserForm is displayed. In this case, you would replace...

Code:
[FONT=Courier New][COLOR=#00008b]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click() [/FONT]

with

Code:
[FONT=Courier New][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()[/FONT]


By the way, a Collection could have been used instead of a Dictionary.

Great! It works perfectly! Thank you very much... Just another question: i don't like how the listbox looks... Is there any another grid i can use? Maybe something with row lines? Maybe a listview? I dont like to have a spreadsheet grid on my userform... Thanks again
 
Upvote 0
If, for example, you'd like the code to be triggered via a CommandButton click, try the following code that needs to be placed in the UserForm code module. In the Project Explorer window (Ctrl+R), right-click the UserForm, select View Code, and copy/paste the code in the code module)...

Code:
[FONT=Courier New][COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click() [COLOR=#008000]'change the name of the CommandButton accordingly[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] oDict [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] aData() [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Rng [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] RowCount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] c [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Set[/COLOR] Rng = Worksheets("Sheet1").Range("A1").CurrentRegion [COLOR=green]'change the sheet name accordingly[/COLOR]
    RowCount = Rng.Rows.Count
    [COLOR=darkblue]If[/COLOR] RowCount > 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Set[/COLOR] oDict = CreateObject("Scripting.Dictionary")
        [COLOR=darkblue]ReDim[/COLOR] aData(1 [COLOR=darkblue]To[/COLOR] 2, 1 [COLOR=darkblue]To[/COLOR] RowCount)
        c = 0
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] RowCount
            [COLOR=darkblue]If[/COLOR] Rng(i, 3).Value <> "" [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] oDict.Exists(Rng(i, 3).Value) [COLOR=darkblue]Then[/COLOR]
                    c = c + 1
                    aData(1, c) = Rng(i, 3).Value
                    aData(2, c) = Rng(i, 2).Value
                    oDict.Add Rng(i, 3).Value, c
                [COLOR=darkblue]Else[/COLOR]
                    aData(2, oDict.Item(Rng(i, 3).Value)) = aData(2, oDict.Item(Rng(i, 3).Value)) + Rng(i, 2).Value
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
        [COLOR=darkblue]ReDim[/COLOR] [COLOR=darkblue]Preserve[/COLOR] aData(1 To 2, 1 To c)
        Me.ListBox1.List = Application.Transpose(aData)
    [COLOR=darkblue]Else[/COLOR]
        MsgBox "No data is available...", vbInformation
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]
[/FONT]

Alternatively, the code can be used in the Initialize event so that the ListBox gets updated when the UserForm is displayed. In this case, you would replace...

Code:
[FONT=Courier New][COLOR=#00008b]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] CommandButton1_Click() [/FONT]

with

Code:
[FONT=Courier New][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Initialize()[/FONT]


By the way, a Collection could have been used instead of a Dictionary.

HI Domenic

Can u please please be kind enough and explain how the code works.
i really want to understand how the dict item etc...works

I really understand the way you explain things and would really appreciate if you could do that please

many thanks
 
Upvote 0
Great! It works perfectly! Thank you very much...

You're very welcome!

Just another question: i don't like how the listbox looks... Is there any another grid i can use? Maybe something with row lines? Maybe a listview? I dont like to have a spreadsheet grid on my userform... Thanks again

Yeah, I think you're right. A ListView control looks nicer. Have a look at the following link...

Daily Dose of Excel » Blog Archive » ListView
 
Upvote 0
HI Domenic

Can u please please be kind enough and explain how the code works.
i really want to understand how the dict item etc...works

I really understand the way you explain things and would really appreciate if you could do that please

many thanks

A Dictionary object is an object that we create in order to store data so that we can refer to it when needed. The data stored is a key and item pair. This allows us to retrieve an item using it's key...

Code:
Dictionary.Item(Key)

In this example, first we create an instance of the Dictionary object using this line of code...

Code:
[COLOR=#00008b]Set[/COLOR] oDict = CreateObject("Scripting.Dictionary")

Then we loop through the data so that two things happen. First, we fill the array aData with the category and amount. For the array, the first row contains the category and the second row contains the corresponding amount. So each category/amount pair will be placed in a subsequent column within the array aData. So the first category/amount pair are placed in the first column, the second pair in the second column, and so on...

Code:
c = c + 1
aData(1, c) = Rng(i, 3).Value
aData(2, c) = Rng(i, 2).Value

Then, at the same time, we want to store the category and column index in our Dictionary. The category being the key, and the column index being the item...

Code:
oDict.Add Rng(i, 3).Value, c

However, when we're looping through the data, we use the Exists method of the Dictionary object to see whether the key, in this case the category, exists before filling our array aData, using the following statement...

Code:
[COLOR=#00008b]If[/COLOR] [COLOR=darkblue]Not[/COLOR] oDict.Exists(Rng(i, 3).Value) [COLOR=darkblue]Then[/COLOR]

If it doesn't already exist, we go ahead and fill aData with the category and amount, while storing the category and column index in our Dictionary. If, however, the key or category already exists, we only want to update its corresponding amount in aData. And we can use the category (key) to retrieve the column index (item) from the Dictionary. With the column index, we can now update the amount for the category in the array aData.

This gives us our column index (item) for the category...

Code:
oDict.Item(Rng(i, 3).Value)

This refers to the amount corresponding to the category in aData...

Code:
aData(2, oDict.Item(Rng(i, 3).Value))
 
Last edited:
Upvote 0
To use the ListView control, first add it to your UserForm. You'll notice that a reference (Alt+F11 > Tools > Reference) to 'Microsoft Windows Common Controls' is automatically added. If not, you'll need to add it yourself. Then place the following code in the UserForm code module (in the Project Explorer window (Ctrl+R), right-click the UserForm, select View Code, and copy/paste the code in the code module). Change the name of the ListView control, accordingly.

Note, if the ListView control is not available in your Toolbox, you'll need to add it to your Toolbox, and then you'll need to register it.

Code:
[FONT=Courier New][COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] UserForm_Activate()
    [COLOR=green]'Change the settings for the ListView[/COLOR]
    [COLOR=darkblue]With[/COLOR] Me.ListView1
        .Gridlines = [COLOR=darkblue]True[/COLOR]
        .HideColumnHeaders = [COLOR=darkblue]False[/COLOR]
        .View = lvwReport
        .Width = 164
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=green]'Add the column headers for the ListView[/COLOR]
    [COLOR=darkblue]With[/COLOR] Me.ListView1.ColumnHeaders
        .Add , , "Categoria", 80, lvwColumnLeft [COLOR=green]'first column must be left-aligned[/COLOR]
        .Add , , "Importo", 80, lvwColumnCenter
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    [COLOR=green]'Load the data[/COLOR]
    [COLOR=darkblue]Call[/COLOR] LoadData
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] LoadData()
    [COLOR=green]'Declare the variables[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Dict [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] LstItem [COLOR=darkblue]As[/COLOR] ListItem
    [COLOR=darkblue]Dim[/COLOR] Rng [COLOR=darkblue]As[/COLOR] Range
    [COLOR=darkblue]Dim[/COLOR] Categoria [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] Importo [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] RowCount [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=green]'Set the range for the source data[/COLOR]
    [COLOR=darkblue]Set[/COLOR] Rng = Worksheets("Sheet1").Range("A1").CurrentRegion
    [COLOR=green]'Count the number of rows in the source range[/COLOR]
    RowCount = Rng.Rows.Count
    [COLOR=darkblue]If[/COLOR] RowCount > 1 [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'Create an instance of the Dictionary object[/COLOR]
        [COLOR=darkblue]Set[/COLOR] Dict = CreateObject("Scripting.Dictionary")
        [COLOR=green]'Loop through the source data[/COLOR]
        [COLOR=darkblue]For[/COLOR] i = 2 [COLOR=darkblue]To[/COLOR] RowCount
            Categoria = Rng(i, 3).Value [COLOR=green]'current category[/COLOR]
            [COLOR=darkblue]If[/COLOR] Categoria <> "" [COLOR=darkblue]Then[/COLOR]
                Importo = Rng(i, 2).Value [COLOR=green]'current amount[/COLOR]
                [COLOR=green]'If the Dictionary does not already contain the category...[/COLOR]
                [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] Dict.Exists(Categoria) [COLOR=darkblue]Then[/COLOR]
                    [COLOR=green]'...add the category and corresponding amount to the ListView[/COLOR]
                    [COLOR=darkblue]Set[/COLOR] LstItem = Me.ListView1.ListItems.Add(, Categoria, Categoria)
                    LstItem.ListSubItems.Add 1, , Importo
                    [COLOR=green]'...add the category to the Dictionary object[/COLOR]
                    Dict.Add Categoria, Categoria
                [COLOR=darkblue]Else[/COLOR]
                    [COLOR=darkblue]With[/COLOR] Me.ListView1
                        [COLOR=green]'...add the amount to the existing amount for the category[/COLOR]
                        .ListItems(Categoria).ListSubItems(1).Text = [COLOR=darkblue]CDbl[/COLOR](.ListItems(Categoria).ListSubItems(1).Text) + Importo
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]
[/FONT]
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top