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

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

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

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

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
'---------------------------