1

My code has suddenly stopped working. I am getting compile error when I run this macro Image 1enter image description here. the combobox1 object definitely exists Image 2 enter image description here

I really cannot think what has changed for it to fail and have not tried any other code. I have seen a similar problem on membership classes but cannot transpose onto this.

The code

Public Sub List()

    Dim rs1 As Object
    Set rs1 = CreateObject("ADODB.Recordset") ' this is the Selection Record Set for Combobox1, which is on Sheet 1
    Dim sqlstrSel As String
    sqlstrSel = " select * from [30List]"

    'Debug.Print sqlstrSel
                    
    Call connectDatabase ' populates Combobox with Selid and Selname
    rs1.Open sqlstrSel, DBCONT
    Sheet1.ComboBox1.Clear
    Sheet1.ComboBox1.AddItem "------1. Select Project"

    If rs1.RecordCount > 0 Then
        For i = 1 To rs1.RecordCount
             Sheet1.ComboBox1.AddItem rs1(0) & " - " & rs1(1) & " - " & rs1(2) & " - " & rs1(3) & " - " & rs1(4) & " - " & rs1(5)
             rs1.movenext
        Next i
    Else
        MsgBox "FFS"
    End If
             
    Sheet1.ComboBox1.Text = Sheet1.ComboBox1.List(0)

End Sub
Improve this question
6
  • 2
    Maybe try deleting and re-creating the combobox Commented Sep 24 at 20:01
  • 2
    Or see what you get from this code: Dim obj As Object: For Each obj In Sheet1.OLEObjects: Debug.Print obj.Name: Next obj Commented Sep 24 at 20:04
  • Adding a watch on Sheet1 or on Sheet1.ComboBox1 would be more useful that a screenshot; an incomplete screenshot at that. Commented Sep 24 at 20:11
  • 2
    Did you declare a variable with the same name Sheet1 (maybe as a Global of type Worksheet) somewhere in that same project? That would "shadow" the worksheet codename and cause the compile error you're seeing. Commented Sep 24 at 20:16
  • 8
    Have you recently updated Excel? ActiveX is now disabled by default. You can re-enable it in the Trust Center Commented Sep 24 at 22:48
Related questions
651
How can I avoid using Select in Excel VBA?
606
Is there a way to crack the password on an Excel VBA Project?
0
Merge mysql recordset in vba
Load 5 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.