adjust worksheet_activate code

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
Hi guys,

I have this little snippet of code that populates a combobox for me when I go to a sheet named preproposal... the problem is that if I have saved the workbook at that sheet and I reopen it... the combobox will not populate unless I leave the sheet and come back... I would like to have it so it populates regardless of which sheet the book opens to.


Private Sub Worksheet_Activate()
Dim i As Integer
ComboBox1.Clear
For i = 1 To ActiveSheet.Scenarios.Count
ComboBox1.AddItem ActiveSheet.Scenarios(i).Name
Next
ComboBox1.Text = "Please select a system"
End Sub
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

DeBeuz

Active Member
Joined
Jun 6, 2012
Messages
448
Hi,

I suggest you place all code in a separate sub (in my test without a combo box in a module), like Update_Combo.
In the Worksheet_Activate sub just call this sub.

Create a code module in this workbook and create a sub Auto_open.
It should first select your sheet (Sheet1.Activate) and the call your update sub.

For using a combo box, you might need to create the new sub in the sheet's module and try to call it from the main module (Sheet1.Update_combo ?)

Hope this helps, since we can not trigger the activate sub by just activating the sheet from the Auto_open.

Succes,

Paul
 

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
Hi Paul thanks for that.... in the end I just made a on workbook open go to the main page so that the workbook will never go to that page first regardless of whether it was saved there or not.

Ken
 

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,477
Members
409,702
Latest member
thmoriarty

This Week's Hot Topics

Top