Refresh combobox after inserting new sheet
 

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/

Refresh combobox after inserting new sheet
 

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/

Refresh combobox after inserting new sheet
 

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

Refresh combobox after inserting new sheet
 

"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/

Refresh combobox after inserting new sheet
 

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

Refresh combobox after inserting new sheet
 

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

Refresh combobox after inserting new sheet
 

Put all of the following in the ThisWorkbook module. It uses the NewSheet
event, obvious enough. It also takes advantage of the fact that the
SheetDeactivate event fires before sheet deletion and the SheetActivate
fires after sheet deletion. It uses a public variable to check if there's a
different number of sheets during the Deactivate and Activate events. If
there is then, and only then, does it call the combobox refresh sub. It
also keeps the combobox value between refreshes. Also, it runs the refresh
when the workbook is first opened, so get rid of that if you don't want.

The above statements about the order of Deactivate, delete, and Activate are
just based on my experimentation, but I think it's true (XL 2k). Let me
know how it works for you:

Option Explicit
Dim sheets_count As Long

Private Sub Workbook_Open()

Call refresh_cbox

End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)

Call refresh_cbox

End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If sheets_count ThisWorkbook.Sheets.Count Then
Call refresh_cbox
End If

End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

sheets_count = ThisWorkbook.Sheets.Count

End Sub
Sub refresh_cbox()

Dim ws As Worksheet
Dim cbox_idx As Long

With Worksheets("Sheet1").ComboBox1
'track cbox value before refresh
cbox_idx = .ListIndex
.Clear
For Each ws In ThisWorkbook.Worksheets
.AddItem ws.Name
Next ws
'if new sheet inserted before old cbox value
If ActiveSheet.Index <= cbox_idx + 1 Then
cbox_idx = cbox_idx + 1
End If
'ensure valid listindex on workbook open and delete
If cbox_idx + 1 <= .ListCount And cbox_idx = 0 Then
.ListIndex = cbox_idx
Else
.ListIndex = 0
End If
'reset the value
.Value = .List(.ListIndex)
End With

End Sub

hth,

Doug Glancy

"Duke17 " wrote in message
...
Ok...I've accomplished getting the combobox1 to reset to the previous
selected value. No problem there. But as expected, it's a little slow
because everytime you click on another sheet now, it has to reload the
data.

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


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

Refresh combobox after inserting new sheet
 

Well, I haven't been able to get it to work right away.

1. Whenever refresh_cbox is called I get an error stating that th
subscript is out of range. I changed Sheet1 to Sheet2 so it looks lik
this:

With Worksheets("Sheet2").ComboBox1

2. Workbook_NewSheet is called when a new sheet is inserted. That i
correct. It was my fault for not mentioning this, but most of the tim
a new sheet will be inserted as a copy of an existing worksheet. :eek
Whenever this happens, Workbook_NewSheet is not called. Is ther
another event handler for this that I should use?

Thanks for all of your help and patience Doug!

Bria

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

Refresh combobox after inserting new sheet
 

Brian,

1. Whenever refresh_cbox is called I get an error stating that the
subscript is out of range. I changed Sheet1 to Sheet2 so it looks like
this:

With Worksheets("Sheet2").ComboBox1

Provided the Worksheet is named "Sheet2" exactly, I don't know what the
problem could be.

2. Workbook_NewSheet is called when a new sheet is inserted. That is
correct. It was my fault for not mentioning this, but most of the time
a new sheet will be inserted as a copy of an existing worksheet. :eek:
Whenever this happens, Workbook_NewSheet is not called. Is there
another event handler for this that I should use?

Sorry, but I'm not able to figure out anything else. I still like the
original code - it was simple at least. But if it's too slow, it's too
slow. If I think of anything, I'll let you know. You could try posting
again with what you've got.

Doug Glancy

Refresh combobox after inserting new sheet
 

Workbook_NewSheet() is something recognized as part of VBA? Or is i
completely custom?

I need a function that triggers when a worksheet has been copied. I'
just like to know if something like this already exists, or if I nee
to create this myself

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

Refresh combobox after inserting new sheet
 

Brian,

It is a VBA-defined event. In the ThisWorkbook module, where you've been
pasting the code I gave you, choose "Workbook" from the top-left dropdown in
the code window. Then choose the top-right dropdown to see all the VBA
workbook events. Unfortunately, there is no CopySheet event. For more info
try "Workbook Events" in VBA help.

hth,

Doug

"Duke17 " wrote in message
...
Workbook_NewSheet() is something recognized as part of VBA? Or is it
completely custom?

I need a function that triggers when a worksheet has been copied. I'd
just like to know if something like this already exists, or if I need
to create this myself?


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

Refresh combobox after inserting new sheet
 

Hey Doug,

I found a solution that works! It turned out to be very easy. Instea
of trying to complicate things by detecting when a new sheet was adde
and how, I just decided to have the combobox refresh every time tha
sheet with the combobox (call it sheet2) was opened.

I figured if a sheet gets copied or inserted, the selected sheet wil
change away from sheet2. When they activate sheet2, the combobox wil
then have the new sheet included.

Works like a charm. Thanks for all the help Doug

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