Edit Text in Button

[ianripping]

Is it possible to link the text in a button, to a cell?

 

[Bob Phillips]

Only programmatically, that is you would need to do it all.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[ianripping]

How do you mean

 

[Frank Kabel]

Hi
one way would be (if you have placed the button on a worksheet) to use
the worksheet_change event. The following code makes the assumptions:
- that your command butto is named 'Link_Button'
- the linked cell is A1

HTH
Frank

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim invent_index As Integer
On Error GoTo CleanUp
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

With Target
Application.EnableEvents = False
Link_Button.Caption = .Value
End With

CleanUp:
Application.EnableEvents = True
End Sub

 

[Bob Phillips]

Like that <G>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Like that <G>

Thanks but I still wonder why one wants to link the Button caption to a
cell. Maybe the OP will enlighten me ;-)

Frank

 

[Bob Phillips]

Pre-supposing the OP, but I think I can semi-understand it. If the sheet
gets regular updates, either automatically or by manual change, it is quite
possible that a button text may be helpful. The text could even be tested in
the macro to determine the action taken (although that could just as easily
be the cell value itself). The one thing we don't always get is a full
requirement, so we produce specific answers, which of course may not be the
best solutions.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)