Command Button Naming

  • Thread starter Thread starter Tanya
  • Start date Start date
T

Tanya

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

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
 
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
 
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
 
[12:48AM!?]
The
Private Sub Worksheet_Change(ByVal Target As Range)
Click to expand...
goes in Sheet1; and you need
Sheets("sheetX").CommandButton1.Caption = Target.Value
D-C Dave


Tanya said:
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]
Click to expand...
 
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 said:
[12:48AM!?]
The
Private Sub Worksheet_Change(ByVal Target As Range)
Click to expand...
goes in Sheet1; and you need
Sheets("sheetX").CommandButton1.Caption = Target.Value
D-C Dave


Tanya said:
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]
Click to expand...
Click to expand...
 
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

Dave D-C said:
[12:48AM!?]
The
Private Sub Worksheet_Change(ByVal Target As Range)
Click to expand...
goes in Sheet1; and you need
Sheets("sheetX").CommandButton1.Caption = Target.Value
D-C Dave
Click to expand...
Tanya said:
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
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
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 said:
I'm trying to reproduce your problem, but I'm unable to.
Click to expand...
 
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)
 
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
 
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
 
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
Click to expand...

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
Click to expand...

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
 
Hi Basilisk


Basilisk96 said:
Am I correct to assume that the sheet tab of your Sheet1 object is
named "1"?
Click to expand...
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.
Click to expand...

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.
Click to expand...


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
Click to expand...

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) ?
Click to expand...
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
 
Hi Tanya,
I haven't had this error? <<<<<<<<
Click to expand...

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?
Click to expand...

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' <<<<<<<<<<
Click to expand...

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
Click to expand...

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
 
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
 
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
 
Back
Top