0

Answer at bottom of post

I got the code below to work but a question remains. When I copy and paste the code into my project it won't work. But inserting the outline of the call from the dropdown menu and pasting the rest inside its body works just fine. So new question: What on earth is Excel doing behind my back to make this work?


I'm trying to get a selection of all opened workbooks in a combobox in Excel 2010/2013. There are plenty of solutions online (more or less they are all the same). But none of them seem to work for me. What I've tried:

Private Sub UserForm1_Initialize()
    Dim wkb As Workbook
    For Each wkb In Application.Workbooks
        ComboBox1.AddItem wkb.Name
    Next wkb
End Sub

or

Private Sub Userform1_Initialize()
    Dim wkb As Workbook
    With Me.ComboBox1
        For Each wkb In Application.Workbooks
            AddItem wkb.Name
        Next wkb
    End With
End Sub

File is saved as xlsm, the two elements are are indeed called UserForm1 and ComboBox1. Is there some sort of security option I need to turn off? The UserForm gets called but the list is empty.

Edit 1: Listing the workbooks in a MsgBox works. So there must be something wrong with the add method.

Sub ListOpenBooks()
'lists each book that's OPEN
    Dim wkb As Workbook
    For Each wkb In Application.Workbooks
          MsgBox wkb.Name
    Next wkb
End Sub

Edit 2: Using the same code in a Button on the UserForm works as well. So it must be the Initialize method when the UserForm is called.

Answer

Alright, just a misunderstanding. In the code segment of the UserForm you don't need its name to initialize. A simple Private Sub UserForm_Initialize() is enough. You can disregard its name completely.

1

Alright, just a misunderstanding. In the code segment of the userform you don't need its name to initialize. A simple Private Sub UserForm_Initialize() is enough. You can disregard its name completely.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.