ListBox adding data on top of previous registers.

Jom

New Member
Joined
Feb 19, 2021
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
Greetings colleagues ... I`m a beginner in VBA. Can you help me?
THE PROBLEM:
My ListBox, when add more registers, it adds on top of the registers that were already added, instead of inserting in the lines below.

HOW IT WORKS ON USERFORM:

TextBox1 to insert a number;
TextBox2 to enter a quantity;
3 OpttionButtons, to choose a color.

When clicking on CommandButton1 the data is sent to ListBox1 inaccording to the criteria of TextBoxs and OptionsButtons.
Example:

Textbox1 = 777 (any number)
TextBox2 = 3 (Qty.)
OptionButton1 = Green (Color)

THE RESULT IN ListBox1: (2 Columns)
777 Green
778 Green
779 Green

It is working correctly, but the new registers are add on top of the previous ones. How can I solve this?

MY CODE:

Private Sub CommandButton1_Click()
Dim Number As Double
Dim Color As String
Dim Quantity as Long
Dim i As Long

Number = Val (TextBox1)
Quantity = Val (TextBox2)
ListBox1.Clear

If OptionButton1 = True Then Color = "Green"
If OptionButton2 = True Then Color = "Blue"
If OptionButton3 = True Then Color = "Black"

For i = 1 To quantity
ListBox1.AddItem
ListBox1.List (ListBox1.ListCount - 1, 0) = Number
ListBox1.List (ListBox1.ListCount - 1, 1) = Color

Number = Number + 1

End Sub

It is working correctly, but the new registers are add on top of the previous ones. How can I solve this?
Any help, I'll be happy.
Att. Jom
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I have moved your thread. 'Questions in Other Languages' (questions in languages other than English only, please) was probably not the best forum to post in. :)
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,303
Office Version
  1. 2010
Platform
  1. Windows
change this bit and see if it helps

VBA Code:
For i = 1 To Quantity 
    ListBox1.AddItem Number
    ListBox1.List (ListBox1.ListCount - 1, 1) = Color 
    Number = Number + 1 
End Sub
 

Jom

New Member
Joined
Feb 19, 2021
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
"diddi",
I appreciate your attempt.
But unfortunately solve didn't.
It got worse.

Current situation:
Ex.:
777 Green
777 Green
777 Green
189 Black
189 Black

Next register:

556 Blue
556 Blue
556 Blue

The numbers stayed the same and next register should have gone to the line below, but it was not and replaced the previous register.

The correct thing would be/The right way is:

777 Green
778 Green
779 Green
189 Black
190 Black
556 Blue
557 Blue
558 Blue

Do you have another idea?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,303
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

what is CommandButton1_Click supposed to do in your code? the code clears listbox each time so if it is supposed to be adding items to the bottom, you should not be clearing the listbox
 
Solution

Jom

New Member
Joined
Feb 19, 2021
Messages
36
Office Version
  1. 2016
Platform
  1. Windows
Even if delete the Line "ListBox1.Clear", does not change anything.
There must be a way to make the new register go to the line below.
 

Jom

New Member
Joined
Feb 19, 2021
Messages
36
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

I would like to apologize for the stubbornness and ignorance on my part.
I changed the line "For i = 1 To Quantity". I put "0", so it didn't work correct.
I'm sorry !
And thank you very much for helping me. Solved successfully!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
Glad you figured it out. :)
I would strongly recommend applying this setting in the vba window. It will avoid such errors in the future.

1613946922391.png
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,303
Office Version
  1. 2010
Platform
  1. Windows
great. could you mark as solved so others dont need to check back here again please. :)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
could you mark as solved
Just to be clear, threads are not 'marked as solved' in this forum as in some other forums. Rather, a particular post can be 'marked as solution'. So if the OP simply says, "Don't worry, I figured it out" then there is no post to 'Mark as solution'
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,297
Members
417,135
Latest member
zeusmining

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
Top