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


--

Regards,
Nigel
(E-Mail Removed)



"Tanya" <(E-Mail Removed)> wrote in message
news:ADE2EA6B-E82B-41BB-B913-(E-Mail Removed)...
> Hi
> Is it possible to have the description/label of a command button updated
> by
> the title in a sheet cell?
> cheers
> Tanya

On Nov 27, 1:21 am, Tanya <Ta...@discussions.microsoft.com> 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

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 <Ta...@discussions.microsoft.com> 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
>

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
> (E-Mail Removed)
>
>
>
> "Tanya" <(E-Mail Removed)> wrote in message
> news:ADE2EA6B-E82B-41BB-B913-(E-Mail Removed)...
> > Hi
> > Is it possible to have the description/label of a command button updated
> > by
> > the title in a sheet cell?
> > cheers
> > 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


Tanya <(E-Mail Removed)> 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 <Ta...@discussions.microsoft.com> wrote:
>> > Hi
>> > Is it possible to have the description/label of a command button updated by
>> > the title in a sheet cell?
>> > cheers
>> > 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

"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 <(E-Mail Removed)> 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 <Ta...@discussions.microsoft.com> wrote:
> >> > Hi
> >> > Is it possible to have the description/label of a command button updated by
> >> > the title in a sheet cell?
> >> > cheers
> >> > Tanya
>
>

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

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 <(E-Mail Removed)> wrote:
>I'm trying to reproduce your problem, but I'm unable to.

Tanya <Ta...@discussions.microsoft.com> 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

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" <(E-Mail Removed)> wrote in message
news:ADE2EA6B-E82B-41BB-B913-(E-Mail Removed)...
> Hi
> Is it possible to have the description/label of a command button updated
> by
> the title in a sheet cell?
> cheers
> Tanya