Excel VBA Compile error Listbox

Question

I'm nearing completion on a custom excel workbook. I'm having a irritating issue where the below code works but gives a compile error on load. I've tried searching for the solution but being so new to VBA I'm not even sure what could be causing the issue. It highlights listbox1 but I have listbox1 in the sheet noted.

(Compile Error "Method or Data member not found")

Private Sub ListBox1_Click()
Sheet2.TextBox1.Value = " "
Dim i As Long
i = Sheet2.ListBox1.ListIndex
If i < -1 Then Exit Sub
Sheet2.TextBox1.Value = Sheet1.Range("C" & (i + 4))
End Sub

Thank You

When you say on load, is it when the workbook is opening? Or is it a form load? — MatthewD
– MatthewD, Commented Jan 13, 2016 at 19:23
@MatthewD Workbook opening only, the above code works if you ignore the error or click past it. The code basically pulls a named range from another sheet and if/when a user clicks a line item it populates a textbox with additional details. — ImNotLeet
– ImNotLeet, Commented Jan 13, 2016 at 19:23

MatthewD · Accepted Answer · 2016-01-13 19:25:52Z

1

It is probably due to the loading of the values into the list. Try something like this.

Create a global boolean variable

Private bDoneLoading as Boolean

Set it to true in the workbook open function after anything else you may have i this function

Private Sub Workbook_Open()

    'Any other code

    bDoneLoading = True
End Sub

Add a check to make sure the workbook has loaded.

Private Sub ListBox1_Click()

    If bDoneLoading = false Then
        Exit sub
    End If

    Sheet2.TextBox1.Value = " "
    Dim i As Long
    i = Sheet2.ListBox1.ListIndex
    If i < -1 Then Exit Sub
    Sheet2.TextBox1.Value = Sheet1.Range("C" & (i + 4))
End Sub

answered Jan 13, 2016 at 19:25

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ImNotLeet Over a year ago

Thank you! One follow-on does it matter where I declare the global variable?

MatthewD Over a year ago

Yes, they have to be declared at the top of the code. Above any other subs or functions.

ImNotLeet Over a year ago

Ty sir for the assistance, easy solution to a huge headache.

Collectives™ on Stack Overflow

Excel VBA Compile error Listbox

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related