VBA Adding Item to ListBox
Solved
Julistage
Posted messages
31
Status
Membre
-
Julistage Posted messages 31 Status Membre -
Julistage Posted messages 31 Status Membre -
Hello,
I am working on a userform. I have a tab with 2 listboxes facing each other, and when I select an item from each and click on "Validate", both items are sent to Excel in a 3-column table. I link the second column to the third using an Excel formula (to get a code):
=INDIRECT(ADDRESS(MATCH("")))
Then the information I have collected is sent to a 3-column multi-column ListBox (3 to be exact). My function works very well:
However, the problem I am encountering is that my macro only works for one item. When I click to add another item to my Multi-column ListBox, it overwrites the previous item.
This is where I don’t know what to do. How can I populate my Excel table each time I click on Validate, without deleting the previous item? (so that I can populate my multi-column ListBox as I wish)
I don't know how to approach the loop. Do you have any advice, please?
Thank you in advance :).
Julie
Configuration: Windows XP / Internet Explorer 6.0
I am working on a userform. I have a tab with 2 listboxes facing each other, and when I select an item from each and click on "Validate", both items are sent to Excel in a 3-column table. I link the second column to the third using an Excel formula (to get a code):
=INDIRECT(ADDRESS(MATCH("")))
Then the information I have collected is sent to a 3-column multi-column ListBox (3 to be exact). My function works very well:
Range("AB2") = ListBox15.Value Range("AC2") = ListBox16.Value Me.ListBox24.RowSource = "Sheet1!AB2:AD2" However, the problem I am encountering is that my macro only works for one item. When I click to add another item to my Multi-column ListBox, it overwrites the previous item.
This is where I don’t know what to do. How can I populate my Excel table each time I click on Validate, without deleting the previous item? (so that I can populate my multi-column ListBox as I wish)
I don't know how to approach the loop. Do you have any advice, please?
Thank you in advance :).
Julie
Configuration: Windows XP / Internet Explorer 6.0
5 réponses
Hello,
Your listbox is static if you use RowSource.
To make it dynamic, you need to loop through your columns and use AddItem:
For example:
;o)
--
“What one conceives well is expressed clearly, And the words to say it arrive easily.”
Nicolas Boileau
Your listbox is static if you use RowSource.
To make it dynamic, you need to loop through your columns and use AddItem:
For example:
Dim col As Long While Cells(1,col).Value <> "" Me.ListBox24.AddItem Cells(1,col) col = col + 1 Wend
;o)
--
“What one conceives well is expressed clearly, And the words to say it arrive easily.”
Nicolas Boileau
Hello,
Thank you for responding so quickly :). I'm not very skilled in VBA,
I don't know the while....wend loop :x I'm looking at my Excel VBA 2003 book lol and I can only find
Do While.....Loop :x. I assume it works the same way.
I must admit I’m not really sure how to incorporate your piece of code.
I understand roughly what it means (apparently ^^).
I call the target ListBox my ListBox24.
"As long as the cell ...... contains a value, add the item from the cell in the userform to the target ListBox, then move to the next column."
I allow myself to specify just in case :x that my problem lies in the fact of adding, in my Excel sheet whenever I click on validate, an item to the next row. Currently, when I click on validate, it overwrites the previous selection.
When you say that my target LB is static by using RowSource, does that mean I can't modify my target ListBox? That it exclusively depends on my Excel table?
Thank you :)
ps: I've modified my starting code. It’s not very important but I’ve changed my cell names.
Thank you for responding so quickly :). I'm not very skilled in VBA,
I don't know the while....wend loop :x I'm looking at my Excel VBA 2003 book lol and I can only find
Do While.....Loop :x. I assume it works the same way.
I must admit I’m not really sure how to incorporate your piece of code.
I understand roughly what it means (apparently ^^).
I call the target ListBox my ListBox24.
"As long as the cell ...... contains a value, add the item from the cell in the userform to the target ListBox, then move to the next column."
I allow myself to specify just in case :x that my problem lies in the fact of adding, in my Excel sheet whenever I click on validate, an item to the next row. Currently, when I click on validate, it overwrites the previous selection.
When you say that my target LB is static by using RowSource, does that mean I can't modify my target ListBox? That it exclusively depends on my Excel table?
Thank you :)
ps: I've modified my starting code. It’s not very important but I’ve changed my cell names.
Hello,
Oh yes, I really didn't understand everything ^^
Do While ... Loop and While ... Wend are almost equivalent, the Do requires going through the loop at least once...
To add to the next line, you need to find the last empty line of your table. You can use this function
Here you have the last empty line of column A. To add your selection based on your example, it would look like this:
Don’t hesitate to come back if this isn’t what you’re looking for.
;o)
--
“What one conceives well is clearly expressed, And the words to say it come easily.”
Nicolas Boileau
Oh yes, I really didn't understand everything ^^
Do While ... Loop and While ... Wend are almost equivalent, the Do requires going through the loop at least once...
To add to the next line, you need to find the last empty line of your table. You can use this function
Dim DerniereLigne As Integer DerniereLigne = Range("A65536").End(xlUp).Row Here you have the last empty line of column A. To add your selection based on your example, it would look like this:
Dim DerniereLigne As Integer DerniereLigne = Range("AB65536").End(xlUp).Row Range("AB" & DerniereLigne) = ListBox15.Value DerniereLigne = Range("AC65536").End(xlUp).Row Range("AC" & DerniereLigne) = ListBox16.Value Don’t hesitate to come back if this isn’t what you’re looking for.
;o)
--
“What one conceives well is clearly expressed, And the words to say it come easily.”
Nicolas Boileau
```html
I integrated this code into my userform. And it doesn’t work too well :x.
In fact, nothing happens :x
When I click on 'validate', it continues to overwrite the previous line.
I feel like it does the same thing as what I had before:
Earlier, before I adapted it this way, meaning by copying/pasting what you showed me, it was pasting the element in column A oddly. On the first click at the first line, on the second twenty lines further down, on the third, a hundred lines further down, and on the fourth to line 22,222 :x ```
In fact, nothing happens :x
When I click on 'validate', it continues to overwrite the previous line.
Workbooks("Test Correspondance7 v7.6.xls").Activate Dim DerniereLigneA As Integer Dim DerniereLigneB As Integer DerniereLigneA = Range("A65536").End(xlUp).Row Range("A2" & DerniereLigne) = ListBox1.Value DerniereLigneB = Range("B65536").End(xlUp).Row Range("B2" & DerniereLigne) = ListBox2.Value Calculate Me.ListBox17.RowSource = "Sheet1!A2:C2" I feel like it does the same thing as what I had before:
Workbooks("Test Correspondance7 v7.6.xls").Activate Range("A2" &) = ListBox1.Value Range("B2") = ListBox2.Value Calculate Me.ListBox17.RowSource = "Sheet1!A2:C2" Earlier, before I adapted it this way, meaning by copying/pasting what you showed me, it was pasting the element in column A oddly. On the first click at the first line, on the second twenty lines further down, on the third, a hundred lines further down, and on the fourth to line 22,222 :x ```
I only have a piece of code right now... it's hard to say anything.
"Test Correspondance7 v7.6.xls" ??? what is that exactly? the active workbook, an open workbook? the workbook that contains the UF?
To be able to answer you, I need an overview of what you want to do.
You can also upload your workbook or a zip of the environment at http://www.cjoint.com, then paste the link provided by the site here. Remove any sensitive or confidential data and replace them with dummy stuff like "toto" "tata" "Pim" "Pam" "Pom" ... ^^
;o)
"Test Correspondance7 v7.6.xls" ??? what is that exactly? the active workbook, an open workbook? the workbook that contains the UF?
To be able to answer you, I need an overview of what you want to do.
You can also upload your workbook or a zip of the environment at http://www.cjoint.com, then paste the link provided by the site here. Remove any sensitive or confidential data and replace them with dummy stuff like "toto" "tata" "Pim" "Pam" "Pom" ... ^^
;o)
Thank you Polux :)
Here is the answer to the question, thanks to him!
Here is the answer to the question, thanks to him!
Dim ws As Worksheet Workbooks("***.xls").Activate Set ws = ThisWorkbook.Worksheets(1) Dim DerniereLigneA As Integer Dim DerniereLigneB As Integer DerniereLigneA = ws.Range("A65536").End(xlUp).Row + 1 Range("A" & DerniereLigneA) = ListBox1.Value DerniereLigneB = Range("B65536").End(xlUp).Row + 1 Range("B" & DerniereLigneB) = ListBox2.Value Calculate 'Initialization and filling of listbox17 Dim Derligne As Long 'ListBox17.Clear Derligne = ws.Range("A65536").End(xlUp).Row + 1 Me.ListBox17.RowSource = "Sheet1!A2:C" & Derligne & ""