Last Display on userform to a Listbox

abbas50

New Member
Joined
Dec 6, 2019
Messages
37
Office Version
2016
Platform
Windows
VBA Code:
lstdisplay.ColumnCount = 15
lstdisplay.RowSource = "A1:O600000"
So when i do this it Displays everything on the sheet i am at i want to adjust that and when i use the userform it shows last 10 or 15 entries only and on the active sheet from userform any help?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,791
Office Version
2007
Platform
Windows
I did not understand well. Do you have 600,000 lines on the sheet? do you want to see the rows 599,986 to 600,000?
 

abbas50

New Member
Joined
Dec 6, 2019
Messages
37
Office Version
2016
Platform
Windows
i have over 20K rows and i have a data entry form with several sheets i just want to see my last entries or last 20-30 rows
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,791
Office Version
2007
Platform
Windows
I still don't understand what you mean by : "my last entries or last 20-30 rows "

You could be more specific with your examples, in the macro you have this: 600,000 now mention that you have 20,000, in the original request from 10 to 15 and now from 20 to 30.
You could explain with some example, some image of what you have on the sheet and what you want to see in your listbox.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
I think what your saying is you only want to see a specific number or rows in your listbox
This script should only show you the last 12 rows of data.
You will see in the script I have set HowManyRows to 12
Change 12 to what you want.

I wrote the script this way and am not using RowSource
VBA Code:
Private Sub CommandButton1_Click()
'Modified  12/7/2019  8:55:12 AM  EST
Dim HowManyRows As Long
HowManyRows = 12
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lstdisplay.ColumnCount = 15
lstdisplay.Clear
lstdisplay.List = Cells(Lastrow - HowManyRows + 1, 1).Resize(HowManyRows, 15).Value
End Sub
 

abbas50

New Member
Joined
Dec 6, 2019
Messages
37
Office Version
2016
Platform
Windows
This is nearly what im looking for and its working it shows my last 12 row i want if i get a mistake when doing an entry on this userform i want to delete it so i did this code
VBA Code:
Private Sub CommandButton3_Click()
For I = 0 To Range("A65356").End(xlUp).Row - 1
    If lstdisplay.Selected(I) Then
          Rows(I + 1).Select
        Selection.Delete
    End If
Next I
End Sub
but its deleting a row from the old list what i mean is it deleted row number 12 on the list not the row i just entered which number 1556 :/
and another thing i have this code for more than 1 sheet so i want to display them even if im not on that sheet :/
Code:
Private Sub CommandButton1_Click()
Dim cNum As Integer
Dim x As Integer
Dim nextrow As Range
Dim sht As String
Dim f As Range
If Me.TextBox1.Value = "" Then
MsgBox "Name Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox2.Value = "" Then
MsgBox "Gender Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox3.Value = "" Then
MsgBox "Please Choose 1 or more Sections"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox4.Value = "" Then
MsgBox "Please Enter A Primary Email"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox7.Value = "" Then
MsgBox "URL Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox8.Value = "" Then
MsgBox "HandleRequired"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox9.Value = "" Then
MsgBox "Followers Number  Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox10.Value = "" Then
MsgBox "Rate Number Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox11.Value = "" Then
MsgBox "Date Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox12.Value = "" Then
MsgBox "Platform Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox13.Value = "" Then
MsgBox "Country Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox14.Value = "" Then
MsgBox "Followers Group Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox15.Value = "" Then
MsgBox "Rate Group Required"
Me.TextBox1.SetFocus
Exit Sub
End If
'set the variable for the sheet
sht = ComboBox1.Value
'check for values
If Me.ComboBox1.Value = "" Then
MsgBox "Please Select A sheet"
Exit Sub
End If
'Prevent duplicates in column H
  Set f = Sheets(sht).Range("H:H").Find(TextBox8.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    MsgBox "Handle already exists: " & TextBox8.Value
    Exit Sub
  End If
'change the number for the number of controls on the userform
cNum = 15
'add the data to the selected worksheet
Set nextrow = Sheets(sht).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
For x = 1 To cNum
nextrow = Me.Controls("TextBox" & x).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear the values in the userform
For x = 1 To cNum
Me.Controls("TextBox" & x).Value = ""
Next
'communicate the results
MsgBox "The values have been sent to the " & sht & " sheet"

Dim arrItems()
Dim cnt As Long
Dim pro As Long

    For pro = 0 To TextBox3.ListCount - 1
        If TextBox3.Selected(pro) Then
            ReDim Preserve arrItems(cnt)
            arrItems(cnt) = TextBox3.List(pro)
            cnt = cnt + 1

        End If
    Next pro

    If cnt > 0 Then
        Sheets(sht).Range("C" & Rows.Count).End(xlUp).Value = Join(arrItems, "|")
    End If

Dim HowManyRows As Long
HowManyRows = 12
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lstdisplay.ColumnCount = 15
lstdisplay.Clear
lstdisplay.List = Cells(Lastrow - HowManyRows + 1, 1).Resize(HowManyRows, 15).Value
i hope anyone can help me and then im fully done to what i want on thsi userform it will help me to do my job faster ty :)

I think what your saying is you only want to see a specific number or rows in your listbox
This script should only show you the last 12 rows of data.
You will see in the script I have set HowManyRows to 12
Change 12 to what you want.

I wrote the script this way and am not using RowSource
VBA Code:
Private Sub CommandButton1_Click()
'Modified  12/7/2019  8:55:12 AM  EST
Dim HowManyRows As Long
HowManyRows = 12
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
lstdisplay.ColumnCount = 15
lstdisplay.Clear
lstdisplay.List = Cells(Lastrow - HowManyRows + 1, 1).Resize(HowManyRows, 15).Value
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
You never said anything in your original posting about deleting rows.

Or other sheets or a lot of what your wanting now.
I normally only like to help if you explain what the overall objective is.
 

abbas50

New Member
Joined
Dec 6, 2019
Messages
37
Office Version
2016
Platform
Windows
You never said anything in your original posting about deleting rows.

Or other sheets or a lot of what your wanting now.
I normally only like to help if you explain what the overall objective is.
im sorry i thought i could achieve it myself if i got to know the way :) anyway im sorry i do apologise for making you mad
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,272
im sorry i thought i could achieve it myself if i got to know the way :) anyway im sorry i do apologise for making you mad
I'm not mad. We see this all the time. Users write or find code from numerous sources and then try to paste them all together thinking they can get what they want.
In a lot of cases this does not work or is not the best way to do things.

If you still want help on this question and if you would provide specific details I may be able to help you.

Please do not say look at my code and you will see what I want.
 

abbas50

New Member
Joined
Dec 6, 2019
Messages
37
Office Version
2016
Platform
Windows
i will never say look at my code fully and provide me im trying to achieve and learn :) im newbie at coding

and thanks for your time replying at me i do appreciate this

what i want is your coee of last 12 shown on a list box i can delete my last entry if i got a mistake thats all and when i did it on the previous code it deletes another row i cant figure it out and i have multiple sheet it seems i cant see second sheet entry when im on another sheet with my userform thank you for your time and effort

I'm not mad. We see this all the time. Users write or find code from numerous sources and then try to paste them all together thinking they can get what they want.
In a lot of cases this does not work or is not the best way to do things.

If you still want help on this question and if you would provide specific details I may be able to help you.

Please do not say look at my code and you will see what I want.
 

Forum statistics

Threads
1,081,855
Messages
5,361,716
Members
400,650
Latest member
tcisrly

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top