Tom Ogilvy again
 

Hi Tom

You helped me a great deal last time with a code to change the text in
"Format Button".
Below I have tried to modify that code to work in my application, but as you
can see, I'm not so succesfull.

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 you can help me with this one

//Thomas

Tom Ogilvy again
 

what is the sheet name for the sheet with values in E9:E109. What is the
sheet name for the sheet with the buttons?
How does the single column of cells map to the 10 x 10 matrix - rows then
columns or columns then rows. K9 maps to which cell in E9:E109 for example.


What will make the values change in E9:E109. Are the values in E9:E109
generated by formulas in those cells? the bottom line, what will make the
cells change so you want the button(s) updated? Is it a DDE link?

By the way:
? Range("E9:E109").count
101

a 10 x 10 matrix is = 100

--
Regards,
Tom Ogilvy




"Jonsson" wrote in message
...
Hi Tom

You helped me a great deal last time with a code to change the text in
"Format Button".
Below I have tried to modify that code to work in my application, but as
you
can see, I'm not so succesfull.

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 you can help me with this one

//Thomas

Tom Ogilvy again
 

Hi Tom,
Thanks for answering.
Sorry for being unclear in my explanation!

I'll try to explain it better!

E9:E109 =Sheet1 (I have an extra row, to get headlines once again because of
pagebreak, therefore that extra row=101)
Sheet with buttons=Sheet2
The formulas are underneath the buttons (cellvalue is in the same cell as
the comparing button)
K9 Sheet2=E18 Sheet1
The value in E9:E109 is changed manually, and the returned value from
Sheet1, cell "E9" goes to Sheet2, cell "B9", the same cell as the button to
be change are.

Hope my explanation will do!

Thanks for your effort to help me.

//Thomas


"Tom Ogilvy" skrev i meddelandet
...
what is the sheet name for the sheet with values in E9:E109. What is the
sheet name for the sheet with the buttons?
How does the single column of cells map to the 10 x 10 matrix - rows then
columns or columns then rows. K9 maps to which cell in E9:E109 for
example.


What will make the values change in E9:E109. Are the values in E9:E109
generated by formulas in those cells? the bottom line, what will make the
cells change so you want the button(s) updated? Is it a DDE link?

By the way:
? Range("E9:E109").count
101

a 10 x 10 matrix is = 100

--
Regards,
Tom Ogilvy




"Jonsson" wrote in message
...
Hi Tom

You helped me a great deal last time with a code to change the text in
"Format Button".
Below I have tried to modify that code to work in my application, but as
you
can see, I'm not so succesfull.

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 Th
en
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 you can help me with this one

//Thomas

Tom Ogilvy again
 

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


--
Regards,
Tom Ogilvy

"Jonsson" wrote in message
...
Hi Tom,
Thanks for answering.
Sorry for being unclear in my explanation!

I'll try to explain it better!

E9:E109 =Sheet1 (I have an extra row, to get headlines once again because
of
pagebreak, therefore that extra row=101)
Sheet with buttons=Sheet2
The formulas are underneath the buttons (cellvalue is in the same cell as
the comparing button)
K9 Sheet2=E18 Sheet1
The value in E9:E109 is changed manually, and the returned value from
Sheet1, cell "E9" goes to Sheet2, cell "B9", the same cell as the button
to
be change are.

Hope my explanation will do!

Thanks for your effort to help me.

//Thomas


"Tom Ogilvy" skrev i meddelandet
...
what is the sheet name for the sheet with values in E9:E109. What is
the
sheet name for the sheet with the buttons?
How does the single column of cells map to the 10 x 10 matrix - rows
then
columns or columns then rows. K9 maps to which cell in E9:E109 for
example.


What will make the values change in E9:E109. Are the values in E9:E109
generated by formulas in those cells? the bottom line, what will make
the
cells change so you want the button(s) updated? Is it a DDE link?

By the way:
? Range("E9:E109").count
101

a 10 x 10 matrix is = 100

--
Regards,
Tom Ogilvy




"Jonsson" wrote in message
...
Hi Tom

You helped me a great deal last time with a code to change the text in
"Format Button".
Below I have tried to modify that code to work in my application, but
as
you
can see, I'm not so succesfull.

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
Th
en
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 you can help me with this one

//Thomas