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
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
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.
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.