change text in FormatButton

[Jonsson]

Hi all,
Below I have tried to modify a code to work in my application, but as you
can see, I'm not so succesful.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim btn As Button
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B9:B18")) Is Nothing _
And Target.Value <> "" Then
For Each btn In Me.Buttons
If Not Intersect(btn.TopLeftCell.Offset(0, 0), Target) Is Nothing Then
btn.Caption = Target
If Not Intersect(Target, Range("c9:c18")) Is Nothing _
And Target.Value <> "" Then
If Not Intersect(btn.TopLeftCell.Offset(0, 0), Target) Is Nothing Then
btn.Caption = Target
Exit For
End If
Next
End If

End Sub


This is what I want to do:
In a separate sheet I set the values in cells E9:E109. Then return that
value to a cell in the same sheet as I have the buttons.
I have 100 buttons in 10x10 area (cell B9:K18 gets the value from
E9:E109)The value should return under the button and then be displayed in
the button. Maybe it's possible to return the value directly from the other
sheet and cells E9:E109.?

Hope someone can help me with this one

//Thomas

 

[Tom Ogilvy]

Provide the requested information and corrections and perhaps someone can
help.

 

[Jonsson]

Hi Tom
I'ts already done in the post "Re: Tom Ogilvy again"
You had many questions and I tried to answer them the best I could, inspite
of my poor english.

Thomas

 

[Tom Ogilvy]

In Sheet2 (sheet with the buttons), right click on the sheet tab and select
view code. Put in this code:

Private Sub Worksheet_Calculate()
Dim btn As Button
For Each btn In Me.Buttons
If btn.TopLeftCell.Text <> "" Then
btn.Caption = btn.TopLeftCell.Value
End If
Next
End Sub

 

[Jonsson]

Tom!
Just what I needed
You are great!!

Thank you

//Thomas