Thank you Basilisk
I can't thank you enough... It worked like a charm :)
Kind Regards
Tanya

"Basilisk96" wrote:

Tanya wrote:
Basilisk you mentioned in an earlier post, that you would be able to simplify
the code so that I could use it with all 10 sheets and commandbuttons? Would
you mind helping me with this please?
Many thanks
Tanya

A few small changes and a loop is all that it takes.
The following code is working for me. I have set up two buttons on
Sheet3, controlled by Sheet1!B2 and Sheet2!C5, respectively. I
believe you have about 10 buttons, so change the constant accordingly,
and add the appropriate button definitions to match. If you have a
statement "Option Base 1" somewhere in your project code, then add 1
to the index of arr() inside the loop.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Const BUTTON_COUNT = 2 'CHANGE TO NUMBER OF YOUR BUTTONS
Const SHEET_WITH_BUTTONS = "Sheet3" 'NAME OF SHEET CONTAINING
BUTTONS

Dim ButtonDefs() As Variant
ReDim ButtonDefs(0 To BUTTON_COUNT - 1)

' START OF BUTTON DEFINITIONS
'Each array is three strings:
'Name of change sheet, change cell address, associated button name
ButtonDefs(0) = Array("Sheet1", "B2", "CommandButton1")
ButtonDefs(1) = Array("Sheet2", "C5", "CommandButton2")
'
'...CONTINUE THESE DEFINITIONS AS NEEDED...
' END OF BUTTON DEFINITIONS

Dim arr As Variant
For Each arr In ButtonDefs
'NOTE: the following assumes Option Base 0 (the default)
swcc = arr(0) 'sheet with change cell
cca = arr(1) 'change cell address
bn = arr(2) 'button name
If StrComp(Sh.Name, swcc, vbTextCompare) = 0 Then
If Target = Me.Worksheets(swcc).Range(cca) Then
Me.Worksheets(SHEET_WITH_BUTTONS).OLEObjects(bn). _
Object.Caption = Target.Text
End If
End If
Next
End Sub

That's about it! Hope it works for you too.

Cheers,
-Basilisk96