Buda çalışan hali.Önce recordset ile listboxa alındı recordset kapatılp tekrar açıldı ve comboya alındı veriler.

Private Sub UserForm_Initialize()

Dim syf, TmpVr As Worksheet, k As Byte



Set TmpVr = Sheets("TmpSilme")

    TmpVr.Unprotect "4455"

    TmpVr.Cells.Clear

  Hcr = 1

  

    For Each syf In Worksheets

        If InStr(1, Ekleme, "|" & syf.Name & "|", 1) = 0 Then

        TmpVr.Range("a" & Hcr) = syf.Name

        Hcr = Hcr + 1

        End If

    Next syf



Set Cn = CreateObject("Adodb.Connection")

Set Rs = CreateObject("adodb.recordset")



Cn.Open "provider=Microsoft.ACE.OLEDB.12.0;data source=" & _

                      ThisWorkbook.FullName & ";extended properties=""excel 8.0;hdr=no"""



SQL = "select * from [TmpSilme$A:A] where not isnull(f1) order by [F1]"



Rs.Open SQL, Cn, 1, 1 '

'  Eðer Hiç Kayýt Yoksa

If Rs.RecordCount = 0 Then

    Rs.Close

    Set Rs = Nothing

    MsgBox "Kayýt Bulunamadý.", vbCritical, "Veri Yok"

    Exit Sub

End If



'ComboBox1.Column = Rs.GetRows



With Me.ListBox1

    .ColumnCount = Rs.Fields.Count

    .Column = Rs.GetRows



'        .ColumnHeads = True

End With



Rs.Close

Rs.Open SQL, Cn, 1, 1

ComboBox1.Column = Rs.GetRows



Rs.Close

Cn.Close

Set Rs = Nothing

Set Cn = Nothing



End Sub

---

Böylede oluyor önemli olan recordset kapatmak combo başta yada sonda olması şart değil.

Private Sub UserForm_Initialize()

Dim syf, TmpVr As Worksheet, k As Byte



Set TmpVr = Sheets("TmpSilme")

    TmpVr.Unprotect "4455"

    TmpVr.Cells.Clear

  Hcr = 1

  

    For Each syf In Worksheets

        If InStr(1, Ekleme, "|" & syf.Name & "|", 1) = 0 Then

        TmpVr.Range("a" & Hcr) = syf.Name

        Hcr = Hcr + 1

        End If

    Next syf



Set Cn = CreateObject("Adodb.Connection")

Set Rs = CreateObject("adodb.recordset")



Cn.Open "provider=Microsoft.ACE.OLEDB.12.0;data source=" & _

                      ThisWorkbook.FullName & ";extended properties=""excel 8.0;hdr=no"""



SQL = "select * from [TmpSilme$A:A] where not isnull(f1) order by [F1]"



Rs.Open SQL, Cn, 1, 1 '

'  Eğer Hiç Kayıt Yoksa

If Rs.RecordCount = 0 Then

    Rs.Close

    Set Rs = Nothing

    MsgBox "Kayıt Bulunamadı.", vbCritical, "Veri Yok"

    Exit Sub

End If



ComboBox1.Column = Rs.GetRows



Rs.Close

Rs.Open SQL, Cn, 1, 1

With Me.ListBox1

    .ColumnCount = Rs.Fields.Count

    .Column = Rs.GetRows



'        .ColumnHeads = True

End With



Rs.Close

Cn.Close

Set Rs = Nothing

Set Cn = Nothing



End Sub