Userform to navigate and select another open workbook

Question

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

You have a typo in the closing loop statement, should be as follows: Loop Until n = Workbooks.Count ( instead of "Worksbooks" ). — T.M.

CharlesPL CharlesPL · Accepted Answer · 2018-10-04T14:14:21

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?

Userform to navigate and select another open workbook

2 Answers