Last Display on userform to a Listbox

A

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?
 
Sort by date Sort by votes
As your listbox is single select use
VBA Code: 
Private Sub CommandButton3_Click()
    If Me.ComboBox1.Value = "" Then Exit Sub
    With Me.lstdisplay
        Sheets(Me.ComboBox1.Value).Range("A" & Rows.Count).End(xlUp).Offset(.ListIndex - .ListCount + 1).EntireRow.Delete
        .RemoveItem (.ListIndex)
    End With
End Sub
and use this to populate the listbox
VBA Code: 
Dim HowManyRows As Long
HowManyRows = 12
Dim Lastrow As Long
Lastrow = Sheets(sht).Cells(Rows.Count, "A").End(xlUp).Row
lstdisplay.ColumnCount = 15
lstdisplay.Clear
lstdisplay.List = Sheets(sht).Cells(Lastrow - HowManyRows + 1, 1).Resize(HowManyRows, 15).Value
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
I cant thank you enough worked like a charm :D i am smiling now :D u made my life easier ty so much :)


Fluff said:
As your listbox is single select use
VBA Code: 
Private Sub CommandButton3_Click()
    If Me.ComboBox1.Value = "" Then Exit Sub
    With Me.lstdisplay
        Sheets(Me.ComboBox1.Value).Range("A" & Rows.Count).End(xlUp).Offset(.ListIndex - .ListCount + 1).EntireRow.Delete
        .RemoveItem (.ListIndex)
    End With
End Sub
and use this to populate the listbox
VBA Code: 
Dim HowManyRows As Long
HowManyRows = 12
Dim Lastrow As Long
Lastrow = Sheets(sht).Cells(Rows.Count, "A").End(xlUp).Row
lstdisplay.ColumnCount = 15
lstdisplay.Clear
lstdisplay.List = Sheets(sht).Cells(Lastrow - HowManyRows + 1, 1).Resize(HowManyRows, 15).Value
Click to expand...
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0
The following code is to present in the listbox only 10 records:

VBA Code: 
  Dim i As Long, wlines As Long, n As Long, a(), m As Long
  lstdisplay.ColumnCount = 15
  wlines = 10
  n = 1
  ReDim a(1 To wlines, 1 To 15)
  If nextrow.Row > wlines Then
    m = nextrow.Row - (wlines - 1)
  Else
    m = 2
  End If
  For i = m To nextrow.Row
    For j = 1 To 15
      a(n, j) = Sheets(sht).Cells(i, j)
    Next
    n = n + 1
  Next
  lstdisplay.List = a

---------------------------------------------------------------------
I put your complete code:

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
  'check for values
  If Me.ComboBox1.Value = "" Or ComboBox1.ListIndex = -1 Then
    MsgBox "Please Select A sheet"
    ComboBox1.SetFocus
    Exit Sub
  End If
  sht = ComboBox1.Value
  '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 i As Long, wlines As Long, n As Long, a(), m As Long
  lstdisplay.ColumnCount = 15
  wlines = 10
  n = 1
  ReDim a(1 To wlines, 1 To 15)
  If nextrow.Row > wlines Then
    m = nextrow.Row - (wlines - 1)
  Else
    m = 2
  End If
  For i = m To nextrow.Row
    For j = 1 To 15
      a(n, j) = Sheets(sht).Cells(i, j)
    Next
    n = n + 1
  Next
  lstdisplay.List = a
End Sub
 
Upvote 0
works like a charm too ty somuch for trying to help me and u did help me :) appreciated my friend :) you people r awesome
 
Upvote 0
I'm glad to help you. Thanks for the feedback.:cool:
 
Upvote 0
Fluff said:
As your listbox is single select use
VBA Code: 
Private Sub CommandButton3_Click()
    If Me.ComboBox1.Value = "" Then Exit Sub
    With Me.lstdisplay
        Sheets(Me.ComboBox1.Value).Range("A" & Rows.Count).End(xlUp).Offset(.ListIndex - .ListCount + 1).EntireRow.Delete
        .RemoveItem (.ListIndex)
    End With
End Sub
and use this to populate the listbox
VBA Code: 
Dim HowManyRows As Long
HowManyRows = 12
Dim Lastrow As Long
Lastrow = Sheets(sht).Cells(Rows.Count, "A").End(xlUp).Row
lstdisplay.ColumnCount = 15
lstdisplay.Clear
lstdisplay.List = Sheets(sht).Cells(Lastrow - HowManyRows + 1, 1).Resize(HowManyRows, 15).Value
Click to expand...
Hello,
Fluff, your code also works for me, thanks a million for you help!
 
Upvote 0
Glad it helps & thanks for letting us know.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Help with VBA code
Replies
1
Views
327
MrPumper
M
M
  • Question
Userform listbox not populating when userform is first opened
Replies
1
Views
154
Trevor G
Trevor G
R
  • Question
Userform Bugginess
Replies
2
Views
64
RockandGrohl
R
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
59
brianv
B
M
  • Question
Weird combobox behaviour
Replies
1
Views
48
mmmreece
M

Forum statistics

Threads
1,215,647
Messages
6,126,006
Members
449,280
Latest member
Miahr

Share this page

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