dropdown initialize

[orzik]

Hello,
I am redesigning a User form into a spreadsheet "form" controls.
I used below "code 1" to dynamically populate drop-down list (combo box) with tab names as they are created with my tool (tab names i.e. tab1, tab2, tab3 etc) It was working fine on a User Form used with "Private Sub UserForm_Initialize()". My challenge is to "initialize" the drop-down placed directly on a spreadsheet (ActiveX Control)
Any advice appreciated.
Cheers,


Code 1

        Dim VerTabName As String
        VerTabName = "v" & i & " LOE"

        With vtabs_cbo
            For i = 1 To 33
                VerTabName = "v" & i & " LOE"    'v1 LOE
                If SheetExists(VerTabName) = True Then               
                    .AddItem VerTabName
                End If
            Next i
        End With

Code 2
The below code I've found on MrExcel boards and works great. It is used in the code above.

Private Function SheetExists(sname) As Boolean
'   Returns TRUE if sheet exists in the active workbook
    Dim x As Object
    On Error Resume Next
    Set x = ActiveWorkbook.Sheets(sname)
    If Err = 0 Then SheetExists = True _
       Else SheetExists = False
End Function

orzik

 

[orzik]

Anyone? any ideas?