lancer procedure suite a un changement de nom de feuille

Bonjour.

Ci joint un message récupéré sur le forum anglophone.

Cette méthode marche très bien.
A toi de l'adapter pour mettre a jour ton ComboBox.

Alain CROS.

---------------------------------------------------
As there is no direct event for rename of sheet I have
wriiten one add-ins for this. This works for me in xl-2000.
I have use withevents in thisworkbook.

With below codes in Add-ins I am able to Trape/fire up
worksheet rename event for any worksheets & from any
workbooks.

Create a New WorkBook with one worksheet. ( As there is no
need for other worksheets)
Goto VB Editor
Now Put this code into thisworkbook code module.


Option Explicit
Dim WithEvents myxl As Excel.Application
Dim actshtname, wbname
Private Sub myxl_SheetActivate(ByVal Sh As Object)
On Error Resume Next
actshtname = Sh.Name
wbname = ActiveWorkbook.Name
With ThisWorkbook.Sheets(1)
.Range("a2") = actshtname'may change to any cell.
.Range("a3").Formula = "='[" & wbname & "]" & actshtname
& "'!a1"
End With
End Sub

Private Sub myxl_WorkbookActivate(ByVal Wb As
Excel.Workbook)
On Error Resume Next
actshtname = Wb.ActiveSheet.Name
wbname = Wb.Name
With ThisWorkbook.Sheets(1)
.Range("a2") = actshtname
.Range("a3").Formula = "='[" & wbname & "]" & actshtname
& "'!a1"
End With
End Sub

Private Sub Workbook_Open()
On Error Resume Next
actshtname = ActiveSheet.Name
wbname = ActiveWorkbook.Name
With ThisWorkbook.Sheets(1)
.Range("a2") = actshtname
.Range("a3").Formula = "='[" & wbname & "]" & actshtname
& "'!a1"
End With
setxl
End Sub

Sub setxl()
Set myxl = Excel.Application
End Sub

Put this code to sheet1's code module.

Private Sub Worksheet_Calculate()
Dim shtrename, oshtname
shtrename = ActiveSheet.Name
oshtname = ThisWorkbook.Sheets(1).Range("a2")
If shtrename <> oshtname Then
MsgBox "This Sheet rename from " & oshtname & " to " &
shtrename, , "Worksheet Rename"
End If
End Sub


Save this workbook as an ADD-in.
File>Save As>Save as Type Select Microsoft Excel Add-in (Last item)

Or

Set Addins=True in Property Window for Thisworkbook in VBE
& save in VBE.

Close Excel/this workbook & Load that Add-in.

Now Change any worksheet name in any workbook to fire up
rename event.


You may need to write code in custombutton-toolbar for on
& off to fire up event.

Regards,
Shah Shailesh
-----------------------------------------------------


"Albator V" <michiels_t@farman.fr> a écrit dans le message de news: 1d9db01c45449$66ab3aa0$a101280a@phx.gbl...

Bonjour,

Dans une barre d'outils personnalises, jai place une
combobox avec la liste des feuilles.
j'ai trouve un fichier exemple complet sur un site
(http://cgi.ethz.ch/~ubreu/cgi-bin/VBA/toolbar.php). la
combobox se met a jour quand on ajoute ou supprime une
feuille, a l'ouverture d'un nouveau classeur...

par contre lorsque l'on renome une feuille, il faut
active une nouvelle feuille pour que la liste se mette
jour.

y'a til une procedure qui permet de lancer une macro
suite a un changement de nom de feuille ??

merci d'avance