Xtykutl

I have a small program VBA which is fact a userform which allow me to display all the existing worksheet of one open workbook on which I am working. Via this userform I can select another sheet and by clicking the sheet via this userform, it reorients me to the desired worksheet.
Now I tried to modifiy a bit of part of this program in order to do it the same but with all my open workbooks. It means if I have several workbook open, I would like that my userform allows me to display all the existing open workbook and by selecting the desired workbook via the userform, it reorients me to this workbook (it means that the selected workbook in the userform is activated and selected). The problem is when I run the code, I have an error message 424 VBA Run-time error '424' Object Required Error…

PS:really sorry for the format of my Code but I do not manage to put it in the right format..

Thanks in advance for your help
Xavi

Here please find the original code which works for userform related to worksheet (this one works):

Sub UserForm_Initialize()

    Dim n As Long

    Dim msg As String

    Dim i As Long

    Dim s As String

    Dim sht As Worksheet



    Do

        n = n + 1

        Me.ListBox1.AddItem Sheets(n).Name

    Loop Until n = Worksheets.Count



End Sub

Here please find the modified code for userform related to workbook (this one does not works: run time error 424):

Sub UserForm_Initialize()

    Dim n As Long

    Dim msg As String

    Dim i As Long

    Dim s As String

    Dim Wb As Workbook



    Do

        n = n + 1

        Me.ListBox1.AddItem Workbooks(n).Name

    Loop Until n = Worksbooks.Count



End Sub

May I propose a simple for loop ?

Dim i As Long

For i = 1 To Application.Workbooks.Count

    Debug.Print Application.Workbooks(i).Name

Next

Then, if you have different instances of Excel (different Application objects then the one your Userform came from), this become a little bit more complex. (This is probably not the case if you are working in Excel 2010 or newer). But, if this is the case, it requires a couple of Win32 API calls and some insights on the "windows" of Excel. I've found my answers here in the past : Can VBA Reach Across Instances of Excel?

If there is no reason for the actual number to be parsed in your code, then why not just loop the sheets directly?

Sub UserForm_Initialize()

    Dim ws As Worksheet



    For Each ws In ThisWorkbook.Worksheets

        Me.ListBox1.AddItem ws.Name

    Next ws



End Sub