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/