Command Button Naming

[Tanya]

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

 

[Nigel]

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

 

[Basilisk96]

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

 

[Tanya]

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

 

[Tanya]

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

 

[Dave D-C]

[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

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]

 

[Tanya]

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

[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

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]

 

[dan dungan]

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.

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

[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

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]
:
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
Hi
Is it possible to have the description/label of a command button updated by
the title in a sheet cell?
cheers
Tanya

 

[Dave D-C]

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.

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

 

[Chip Pearson]

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]

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

 

[Tanya]

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

 

[Basilisk96]

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

 

[Tanya]

Hi Basilisk

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.



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

 

[Basilisk96]

Hi Tanya,

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.

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.

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

 

[Basilisk96]

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

 

[Tanya]

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

 

[Tanya]

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

 

[Basilisk96]

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

 

[Basilisk96]

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