I think I'd use an extra worksheet (hidden??) that contained 4 columns.
the worksheet name that held the object
the object's name
the object's English description
the object's French description
Then maybe use a worksheet change event that looked for a change to L5--and ran
a macro that looped through that range changing the .captions of the objects.
I used this to create that log worksheet (in a general module):
Option Explicit
Sub RunOnce()
Dim OLEObj As OLEObject
Dim wks As Worksheet
Dim LogWks As Worksheet
Dim oRow As Long
Application.DisplayAlerts = False
On Error Resume Next
Worksheets("Logwks").Delete
On Error GoTo 0
Application.DisplayAlerts = True
Set LogWks = Worksheets.Add
With LogWks
.Name = "LogWks"
.Range("a1").Resize(1, 4).Value _
= Array("sheet name", "Obj Name", "English", "French")
oRow = 1
For Each wks In ActiveWorkbook.Worksheets
For Each OLEObj In wks.OLEObjects
oRow = oRow + 1
.Cells(oRow, 1).Value = "'" & wks.Name
.Cells(oRow, 2).Value = "'" & OLEObj.Name
On Error Resume Next 'if no .caption
.Cells(oRow, 3).Value = "'" & OLEObj.Object.Caption
On Error GoTo 0
Next OLEObj
Next wks
End With
End Sub
I assumed that the english captions were existing. Then I added some, er,
French captions to that list (in column D. Be careful--If you run this more
than once, it'll delete the older version and create a new version. And you'll
be typing in the other (French?) descriptions again!
Then behind the worksheet that held that F5, I put this code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColToUse As Long
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("L5")) Is Nothing Then Exit Sub
If LCase(Target.Value) = "f" Then
ColToUse = 4
Else
ColToUse = 3
End If
Call ChangeCaptions(ColToUse)
End Sub
And finally in the general module, I put this:
Sub ChangeCaptions(WhichCol As Long)
Dim myCell As Range
With Worksheets("LogWks")
For Each myCell In .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
On Error Resume Next
Worksheets(myCell.Value).OLEObjects(myCell.Offset(0, 1).Value) _
.Object.Caption _
= myCell.Offset(0, WhichCol - 1).Value
On Error GoTo 0
Next myCell
End With
End Sub
the on error stuff was to ignore errors caused by the objects that don't have a
..caption property.
