Last Display on userform to a Listbox

abbas50

Board Regular
Joined
Dec 6, 2019
Messages
51
Office Version
  1. 2016
Platform
  1. 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?
 

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.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,931
Messages
6,110,745
Members
448,295
Latest member
Uzair Tahir Khan

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