Forum Discussion
Solved
UserForm with a ComboBox that populates with the name of the open workbooks
Hi,
So I'm looking for some help on creating a UserForm with a ComboBox that populates with the name of the open workbooks and WorkSheets, displays the choices and allows you to select one.
I found this code, which I believe will do what I want but where do I put it? Do I put it in a general module or do I put it in a Combobox userform?
Sub ViewListOfOpenWorkBooksAndWorkSheets() Dim book As Workbook, sheet As Worksheet For Each book In Workbooks For Each sheet In book.Worksheets UserForm1.ComboBox1.AddItem book.Name + "!" + sheet.Name Next sheet Next book UserForm1.Show End Sub
Charlotte
That code is a macro, to be copied into a standard module (the kind you create by selecting Insert > Module in the Visual Basic Editor).
Running the macro will populate the combo box, then show the userform.
As an alternative, you could create a UserForm_Initialize event procedure in the userform's module:
Private Sub UserForm_Initialize() Dim book As Workbook, sheet As Worksheet For Each book In Workbooks For Each sheet In book.Worksheets Me.ComboBox1.AddItem book.Name & "!" & sheet.Name Next sheet Next book End Sub
That code is a macro, to be copied into a standard module (the kind you create by selecting Insert > Module in the Visual Basic Editor).
Running the macro will populate the combo box, then show the userform.
As an alternative, you could create a UserForm_Initialize event procedure in the userform's module:
Private Sub UserForm_Initialize() Dim book As Workbook, sheet As Worksheet For Each book In Workbooks For Each sheet In book.Worksheets Me.ComboBox1.AddItem book.Name & "!" & sheet.Name Next sheet Next book End SubIf I want to add a cancel process if commandbutton2 (Cancel button) or the x is pressed, do I also do it in the module? At the minute, if I don't select a workbook, it tries to continue running the next part of the sub which is to save the one that been selected and comes up with a debug error?
Could you post the relevant code?
Or could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
