Auto change Caption on Command Bar via formula

[rob nobel]

Is it possible to have the caption on a Forms.CommandButton change according
to a value in a cell?
Rob

 

[Dave Peterson]

something like this:

With Worksheets("Sheet1")
.Buttons("button 1").Caption = .Range("a1").Value '.Text???
End With

 

[rob nobel]

Hi Dave,
Thanks for this but I can't get it to work.
What I've tried is...

Private Sub Button1_Click()
With Worksheets("Sheet1")
.Buttons("Button1").Caption = .Range("a1").Value '.Text???
End With
End Sub
(with "test" in cell A1)

Also tried varying the ("Button1") to ("Button1_Click") without success and
I don't have any other ideas to try. Do you?

When I click the button it says, "Unable to get the buttons property of the
worksheets class".
When I run the procedure in VBA it says, "Application-defined or
Object-defined error"

Rob

 

[Dave Peterson]

You said a button from the Forms toolbar?

Right click on the button and look at the name box (to the left of the
formulabar). What's the name of the button. (In a simple test (I just plopped
a button from the Forms toolbar onto the worksheet), the button was really
named: "Button 1" (note the space character).

And if the button were actually from the control toolbox toolbar, then how
about:

Private Sub CommandButton1_Click()
Me.CommandButton1.Caption = Me.Range("a1").Value
End Sub

(the Me represents the owner of the module. In this case, the worksheet
"sheet1".

And this code would be in the worksheet module--not a general module like the
code for the button from the Forms toolbar.

 

[rob nobel]

Grrr....Sorry Dave, it was a a control toolbox button. The forms procedure
will help me anyway so thanks for that too.
The Control button works fine, but only once I click the button. That's OK
but is there a way for the name to change as soon as the Value in A1 is
changed? (Sorry bout that..... there's always more, isn't there!)
Rob

 

[Dave Peterson]

How does the cell change--typing or the result of a calculation?

If by typing, put this behind the worksheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("a1")) Is Nothing Then Exit Sub

Me.CommandButton1.Caption = Range("a1").Value 'or .Text ??

End Sub

If it's the result of a calculation:
Private Sub Worksheet_Calculate()
Me.CommandButton1.Caption = Range("a1").Value 'or .Text ??
End Sub

Using the .text property will put what the cell looks like (try it with a
currency formatted cell). The .value will put the, er, value.

Grrr....Sorry Dave, it was a a control toolbox button. The forms procedure
will help me anyway so thanks for that too.
The Control button works fine, but only once I click the button. That's OK
but is there a way for the name to change as soon as the Value in A1 is
changed? (Sorry bout that..... there's always more, isn't there!)
Rob

 

[rob nobel]

Good stuff Dave! The Calculation option does the trick very well thank you.
In fact, the value property works for text as well although its always text
I'll need so I'll probably use the .Text property.
Rob