error 7777 during execution

[TonC]

Hello,

Error 7777 during execution
Invalid use property ListIndex

Is there a solution to tackle this error?

Greetings,
TonC

[arnelgp]

you cannot assign value to ListIndex property of listbox, without Setting Focus to it first.

[MyListbox].SetFocus
[MyListBox].ListIndex = theNewIndexHere

You may also try the code (without setting focus) to:

[MyListbox] = [MyListbox].ItemData(theNewIndexHere)

[Gasman]

Hello,

Error 7777 during execution
Invalid use property ListIndex

Is there a solution to tackle this error?

Greetings,
TonC

Would really help if you showed the code, and where it errors?
We are not mind readers.

[Aussiebear]

This error typically occurs when:

1. If you trying to set the ListIndex property of a control that doesn't allow it, or when there's no item selected, or the index is out of bounds. While ListIndex is read/write for UserForm ListBoxes (meaning you can set it to select an item), it can be read-only in certain contexts or for certain types of controls (e.g., in MS Access sometimes, or for some older Excel control types). Given that you continue to post in the Access sub forum then perhaps this may be the issue.
2. You are still using an ActiveX ListBox on a worksheet, but now attempting to manipulate ListIndex in a way it doesn't support, or you've misunderstood how ListIndex works for selection.

So let's break down the likely causes and solutions, especially if you've attempted to switch to a UserForm, or are trying to use ListIndex with your existing setup:
Common Scenarios for "Invalid Use of Property ListIndex":

• Trying to Set ListIndex When No Item is Selected or List is Empty:
  
  • If ListBox1.ListCount is 0 (the ListBox is empty), trying to set ListBox1.ListIndex = 0 or any other value will cause this error because there are no items to select.
  • Similarly, if the ListBox is populated but nothing is selected (ListBox1.ListIndex would be -1), and they try to manipulate it directly without first selecting an item (e.g., Me.ListBox1.ListIndex = Me.ListBox1.ListIndex + 1 when ListIndex is -1), it can fail.
  
  
• ListIndex on a Multi-Select ListBox (Incorrect Selection Method):
• If the MultiSelect property of the ListBox is set to fmMultiSelectMulti (allowing multiple selections), you generally cannot use ListBox1.ListIndex = X to select an item. Instead, you need to use the Selected array property: ListBox1.Selected(X) = True. If you are trying to set ListIndex directly on a multi-select ListBox, this error will occur.
• While we are guideing you towards UserForms, a typo or referring to a different type of control that looks like a ListBox but isn't, could cause this.
• If the code attempting to set or use ListIndex runs before the ListBox has been populated with items, then ListCount will be 0, and ListIndex will be -1, leading to the error when trying to access or set it.
• Incorrect Object Reference (Less likely now, but possible):
• List Not Populated Yet:

How to Help us to help you to Debug and Solve:
1. Confirm the ListBox Type and Context (Again!):

• Can you confirm: "Is this ListBox on a UserForm or directly on an Excel worksheet?"
• If it's on a UserForm, excellent. If it's on a worksheet, the previous advice (in your previous thread) about using a UserForm still stands as the primary solution.

2. Check the MultiSelect Property:

• Crucial Step: Can you go to the Properties window for your ListBox (on the UserForm).
• Find the MultiSelect property.
• If MultiSelect is set to 1 - fmMultiSelectMulti or 2 - fmMultiSelectExtended: This is almost certainly the cause.
• Solution for Multi-Select: They cannot use ListBox1.ListIndex = X. They must use ListBox1.Selected(X) = True.

3. Check for Empty ListBox:

• You could try and insert a debug line before the line causing the error:

Debug.Print "ListBox1.ListCount: " & Me.ListBox1.ListCount
        Debug.Print "ListBox1.ListIndex: " & Me.ListBox1.ListIndex
        Stop ' This will pause execution so they can see the Immediate Window (Ctrl+G)

• If ListCount is 0, then the list is empty, and they need to ensure it's populated before trying to manipulate ListIndex.
• If ListIndex is -1 and they're trying to add to it, that's also an issue.

4. Review the Exact Line of Code Causing the Error:

• Can you indicate the exact line of code that the debugger highlights when the error occurs. This is the most direct way to pinpoint the problem.This error typically occurs when:
  
  1. Are you trying to set the ListIndex property of a control that doesn't allow it, or when there's no item selected, or the index is out of bounds. While ListIndex is read/write for UserForm ListBoxes (meaning you can set it to select an item), it can be read-only in certain contexts or for certain types of controls (e.g., in MS Access sometimes, or for some older Excel control types). Please test for the afore mentioned scenarios.
  2. Are you still using an ActiveX ListBox on a worksheet, but now attempting to manipulate ListIndex in a way it doesn't support, or you've misunderstood how ListIndex works for selection.
  
  Let's break down the likely causes and solutions, especially if you've attempted to switch to a UserForm as previousily suggested, or are trying to use ListIndex with their existing setup:
  Common Scenarios for "Invalid Use of Property ListIndex":
  
  
  • Trying to Set ListIndex When No Item is Selected or List is Empty:
    
    • If ListBox1.ListCount is 0 (the ListBox is empty), trying to set ListBox1.ListIndex = 0 or any other value will cause this error because there are no items to select.
    • Similarly, if the ListBox is populated but nothing is selected (ListBox1.ListIndex would be -1), and they try to manipulate it directly without first selecting an item (e.g., Me.ListBox1.ListIndex = Me.ListBox1.ListIndex + 1 when ListIndex is -1), it can fail.
    
    
  • ListIndex on a Multi-Select ListBox (Incorrect Selection Method):
  • If the MultiSelect property of the ListBox is set to fmMultiSelectMulti (allowing multiple selections), you generally cannot use ListBox1.ListIndex = X to select an item. Instead, you need to use the Selected array property: ListBox1.Selected(X) = True. If you are trying to set ListIndex directly on a multi-select ListBox, this error will occur.
  • While we are guideing you towards UserForms, a typo or referring to a different type of control that looks like a ListBox but isn't, could cause this.
  • If the code is attempting to set or use ListIndex runs before the ListBox has been populated with items, then ListCount will be 0, and ListIndex will be -1, leading to the error when trying to access or set it.
  • Incorrect Object Reference (Less likely now, but possible):
  • List Not Populated Yet:
  
  Help us to help you Debug and Solve:
  1. Confirm the ListBox Type and Context (Again!):
  
  
  • Could you confirm: "Is this ListBox on a UserForm or directly on an Excel worksheet?"
  • If it's on a UserForm, excellent. If it's on a worksheet, the previous advice about using a UserForm still stands as the primary solution.
  
  2. Check the MultiSelect Property:
  
  
  • Crucial Step: Please go to the Properties window for your ListBox (on the UserForm).
  • Find the MultiSelect property.
  • If MultiSelect is set to 1 - fmMultiSelectMulti or 2 - fmMultiSelectExtended: This is almost certainly the cause.
  • Solution for Multi-Select: They cannot use ListBox1.ListIndex = X. You must use ListBox1.Selected(X) = True.
  
  3. Check for Empty ListBox:
  
  
  • Could you please try to insert a debug line before the line causing the error:
  
  
  

  Debug.Print "ListBox1.ListCount: " & Me.ListBox1.ListCount
  Debug.Print "ListBox1.ListIndex: " & Me.ListBox1.ListIndex
  Stop ' This will pause execution so you can see the Immediate Window (Ctrl+G)

  • If ListCount is 0, then the list is empty, and they need to ensure it's populated before trying to manipulate ListIndex.
  • If ListIndex is -1 and you're trying to add to it, that's also an issue.
  
  4. Review the Exact Line of Code Causing the Error: As Gasman has suggested a presentation of your current code would be helpful.
  
  
  • We ask you for the exact line of code that the debugger highlights when the error occurs. This is the most direct way to pinpoint the problem.

[TonC]

Hello, here is the code in UserForm1,

Option Compare Database
Option Explicit   'AussieBear 1 ste post

Private Sub btnPageDown_Click()
Dim lngPageSize As Long
Dim lngNewTopIndex As Long
Dim lngLastPossibleTopIndex As Long

    ' Determine the page size
    lngPageSize = 40
    ' Adjust as needed
    ' Calculate the new TopIndex for Page Down
    lngNewTopIndex = Me.ListBox1.listIndex + lngPageSize
    ' Calculate the maximum possible TopIndex to avoid going past the end
    ' ListCount - 1 is the last item index
    ' Subtracting (lngPageSize - 1) helps ensure the last page shows full content if possible
    lngLastPossibleTopIndex = Me.ListBox1.ListCount
'    Me.ListBox1.ItemData
    ' ListRows is often useful here
    If lngLastPossibleTopIndex < 0 Then
        lngLastPossibleTopIndex = 0
        ' Handle empty listbox
        If lngNewTopIndex > lngLastPossibleTopIndex Then
            lngNewTopIndex = lngLastPossibleTopIndex
        End If
    End If
                'Aussiebear has just replied to a thread you have subscribed to entitled - error 7777 during execution - in the Access Help forum of VBA Express Forum.
    
        Debug.Print "ListBox1.ListCount: " & Me.ListBox1.ListCount
        Debug.Print "ListBox1.ListIndex: " & Me.ListBox1.listIndex
        Stop ' This will pause execution so they can see the Immediate Window (Ctrl+G)
        'Immediate Window (Ctrl+G)
        'ListBox1.ListCount: 382
        'ListBox1.ListIndex: -1
    
    ' Apply the new TopIndex
    Me.ListBox1.listIndex = lngNewTopIndex   'this line causes error 7777

End Sub

Private Sub btnPageUp_Click()

Dim lngPageSize As Long
Dim lngNewTopIndex As Long
    ' Determine the page size (e.g., number of visible items)
    ' This might require some trial and error or calculation based on font size and listbox height
    ' For simplicity, let's assume a fixed page size for now
    lngPageSize = 40
    ' Adjust as needed
    ' Calculate the new TopIndex for Page Up
    lngNewTopIndex = Me.ListBox1.listIndex - lngPageSize
    ' Ensure we don't go below 0
    If lngNewTopIndex < 0 Then
        lngNewTopIndex = 0
    End If
    ' Apply the new TopIndex
    Me.ListBox1.listIndex = lngNewTopIndex

End Sub
Private Property Get listIndex() As Variant
        ListBox1.listIndex = ListBox1.listIndex + 40   'for page down.
        ListBox1.listIndex = ListBox1.listIndex - 40   'for page up.
End Property

Private Sub ListBox1_GotFocus() ' opmerking
    With Me.ListBox1
        
[ListBox1].SetFocus
        
[ListBox1].listIndex = 0
    End With
'Private Sub lstItems_GotFocus()
'[lstItems].SetFocus
'[lstItems].ListIndex = 0
End Sub

hope this helps
Greetings TonC

[TonC]

Oh, I forgot
The term ListRows is not available

[Gasman]

If this is a userform, why is it in the Access forum?

Which line causes the error?
What are the values when that happens?

[TonC]

Hello Gasman,
I ask this question because I use Microsoft Office professional Plus access 2016, Part Access. So I really dont know why you asking me this. Its an Db which I use for my Music repertoire.

 Debug.Print "ListBox1.ListCount: " & Me.ListBox1.ListCount        Debug.Print "ListBox1.ListIndex: " & Me.ListBox1.listIndex
        Stop ' This will pause execution so they can see the Immediate Window (Ctrl+G)
        'Immediate Window (Ctrl+G)
        'ListBox1.ListCount: 382
        'ListBox1.ListIndex: -1
    
    ' Apply the new TopIndex
    Me.ListBox1.listIndex = lngNewTopIndex   'this line causes error 7777

Greetings
TonC

[Gasman]

Well (to me at least) a Userform is in Excel? A form is in Access? Plus you posted the page down/up for a listbox in the Excel section previously?
So for me at least, I was confused as to what app the code is actually meant to be in?

So what is the value of lngNewTopIndex? What value is the Me.ListBox1.ListCount?
Listbox index starts at 0.

Also, what are you doing with the With?
The whole idea of using with is to not have to use the object name everywhere?

Private Sub ListBox1_GotFocus() ' opmerking
    With Me.ListBox1
        .SetFocus
        .listIndex = 0
    End With
End Sub

I think I have already mentioned that learning some simple debugging would help you tremendously?
This would be one of those moments.

https://www.youtube.com/results?sear...bug+access+vba

[TonC]

Gasman,
I've made indead a mistake. I posted my question in Excell help. I'am sorry I will do better in the future. Now I know the diffrence between UserForm and Form.
Your comment about looking for how to use debugging is off course a very good idea. I'm busy at the moment to read about debugging as well in books and internet.
I hope you understand I'am 66 y old, so it takes a bit longer to learn about debugging. And my dog ​​complains to my wife, that I have no time to play with her.

I already send my code to AussieBear en hopes he can come with good advice.

Greeting
TonC

[Gasman]

Well I am 71 next week. :-)
Unless you know what those values are, it is hard to decipher the issue.
Walking your code line by line would also help.

If you are going to require help privately, then I will step away, as I will not know the state of play. Plus that assistance helps no-one but you, which is not really what forums are for, in my mind at least.
I will leave you to it.

[Aussiebear]

Gasman was right all along. I initially thought this was for an excel workbook. So TonC please scrap all the previous code and we will start again.

' Assuming your ListBox is named lstMyData and is on an Access Form named frmMyForm
' And it's bound to a table like tblLargeData
' You'd also need variables for CurrentPage, RecordsPerPage, etc.
Dim CurrentPage As Long 
' Stored in a form property or global
Dim RecordsPerPage As Long 
' Stored in a form property or global
' In your Form's Open event or module

Private Sub Form_Open(Cancel As Integer)
    CurrentPage = 1    RecordsPerPage = 20 
    ' Or whatever number of visible rows
    Call RefreshListBoxPage
End Sub

Private Sub btnPageDown_Click()
    CurrentPage = CurrentPage + 1
    Call RefreshListBoxPage
End Sub

Private Sub btnPageUp_Click()
    If CurrentPage > 1 Then
        CurrentPage = CurrentPage - 1
        Call RefreshListBoxPage
    Else
        CurrentPage = 1 
        ' Stay on first page
    End If
End Sub

Private Sub RefreshListBoxPage()
    Dim strSQL As String
    Dim lngOffset As Long
    Dim lngTotalRecords As Long
    ' Get total records first (important for last page logic)
    lngTotalRecords = DCount("*", "tblLargeData").   ' <------- Replace tblLargeData with actual table/query name
    lngOffset = (CurrentPage - 1) * RecordsPerPage
    ' Ensure we don't go past the end
    If lngOffset >= lngTotalRecords And lngTotalRecords > 0 Then
        CurrentPage = Int((lngTotalRecords - 1) / RecordsPerPage) + 1
        lngOffset = (CurrentPage - 1) * RecordsPerPage
    End If
    ' Construct SQL for paging (different approaches exist, this is one)
    ' Using TOP N and ORDER BY is common, or filtering by primary key range.
    ' A more robust way involves subqueries for row numbering if no sequential ID.
    ' For simplicity, let's assume a simple table and we'll "simulate" paging by ID or record number.
    ' This is simplified and might need adjustment based on data structure and sorting.
    ' Option A: If you have a unique, sequential ID field (e.g., [ID])
        ' Not true paging, but works if you know the ID range for each page
        ' This is often the most practical in Access without complex temp tables.
        ' Needs refinement to ensure exact 'RecordsPerPage'
        ' (Not truly generic "page down" but more like "next set of IDs")
    ' Option B: Using a temp query or recordset to get the right range
        ' This is more complex and beyond a simple example, but is the most accurate paging.
        ' For a simple, common approach where sorting is stable and you can get 'next N'
        ' This example assumes a sorted table.
    strSQL = "SELECT TOP " & RecordsPerPage & " * FROM tblLargeData " & "WHERE ID NOT IN (SELECT TOP " _
    & lngOffset & " ID FROM tblLargeData ORDER BY ID) " & "ORDER BY ID;"
    ' IMPORTANT: You need a robust way to get the TOP N records starting from an offset.
    ' Access SQL does NOT have a simple OFFSET clause like SQL Server or MySQL.
   
    ' Common workarounds:
    ' 1. Use a temporary table with row numbers.
    ' 2. Filter by a primary key range if the key is sequential.
    ' 3. Use an ADO Recordset to move to the desired record.
    ' If using a simple ID-based paging (less robust "paging"):
    Dim FirstIDOnPage As Variant
    On Error Resume Next 
    ' In case DLookup fails for empty table or invalid offset
    FirstIDOnPage = DLookup("ID", "tblLargeData", "ID IN (SELECT TOP " & lngOffset + 1 & " ID FROM tblLargeData ORDER BY ID) ORDER BY ID")
    On Error GoTo 0
    If IsNull(FirstIDOnPage) Then
        ' Handle end of data, revert to previous page
        If CurrentPage > 1 Then
            CurrentPage = CurrentPage - 1
            ' No Exit Sub here, we want to re-evaluate with the corrected CurrentPage
        End If
        ' If FirstIDOnPage is Null AND we couldn't go back a page (because CurrentPage was 1), then there's likely no data, or we're at the absolute end.
        ' A more robust error handling might be needed here, or simply allow the current strSQL to run (it will likely result in an empty list)
       Exit Sub 
       ' Exit if no valid FirstIDOnPage could be found or determined
    End If
    strSQL = "SELECT * FROM tblLargeData WHERE ID >= " & FirstIDOnPage & " ORDER BY ID;"
    Me.lstMyData.RowSource = strSQL        Me.lstMyData.Requery
    End Sub

See if this assists you.

[TonC]

Hello AussieBear,
I'am Dizzy from your code, but I going to do my best to get it to work, It probably takes a few months. You will hear from me later, thats a promise.
Many thanks, and off course all others.

Greetings
TonC

What to do with the status of this thread ? Not solved, stays it open or must I do something else ?

[Aussiebear]

Lets just see if the experts can improve it first. I'm confident that Gasman & arnelgp can knock it into shape.

[arnelgp]

if this is Access Listbox, why not replace it with a Datasheet form or a Continous form?

[arnelgp]

another alternative code:

Private Sub btnPageDown_Click()
    With Me.ListBox1
        .SetFocus
        If .listIndex < 0 Then .listIndex = 0
    End With
    MySendKeys "{PGDN}"
End Sub


Private Sub btnPageUp_Click()
    Me.ListBox1.SetFocus
    MySendKeys "{PGUP}"
End Sub




Public Sub MySendKeys(ByVal strText As String, Optional ByVal Wait As Boolean = False)
    With CreateObject("WScript.Shell")
        .SendKeys strText, Wait
    End With
End Sub

[Aussiebear]

Well there's a realistic alternative?