Command Button Naming

[Tanya]

Thank you Basilisk for your Patience with me.

I have been doing some research and finally have the macro working, however
it is at a cost...

This is the successful macro:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const SHEET_WITH_BUTTON = "Setup"
Const SHEET_WITH_CHANGE_CELL = "1"
Const CHANGE_CELL_ADDRESS = "$B$1"
Const BUTTON_NAME = "CommandButton1"
If StrComp(Sh.Name, SHEET_WITH_CHANGE_CELL, vbTextCompare) = 0 Then
If Target.Address = CHANGE_CELL_ADDRESS Then
Me.Worksheets(SHEET_WITH_BUTTON).OLEObjects(BUTTON_NAME). _
Object.Caption = Target.Text
End If
End If

End Sub

The problem lay in the fact that I had another macro:
Private Sub Protect_Workbook_Click()
'Protect workbook
Dim ws As Worksheet
Const PWORD As String = "BBHS"
For Each ws In ActiveWorkbook.Worksheets
With ws
If ws.ProtectContents = False Then
.EnableSelection = xlUnlockedCells
.Protect Password:=PWORD
End If
End With
Next ws
ActiveWorkbook.Protect Password:=PWORD

End Sub

At least... I think the problem was that the workbook was protected?

I am still working on this...

I have spent heaps of time on this, but it hasn't been wasted, I've cleaned
up the workbook modules

I will keep you posted, if you have any suggestions, I would greatly
appreciate them.

Cheers
Tanya

 

[Tanya]

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

 

[Basilisk96]

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

 

[Tanya]

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