Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return text in textButton from cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return text in textButton from cell
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return text in textButton from cell
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return text in textButton from cell
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF cell contains certain text return value | Excel Worksheet Functions | |||
Enter text in a cell to return a text value in same cell | Excel Discussion (Misc queries) | |||
lookup a text cell and return text | Excel Discussion (Misc queries) | |||
How do you make cell 2 return data if cell 1 contains text? | Excel Discussion (Misc queries) | |||
return textfrom cell into textbutton/commandbutton | Excel Programming |