Problem with ComboBox when 2 workbooks are open

[Pedro Mendes]

Hi,

So I am here with a small problem.
I have a workbook with a userform and in this userform I have a combobox where we choose from a list what sheet to open (i have several sheets in this workbook), in this i have an exit event that after i choose it goes to the sheet that I want. To populate this list i create a name range and add this in the combobox proprieties (Rowsource). This range changes in accordance of some previous data, and creates all the sheets in the workbook.

Everything works fine if I only have this workbook open.
The problem starts when I have 2 workbooks open. Lets say that I have one workbook named "template.xlsm" this is the one that has the userform and all the things, and that i have another workbook named "Book 2.xlsx".
If I have both workbooks open, but the one that I am seeing in my screen is the "Book 2.xlsx", when I open the user form the combobox is empty, so it is not getting the info from the correct workbook/worksheet.

I tried to use:

Private sub userform_initialize()
Workbooks("template.xlsm").Activate
Sheets("Sheet1").Activate

End Sub


I tried to:

Private sub userform_initialize()
Workbooks("template.xlsm").Activate
Sheets("Sheet1").Activate
ComboBox1.List = Workbooks("template.xlsm").Sheets("Sheet1").Range("A1:A25")

End Sub

I have tried to write something to a cell to see if it make the correct workbook come front ---- Workbooks("template.xlsm").Sheets("Sheet1").Range("C1") = Date ---- and although this works and writes the date on the cell that I have specified, it don't make the window of the workbook "template.xlsm" come in front of the window from the workbook "Book 2"


Hope I managed to make myself clear. And hope that someone has a solution.
Thank you all.

Best regards,
Pedro Mendes


Nothing seams to work and I always have the "Book 2.xlsx" excel file in my screen, and I don't find a way that on opening it changes the the workbook that is in front "Book 2.xlsx" to show the workbook "template.xlsm"

When I

 

[dmt32]

Hi
try

Private Sub UserForm_Initialize()
 Me.ComboBox1.List = ThisWorkbook.Worksheets("Sheet1").Range("A1:A25").Value
End Sub

Dave

 

[Pedro Mendes]

Hi dmt32,​

Thank you i complete forgot to put the Me. before combobox1.list


Thank you and wish a wonderful week ahead
Regards,
Pedro Mendes