Scroll vertical scrollbar to the bottom of list

[kelly mort]

Hi
Can I get a script to scroll the vertical scrollbar to the bottom of the list of a listbox?

I load the listbox each time I add data . So I will like to see the last item.

Thanks

 

[kelly mort]

Okay. Sure

 

[Domenic]

First, before sorting the data and loading it into your listbox, assign the last value to a variable, let's say myVar. Then try...

    With Me.ListBox1
        .ListIndex = Application.Match(myVar, .List, 0) - 1
    End With

Hope this helps!

 

[kelly mort]

First, before sorting the data and loading it into your listbox, assign the last value to a variable, let's say myVar. Then try...

    With Me.ListBox1
        .ListIndex = Application.Match(myVar, .List, 0) - 1
    End With

Hope this helps!

I am confused here:

How do I assign the last value to the variable?

Is that going to be the entire row? Because the data starts from column B to column P and I am sorting from C to D using C as the sort criteria.

I will be glad if you can give some cool example for me.

Regards
Kelly

 

[Domenic]

Let's say that Sheet1, Column B through Column P, contains the data. And, let's say that Column C contains unique values. First get the last value from Column C....

    Dim vLastValue As Variant

    With Worksheets("Sheet1")
        vLastValue = .Cells(.Rows.Count, "C").End(xlUp).Value
    End With

Then select that item from your listbox...

    With Me.ListBox1
        .ListIndex = Application.Match(vLastValue, Application.Index(.List, 0, [COLOR=#ff0000]2[/COLOR]), 0) - 1
    End With

Note that the number 2 in red refers to the second column, since Column C is the second column in your listbox.

Hope this helps!

 

[kelly mort]

I am getting a mismatch error on the line
.ListIndex = Application.Match. .......

I placed that code after sorting the data and loading the listbox.
Is that the correct syntax to go?

 

[Domenic]

First, get the last value, then sort, and then load into the listbox. Since I'm not going to be available until sometime later today, I'm going to guess that you have dates in Column C (or which ever column you're using). If so, try...

   Dim dblLastValue As Double
    
   With Worksheets("Sheet1")
        dblLastValue = .Cells(.Rows.Count, "C").End(xlUp).Value
    End With

and

    With Me.ListBox1
        .ListIndex = Application.Match(dblLastValue, Application.Index(.List, 0, 2), 0) - 1
    End With

Does this help?

 

[kelly mort]

Still the same error.

Okay I think i am getting the idea where the error is coming from.

Column C contains names which could be from 2 words to 4 words long.

 

[Domenic]

Can you post the complete code that you're using?

 

[kelly mort]

Private Sub CmdAdd_Click()
    term = Me.CmbClass.Value
    If Me.Rw2.Text = "" Then
        MsgBox "Fill the the name box!", vbInformation, "Slimsoft System"
    Exit Sub
    End If
    If Application.CountIf(Sheets(term).Range("C7:C1007"), Me.Rw2.Text) > 0 Then
        MsgBox "The name " & Me.Rw2.Text & " already exists", vbInformation, "Slimsoft Systems"
    Exit Sub
    End If
    With Sheets(term)
    Set Drng = .Range("B7")
    Set lrRng = .Cells(.Rows.Count, Drng.Column).End(xlUp).Offset(1, 0)
        lrRng.Value = Application.Max(Range(Drng, lrRng.Offset(-1, 0))) + 1
    For i = 1 To 24
        lrRng.Offset(0, i).Value = Me.Controls("Rw" & i + 1).Value
    Next i
    If Me.Rw26.Value <> vbNullString Then
        lrRng.Offset(0, 25).Value = CDate(Me.Rw26.Value)
    Else
        lrRng.Offset(0, 25).Value = Me.Rw26.Value
    End If
        lrRng.Offset(0, 26).Value = Me.Rw27.Value
        Me.Rw2.SetFocus
        Dim vLastName As String
        With Worksheets(term)
        vLastName = .Cells(.Rows.Count, "C").End(xlUp).Value
        End With
    Call SortIt
    Me.OptClear.Value = False
    Me.OptSearch.Value = False
    Call LookupCurrentName
    With Me.lstView
            '.ListIndex = Application.Match(vLastName, Application.Index(.List, 0, 2), 0) - 1
        End With
    For i = 1 To 27
        Me.Controls("Rw" & i).Value = ""
    Next i
    Call Switch_Controls
    End With
End Sub

 

[Domenic]

I see that you've named your listbox lstView. This suggests that you're actually using a listview instead of a listbox. Can you please confirm which one you're actually using?