Return text in textButton from cell
That was a typo on my part (two actually) ( I guess I should write my code
from scratch rather than editing existing code in an email).
Private Sub Worksheet_Change(ByVal Target As Range)
Dim btn As Button
If Target.Count 1 Then Exit Sub
If Not Intersect(Target, Range("A2:A31")) Is Nothing _
And Target.Value < "" Then
For Each btn In Me.Buttons
If Not Intersect(btn.TopLeftCell.Offset(0, -1), Target) Is Nothing Then
btn.Caption = Target
Exit For
End If
Next
End If
End Sub
I tested the above in the code module of the sheet containing the buttons
and it worked for me.
--
Regards,
Tom Ogilvy
"Jonsson" wrote in message
...
Hi again, Tom!
Sorry for being a jerk, I wasn't doing the right thing. Apolygize for
that.
Your code works great!
Now, when pasting your code for 30 buttons, I get an error in line:
If not Intersect(Target.Address,Range("A2:A31)) is Nothing _
And Target.Value < "" Then
I have tried to change it into 1 line but no result.
Maybe the problem is the language, I use Swedish version.
//Thomas
"Jonsson" skrev i meddelandet
...
Hi Tom!
Thanks for answering.
Am I supposed to place your code into the worksheets modul , and change
it
to the buttons actual name?
If so, I get a dialogbox poppin up asking for macros when I try to run
it.
I'm sure I'm doing something wrong here.................
//Thomas
"Tom Ogilvy" skrev i meddelandet
...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" And Target.Value < "" Then
Me.Buttons("Button1").Caption = Target.Value
End If
End Sub
Change Button1 to the name of your button from the Forms toolbar.
Now you say you have 30 buttons. Assume the buttons are in column B
starting in Row2 and lined up with the cells where the values will be
place
(A2 to A31
Private Sub Worksheet_Change(ByVal Target As Range)
Dim btn as Button
if Target.Count 1 then exit sub
If not Intersect(Target.Address,Range("A2:A31)) is Nothing _
And Target.Value < "" Then
for each btn in me.Buttons
if not intersect(btn.TopLeftCell.offset(0,-1),Target) is nothing
Then
btn.Caption = Target
exit for
end if
Next
End if
End Sub
--
Regards,
Tom Ogilvy
"Jonsson" wrote in message
...
Hi all,
I need to change the text in textButton by doing the following:
When I write for instance"2" in cell A2 I want the "2" value to be
displayed
as text in the textButton
I got this code from "papou" just a while a go, but I need to modify
it
so
it works in a textbutton instead(the one you get from "Format"
toolbar).
I have tried to record the event but I failed it.
A little twist: I have 30 buttons I want to be able to do this
on...............
Thanks in advance!
//Thomas
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$15" And Target.Value < "" Then
Me.CommandButton1.Caption = Target.Value
End If
End Sub
|