Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Macro - Is there an easier way ?
Is there an easier way to do this macro ?
The textboxes are from the control toolbox. I will have up to 25 textboxes included in this routine. This macro sits in a normal module. Sub LoadBox(T1) Select Case T1 Case 1 ActiveSheet.EditBox.Text = ActiveSheet.TextBox1.Text Case 2 ActiveSheet.EditBox.Text = ActiveSheet.TextBox2.Text Case 3 ActiveSheet.EditBox.Text = ActiveSheet.TextBox3.Text Case 4 ActiveSheet.EditBox.Text = ActiveSheet.TextBox4.Text End Select End Sub Help much appreciated. Andrew Bourke |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Macro - Is there an easier way ?
Andrew,
You could pass the text directly to the EditBox... '-------------------------- Sub TransferText() Dim strText As String strText = Worksheets("Sheet1").TextBox2.Text LoadBox strText End Sub Sub LoadBox(ByRef T1 As String) ActiveSheet.EditBox.Text = T1 End Sub '------------------------ Jim Cone San Francisco, USA "Andrew B" wrote in message Is there an easier way to do this macro ? The textboxes are from the control toolbox. I will have up to 25 textboxes included in this routine. This macro sits in a normal module. Sub LoadBox(T1) Select Case T1 Case 1 ActiveSheet.EditBox.Text = ActiveSheet.TextBox1.Text Case 2 ActiveSheet.EditBox.Text = ActiveSheet.TextBox2.Text Case 3 ActiveSheet.EditBox.Text = ActiveSheet.TextBox3.Text Case 4 ActiveSheet.EditBox.Text = ActiveSheet.TextBox4.Text End Select End Sub Help much appreciated. Andrew Bourke |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Macro - Is there an easier way ?
Thanks for your reply Jim but this still does not
help me with trying to find a short cut way to refer to each textbox. I am after some way of referring to textbox* where * is a number from 1 to 25. Regards Andrew Jim Cone wrote: Andrew, You could pass the text directly to the EditBox... '-------------------------- Sub TransferText() Dim strText As String strText = Worksheets("Sheet1").TextBox2.Text LoadBox strText End Sub Sub LoadBox(ByRef T1 As String) ActiveSheet.EditBox.Text = T1 End Sub '------------------------ Jim Cone San Francisco, USA "Andrew B" wrote in message Is there an easier way to do this macro ? The textboxes are from the control toolbox. I will have up to 25 textboxes included in this routine. This macro sits in a normal module. Sub LoadBox(T1) Select Case T1 Case 1 ActiveSheet.EditBox.Text = ActiveSheet.TextBox1.Text Case 2 ActiveSheet.EditBox.Text = ActiveSheet.TextBox2.Text Case 3 ActiveSheet.EditBox.Text = ActiveSheet.TextBox3.Text Case 4 ActiveSheet.EditBox.Text = ActiveSheet.TextBox4.Text End Select End Sub Help much appreciated. Andrew Bourke |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Macro - Is there an easier way ?
Andrew,
I was thinking that if you knew the textbox suffix you would also be able to determine its text and pass that thru. However, here is what I believe you want... "------------------------------- Sub TransferText() Dim T1 As Long T1 = 2 LoadBox T1 End Sub Sub LoadBox(ByRef T1 As Long) Dim objTB As OLEObject Set objTB = ActiveSheet.OLEObjects("TextBox" & T1) ActiveSheet.EditBox.Text = objTB.Object.Text Set objTB = Nothing End Sub '--------------------------- "Andrew B" wrote in message ... Thanks for your reply Jim but this still does not help me with trying to find a short cut way to refer to each textbox. I am after some way of referring to textbox* where * is a number from 1 to 25. Regards Andrew Jim Cone wrote: Andrew, You could pass the text directly to the EditBox... '-------------------------- Sub TransferText() Dim strText As String strText = Worksheets("Sheet1").TextBox2.Text LoadBox strText End Sub Sub LoadBox(ByRef T1 As String) ActiveSheet.EditBox.Text = T1 End Sub '------------------------ Jim Cone San Francisco, USA "Andrew B" wrote in message Is there an easier way to do this macro ? The textboxes are from the control toolbox. I will have up to 25 textboxes included in this routine. This macro sits in a normal module. Sub LoadBox(T1) Select Case T1 Case 1 ActiveSheet.EditBox.Text = ActiveSheet.TextBox1.Text Case 2 ActiveSheet.EditBox.Text = ActiveSheet.TextBox2.Text Case 3 ActiveSheet.EditBox.Text = ActiveSheet.TextBox3.Text Case 4 ActiveSheet.EditBox.Text = ActiveSheet.TextBox4.Text End Select End Sub Help much appreciated. Andrew Bourke |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextBox Macro - Is there an easier way ?
Yes! Yes! Yes!
It works - thank you very much Jim. I've spent hours just trying to get this simple thing to work. Sometimes I find it difficult to express exactly what I want to do because my knowledge of the higher order instructions in Excel is very limited compared to people like yourself. Andrew Bourke Jim Cone wrote: Andrew, I was thinking that if you knew the textbox suffix you would also be able to determine its text and pass that thru. However, here is what I believe you want... "------------------------------- Sub TransferText() Dim T1 As Long T1 = 2 LoadBox T1 End Sub Sub LoadBox(ByRef T1 As Long) Dim objTB As OLEObject Set objTB = ActiveSheet.OLEObjects("TextBox" & T1) ActiveSheet.EditBox.Text = objTB.Object.Text Set objTB = Nothing End Sub '--------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
'Find' textbox value macro | Excel Discussion (Misc queries) | |||
a macro that Formats a textbox based on value in a cell | Excel Worksheet Functions | |||
Forms Textbox and Assign Macro | Excel Programming | |||
textbox in a macro | Excel Programming | |||
Excel VBA/Macro - Textbox | Excel Programming |