Command Button Naming
 

Hi
Is it possible to have the description/label of a command button updated by
the title in a sheet cell?
cheers
Tanya

Command Button Naming
 

Unless you are referring to a Form Control, a control button has a property
called Caption, that is the name that appears on the button, not its's name
which is used by Excel and VBA code to identifiy it.

SO to change the caption, use

CommandButton1.Caption = "My Button"

To link this to a cell, you could use

CommandButton1.Caption = Range("A1")

To run the above manually store it in the code sheet for the worksheet where
the control is placed.

If you want it to automatically change it when the cell is changed, use the
change event, something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Range("A1").Value
End If
End Sub


--

Regards,
Nigel




"Tanya" wrote in message
...
Hi
Is it possible to have the description/label of a command button updated
by
the title in a sheet cell?
cheers
Tanya

Command Button Naming
 

On Nov 27, 1:21 am, Tanya wrote:
Hi
Is it possible to have the description/label of a command button updated by
the title in a sheet cell?
cheers
Tanya

Of course.
Let's say your title cell is A1, and your command button is named in
code as CommandButton1. In the source code of the sheet object, enter
this code in the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Target.Value
End If
End Sub

Hopefully, it's as simple as that, but if not - post back more
questions.

Cheers,
-Basilisk96

Command Button Naming
 

Hi

I did what you suggested but must be missing something.

Private Sub Worksheet_Change(ByVal Target As Range)

'Target Address is on a separate sheet, I don't think I have done this
correctly

If Target.Address = "Sheet1!B2" Then
CommandButton1.Caption = Target.Value
End If
End Sub

I guess it sounds like a weird thing to want to do, but on my startup sheet
I have a number of command buttons each linked to a separate sheet [classe
lists]

Thanking you in advance.

Regards
Tanya



"Basilisk96" wrote:

On Nov 27, 1:21 am, Tanya wrote:
Hi
Is it possible to have the description/label of a command button updated by
the title in a sheet cell?
cheers
Tanya

Of course.
Let's say your title cell is A1, and your command button is named in
code as CommandButton1. In the source code of the sheet object, enter
this code in the Worksheet_Change event:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Target.Value
End If
End Sub

Hopefully, it's as simple as that, but if not - post back more
questions.

Cheers,
-Basilisk96

Command Button Naming
 

Hi Nigel
I am trying to change the display text on the Commandbutton1 to read TEXT in
cell reference sheet1!B2

Is this possible?

I have several command buttons on this one sheet, each taking the user to a
designated sheet, namely class 1, class 2 etc. The idea is a teacher can see
at a glance which class is which.

You may wonder why I haven't simply changed the sheet tap names, and I tried
this originally, however, on a couple of the sheets I wanted to look up
values in other sheets and created a vlookup. This required labelling each
sheet and wouldn't allow me to then change the tabs, hence command button
approach. Also, I thought this might be more user friendly as there are 10
classes in each workbook.

Regards
Tanya

"Nigel" wrote:

Unless you are referring to a Form Control, a control button has a property
called Caption, that is the name that appears on the button, not its's name
which is used by Excel and VBA code to identifiy it.

SO to change the caption, use

CommandButton1.Caption = "My Button"

To link this to a cell, you could use

CommandButton1.Caption = Range("A1")

To run the above manually store it in the code sheet for the worksheet where
the control is placed.

If you want it to automatically change it when the cell is changed, use the
change event, something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Range("A1").Value
End If
End Sub


--

Regards,
Nigel




"Tanya" wrote in message
...
Hi
Is it possible to have the description/label of a command button updated
by
the title in a sheet cell?
cheers
Tanya

Command Button Naming
 

[12:48AM!?]
The
Private Sub Worksheet_Change(ByVal Target As Range)
goes in Sheet1; and you need
Sheets("sheetX").CommandButton1.Caption = Target.Value
D-C Dave


Tanya wrote:
I did what you suggested but must be missing something.
Private Sub Worksheet_Change(ByVal Target As Range)
'Target Address is on a separate sheet, I don't think I have done this
correctly
If Target.Address = "Sheet1!B2" Then
CommandButton1.Caption = Target.Value
End If
End Sub
I guess it sounds like a weird thing to want to do, but on my startup sheet
I have a number of command buttons each linked to a separate sheet [classe
lists]

"Basilisk96" wrote:
Of course.
Let's say your title cell is A1, and your command button is named in
code as CommandButton1. In the source code of the sheet object, enter
this code in the Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Target.Value
End If
End Sub

On Nov 27, 1:21 am, Tanya wrote:
Hi
Is it possible to have the description/label of a command button updated by
the title in a sheet cell?
cheers
Tanya

Command Button Naming
 

Hi Dave
I'm still missing something, when I try to run this macro it brings up a
dialogue box which wants choose a macro to run.

Sheet 33 has the commandButton1 which has the following code:

Private Sub CommandButton1_Click()
'Unprotect workbook
UnProtect_Workbook
'Show Task Weights
Sheets("1").Visible = True
Sheets("1").Select
'Protect workbook
Protect_Workbook
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("sheet1").CommandButton1.Caption = Target.Value
If Target.Address = "B2" Then
CommandButton1.Caption = Target.Value
End If
End Sub

Thanks
Tanya

"Dave D-C" wrote:

[12:48AM!?]
The
Private Sub Worksheet_Change(ByVal Target As Range)
goes in Sheet1; and you need
Sheets("sheetX").CommandButton1.Caption = Target.Value
D-C Dave


Tanya wrote:
I did what you suggested but must be missing something.
Private Sub Worksheet_Change(ByVal Target As Range)
'Target Address is on a separate sheet, I don't think I have done this
correctly
If Target.Address = "Sheet1!B2" Then
CommandButton1.Caption = Target.Value
End If
End Sub
I guess it sounds like a weird thing to want to do, but on my startup sheet
I have a number of command buttons each linked to a separate sheet [classe
lists]

"Basilisk96" wrote:
Of course.
Let's say your title cell is A1, and your command button is named in
code as CommandButton1. In the source code of the sheet object, enter
this code in the Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Target.Value
End If
End Sub

On Nov 27, 1:21 am, Tanya wrote:
Hi
Is it possible to have the description/label of a command button updated by
the title in a sheet cell?
cheers
Tanya

Command Button Naming
 

Hi Tanya,

In understand the procedure, Private Sub CommandButton1_Click()
is on Sheet 33.

Where is the procedure, Private Sub Worksheet_Change(ByVal Target As
Range) located?

I'm trying to reproduce your problem, but I'm unable to.

Are either of these the macro that returns the dialog box?

Dan D.



On Nov 27, 12:32 pm, Tanya wrote:
Hi Dave
I'm still missing something, when I try to run this macro it brings up a
dialogue box which wants choose a macro to run.

Sheet 33 has the commandButton1 which has the following code:

Private Sub CommandButton1_Click()
'Unprotect workbook
UnProtect_Workbook
'Show Task Weights
Sheets("1").Visible = True
Sheets("1").Select
'Protect workbook
Protect_Workbook
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("sheet1").CommandButton1.Caption = Target.Value
If Target.Address = "B2" Then
CommandButton1.Caption = Target.Value
End If
End Sub

Thanks
Tanya

"Dave D-C" wrote:
[12:48AM!?]
The
Private Sub Worksheet_Change(ByVal Target As Range)
goes in Sheet1; and you need
Sheets("sheetX").CommandButton1.Caption = Target.Value
D-C Dave

Tanya wrote:
I did what you suggested but must be missing something.
Private Sub Worksheet_Change(ByVal Target As Range)
'Target Address is on a separate sheet, I don't think I have done this
correctly
If Target.Address = "Sheet1!B2" Then
CommandButton1.Caption = Target.Value
End If
End Sub
I guess it sounds like a weird thing to want to do, but on my staHrtup sheet
I have a number of command buttons each linked to a separate sheet [classe
lists]

"Basilisk96" wrote:
Of course.
Let's say your title cell is A1, and your command button is named in
code as CommandButton1. In the source code of the sheet object, enter
this code in the Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Target.Value
End If
End Sub

On Nov 27, 1:21 am, Tanya wrote:
Hi
Is it possible to have the description/label of a command button updated by
the title in a sheet cell?
cheers
Tanya

Command Button Naming
 

I'm waiting for Basilisk96 to clear this up.
In sheet 1 you should have
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Sheets("Sheet33").CommandButton1.Caption = Target.Value
End If
End Sub
Then when you enter something into sheet1 A1,
that should go into the ButtonCaption on Sheet33.
The only way I know of to get a dialog box is if you
are running the macro from the "tools" menu.
Instead, just enter something into sheet1 A1.

dan dungan wrote:
I'm trying to reproduce your problem, but I'm unable to.

Tanya wrote:
I'm still missing something, when I try to run this macro it brings up a
dialogue box which wants choose a macro to run.

"Basilisk96" wrote:
Of course.
Let's say your title cell is A1, and your command button is named in
code as CommandButton1. In the source code of the sheet object, enter
this code in the Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Target.Value
End If
End Sub

Command Button Naming
 

Tanya,

Paste the following code in the ThisWorkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const SHEET_WITH_BUTTON = "Sheet1" '<<< CHANGE.
Const SHEET_WITH_CHANGE_CELL = "Sheet2" '<<< CHANGE
Const CHANGE_CELL_ADDRESS = "$A$1" '<<< CHANGE
Const BUTTON_NAME = "Button1" '<<< CHANGE
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

Change the value of SHEET_WITH_BUTTON to the name of the worksheet on which
the command button resides.

Change the value of SHEET_WITH_CHANGE_CELL to the name of the worksheet
containing the cell whose text you want to assign to the button.

Change CHANGE_CELL_ADDRESS to the address of the cell that triggers the
change of the button's caption. The '$' characters are required as shown.

Change BUTTON_NAME to the name of the button whose text is to change.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Tanya" wrote in message
...
Hi
Is it possible to have the description/label of a command button updated
by
the title in a sheet cell?
cheers
Tanya

Command Button Naming
 

Thank you Dave,
When I realised I needed to change the content of the cell B2 on sheet 1,
all made sense and worked a dream.
Thank you very very much.

Regards
Tanya

"Dave D-C" wrote:

I'm waiting for Basilisk96 to clear this up.
In sheet 1 you should have
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Sheets("Sheet33").CommandButton1.Caption = Target.Value
End If
End Sub
Then when you enter something into sheet1 A1,
that should go into the ButtonCaption on Sheet33.
The only way I know of to get a dialog box is if you
are running the macro from the "tools" menu.
Instead, just enter something into sheet1 A1.

dan dungan wrote:
I'm trying to reproduce your problem, but I'm unable to.

Tanya wrote:
I'm still missing something, when I try to run this macro it brings up a
dialogue box which wants choose a macro to run.

"Basilisk96" wrote:
Of course.
Let's say your title cell is A1, and your command button is named in
code as CommandButton1. In the source code of the sheet object, enter
this code in the Worksheet_Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
CommandButton1.Caption = Target.Value
End If
End Sub

Command Button Naming
 

Hi Chip

An earlier post appeared to work, however I found that when I edited other
cells in the worksheet the commandbutton would be updated, no idea how when I
had specified the target cell

Anyway, I tried what you suggested:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const SHEET_WITH_BUTTON = "Setup" <<NAME OF SHEET CONTAINING BUTTON
Const SHEET_WITH_CHANGE_CELL = "Sheet1" '<<< NAME OF SHEET WITH CELL
Const CHANGE_CELL_ADDRESS = "$B$1" '<<< CELL REFERENCE
Const BUTTON_NAME = "CommandButton1" '<<< COMMANDBUTTON NAME
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


I must be still missing something because nothing happens when I change cell
value on sheet1!B1

Thanking you in advance for your support.

Regards
Tanya
"Chip Pearson" wrote:

Tanya,

Paste the following code in the ThisWorkbook module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const SHEET_WITH_BUTTON = "Sheet1" '<<< CHANGE.
Const SHEET_WITH_CHANGE_CELL = "Sheet2" '<<< CHANGE
Const CHANGE_CELL_ADDRESS = "$A$1" '<<< CHANGE
Const BUTTON_NAME = "Button1" '<<< CHANGE
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

Change the value of SHEET_WITH_BUTTON to the name of the worksheet on which
the command button resides.

Change the value of SHEET_WITH_CHANGE_CELL to the name of the worksheet
containing the cell whose text you want to assign to the button.

Change CHANGE_CELL_ADDRESS to the address of the cell that triggers the
change of the button's caption. The '$' characters are required as shown.

Change BUTTON_NAME to the name of the button whose text is to change.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Tanya" wrote in message
...
Hi
Is it possible to have the description/label of a command button updated
by
the title in a sheet cell?
cheers
Tanya

Command Button Naming
 

I apologize for the silence, I was away for a couple days...

I tried Chip's suggestion, and it worked the first time. I think I
see why it didn't work for you, though. In an earlier post, you showed
the button's Click code, where you call out the sheet by its tab name
"1":
'Show Task Weights
Sheets("1").Visible = True
Sheets("1").Select

whereas in the code you tried, you're calling it:
Const SHEET_WITH_CHANGE_CELL = "Sheet1" '<<< NAME OF SHEET WITH CELL

Am I correct to assume that the sheet tab of your Sheet1 object is
named "1"?
If so, then trying to index it by "Sheet1" will raise a "Index out of
bounds" error because that tab doesn't exist.

I like Chip's suggestion, because it paves the way for a systematic
approach to your problem: with a loop construct and a little more
code, you can update all the ten class buttons under one procedure
call.

I'd like to correct myself on my first suggestion, though. I think
it's much more robust not to use the address property for ID directly
in the way I showed it originally. A better way could be to use the
Range property:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then
Sheets("Sheet33").CommandButton1.Caption = Target.Value
End If
End Sub

This eliminates the hassle of "$B$2" vs. "B2" comparison failures.
The Target.Address property always returns an absolute address with
the $ signs, whereas Range can take a wider variety of inputs.


An earlier post appeared to work, however I found that when I edited other
cells in the worksheet the commandbutton would be updated, no idea how when I
had specified the target cell

This doesn't make sense. How do you know that the button's caption
got updated with the target cell's text, unless you changed the target
cell's contents (which would trigger the caption change as expected) ?

Cheers,
-Basilisk96

Command Button Naming
 

Hi Basilisk


"Basilisk96" wrote:

Am I correct to assume that the sheet tab of your Sheet1 object is
named "1"?
YES you are correct <<<<<<

If so, then trying to index it by "Sheet1" will raise a "Index out of
bounds" error because that tab doesn't exist.

I haven't had this error? <<<<<<<<

I like Chip's suggestion, because it paves the way for a systematic
approach to your problem: with a loop construct and a little more
code, you can update all the ten class buttons under one procedure
call.


Chips code didn't appear to work. I wondered if I had placed the code under
the incorrect sheet?

I'd like to correct myself on my first suggestion, though. I think
it's much more robust not to use the address property for ID directly
in the way I showed it originally. A better way could be to use the
Range property:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then
Sheets("Sheet33").CommandButton1.Caption = Target.Value
End If
End Sub

I tried this and again not working for me. Note I placed the code against
sheet1 labeled '1' <<<<<<<<<<


This eliminates the hassle of "$B$2" vs. "B2" comparison failures.
The Target.Address property always returns an absolute address with
the $ signs, whereas Range can take a wider variety of inputs.


An earlier post appeared to work, however I found that when I edited other
cells in the worksheet the commandbutton would be updated, no idea how when I
had specified the target cell

This doesn't make sense. How do you know that the button's caption
got updated with the target cell's text, unless you changed the target
cell's contents (which would trigger the caption change as expected) ?

All I know is that when I worked in the rest of the workbook, i.e. insert
column the macro ran and changed the commandbutton1 caption, relevant to the
contents in a cell other than B2?? I have no idea why this was happening
<<<<<<<<<<

Thank you for your time considering this problem!
Regards
Tanya


Cheers,
-Basilisk96

Command Button Naming
 

Hi Tanya,

If so, then trying to index it by "Sheet1" will raise a "Index out of
bounds" error because that tab doesn't exist.

I haven't had this error? <<<<<<<<

Is any error handler active during the call, and is not set to catch
the error?

Chips code didn't appear to work. I wondered if I had placed the code under
the incorrect sheet?

I tried it, and it worked the first time. It must reside in the
"ThisWorkbook" module, which is typically listed in the project tree
under "Miscrosoft Excel Objects" section, after all the worksheets.
If using Chip's version, it's best to disable (comment out) the
previous code in Sheet "1", and vice versa.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then
Sheets("Sheet33").CommandButton1.Caption = Target.Value
End If
End Sub

I tried this and again not working for me. Note I placed the code against
sheet1 labeled '1' <<<<<<<<<<

There must be more to this problem than what I perceive so far,
because that code works for me. But then again, I am testing it it a
fresh workbook without any other code in it.

<..snip..
This doesn't make sense. How do you know that the button's caption
got updated with the target cell's text, unless you changed the target
cell's contents (which would trigger the caption change as expected) ?

All I know is that when I worked in the rest of the workbook, i.e. insert
column the macro ran and changed the commandbutton1 caption, relevant to the
contents in a cell other than B2?? I have no idea why this was happening

Did you insert the said column before column B in sheet "1"?

The recurring problems are rather strange...
BTW, what version of Excel and OS are you running?

Cheers,
-Basilisk96

Command Button Naming
 

I just had another thought...

Is the change cell (B2 on sheet "1") using a formula to pick up the
text, or is a text value entered in it directly?

Cheers,
-Basilisk96

Command Button Naming
 

Hi Basilisk
I am running Excel 2003 and my OS is WinXP SP2
From your comments it appears the problem with the wrong update of text on
CommandButton may be related to the fact that I had the macro running under
the relevant sheet.
Since placing the code Chip wrote in the correct place under This worksheet
module.
I will try it in a new workbook also and see how I go.
Thanks
Tanya

"Basilisk96" wrote:

Hi Tanya,

If so, then trying to index it by "Sheet1" will raise a "Index out of
bounds" error because that tab doesn't exist.

I haven't had this error? <<<<<<<<

Is any error handler active during the call, and is not set to catch
the error?

Chips code didn't appear to work. I wondered if I had placed the code under
the incorrect sheet?

I tried it, and it worked the first time. It must reside in the
"ThisWorkbook" module, which is typically listed in the project tree
under "Miscrosoft Excel Objects" section, after all the worksheets.
If using Chip's version, it's best to disable (comment out) the
previous code in Sheet "1", and vice versa.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then
Sheets("Sheet33").CommandButton1.Caption = Target.Value
End If
End Sub

I tried this and again not working for me. Note I placed the code against
sheet1 labeled '1' <<<<<<<<<<

There must be more to this problem than what I perceive so far,
because that code works for me. But then again, I am testing it it a
fresh workbook without any other code in it.

<..snip..
This doesn't make sense. How do you know that the button's caption
got updated with the target cell's text, unless you changed the target
cell's contents (which would trigger the caption change as expected) ?

All I know is that when I worked in the rest of the workbook, i.e. insert
column the macro ran and changed the commandbutton1 caption, relevant to the
contents in a cell other than B2?? I have no idea why this was happening

Did you insert the said column before column B in sheet "1"?

The recurring problems are rather strange...
BTW, what version of Excel and OS are you running?

Cheers,
-Basilisk96

Command Button Naming
 

Hi again Basilisk
I can get the code to work in a new workbook, just not my own?
Cheers
Tanya

"Basilisk96" wrote:

Hi Tanya,

If so, then trying to index it by "Sheet1" will raise a "Index out of
bounds" error because that tab doesn't exist.

I haven't had this error? <<<<<<<<

Is any error handler active during the call, and is not set to catch
the error?

Chips code didn't appear to work. I wondered if I had placed the code under
the incorrect sheet?

I tried it, and it worked the first time. It must reside in the
"ThisWorkbook" module, which is typically listed in the project tree
under "Miscrosoft Excel Objects" section, after all the worksheets.
If using Chip's version, it's best to disable (comment out) the
previous code in Sheet "1", and vice versa.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then
Sheets("Sheet33").CommandButton1.Caption = Target.Value
End If
End Sub

I tried this and again not working for me. Note I placed the code against
sheet1 labeled '1' <<<<<<<<<<

There must be more to this problem than what I perceive so far,
because that code works for me. But then again, I am testing it it a
fresh workbook without any other code in it.

<..snip..
This doesn't make sense. How do you know that the button's caption
got updated with the target cell's text, unless you changed the target
cell's contents (which would trigger the caption change as expected) ?

All I know is that when I worked in the rest of the workbook, i.e. insert
column the macro ran and changed the commandbutton1 caption, relevant to the
contents in a cell other than B2?? I have no idea why this was happening

Did you insert the said column before column B in sheet "1"?

The recurring problems are rather strange...
BTW, what version of Excel and OS are you running?

Cheers,
-Basilisk96

Command Button Naming
 

So, it appears that the IS more to this problem than what we can see
on the surface... where can we go from here?


Tanya wrote:
Hi again Basilisk
I can get the code to work in a new workbook, just not my own?
Cheers
Tanya

Command Button Naming
 

"there IS" was what I meant, not "the IS". I need a break lol.

Basilisk96 wrote:
So, it appears that the IS more to this problem than what we can see

Command Button Naming
 

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

Command Button Naming
 

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" wrote:

"there IS" was what I meant, not "the IS". I need a break lol.

Basilisk96 wrote:
So, it appears that the IS more to this problem than what we can see

Command Button Naming
 

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

Command Button Naming
 

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