Problem with ComboBox when 2 workbooks are open

Pedro Mendes

New Member
Joined
Jun 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,835
Office Version
  1. 2019
Platform
  1. Windows
Hi
try

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

Dave
 
Solution

Pedro Mendes

New Member
Joined
Jun 8, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows

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
 

Forum statistics

Threads
1,143,907
Messages
5,721,440
Members
422,362
Latest member
elliotpat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top