Hi,

I'm really new to VBA programming, but I'm trying to learn from
everyone here. :)

I have an ActiveX ComboBox that is populated with the names of all the
worksheets. All I want to do is refresh the list of worksheet names in
the combobox whenever a worksheet is added or deleted from the
workbook.

Any ideas?

Thanks,
Brian


---
Message posted from http://www.ExcelForum.com/

Brian,

Paste this code in the ThisWorkbook code module in the VB editor. It
assumes a "ComboBox1" in "Sheet1." The code runs each time a new sheet is
activated, which happens whenever you insert or delete a sheet:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim ws As Worksheet

With Worksheets("Sheet1").ComboBox1
.Clear
For Each ws In ThisWorkbook.Worksheets
.AddItem ws.Name
Next ws
End With

End Sub

hth,

Doug

"Duke17 " wrote in message
...
Hi,

I'm really new to VBA programming, but I'm trying to learn from
everyone here. :)

I have an ActiveX ComboBox that is populated with the names of all the
worksheets. All I want to do is refresh the list of worksheet names in
the combobox whenever a worksheet is added or deleted from the
workbook.

Any ideas?

Thanks,
Brian


---
Message posted from http://www.ExcelForum.com/

Hey Doug thanks a lot for your reply. I had to make a slight change t
get it to work though...I changed:

With Worksheets("Sheet2").ComboBox1

TO

With Sheet2.ComboBox1 (I take it they're the same?)


I noticed that this code will take effect even if the user simpl
clicks on another worksheet. I only need it to update if there's bee
a new worksheet added, or a current one deleted. Do you or anyone els
have any ideas

--
Message posted from http://www.ExcelForum.com

"Duke17 " wrote in message
...
With Sheet2.ComboBox1 (I take it they're the same?)

Actually they're not. WorkSheets("Sheet2") or ("mysheet"), etc., refer to
the names in the sheet tabs. Sheet2, without the quotes, is the codename,
which appears in the VBE Project Explorer under Microsoft Excel Objects, for
example -- Sheet2("mysheet").

I noticed that this code will take effect even if the user simply
clicks on another worksheet. I only need it to update if there's been
a new worksheet added, or a current one deleted. Do you or anyone else
have any ideas?

I don't have any other ideas. What is the function of the ComboBox? I
understand that it runs every time you switch sheets. What problem is that
causing?

Doug

Message posted from http://www.ExcelForum.com/

Well, data in the spreadsheet is loaded according to the name in th
combobox...so when I click on another spreadsheet, it refreshes th
combobox, and sets it back to the first spreadsheet in the list.

If it would only refresh when a new spreadsheet is inserted that woul
be cool. Or after refreshing, would it be possible to have it se
itself back to the previously selected name in the combobox, instead o
resetting it to the first name in the list

--
Message posted from http://www.ExcelForum.com

Ok...I've accomplished getting the combobox1 to reset to the previou
selected value. No problem there. But as expected, it's a little slo
because everytime you click on another sheet now, it has to reload th
data.

How would I allow this code to execute ONLY when a new sheet is adde
or deleted? I think with this change, everything will be perfect

--
Message posted from http://www.ExcelForum.com