Shroter Way Text boxes

dantb · Mar 28th, 2002, 12:52 PM

HI All. What I have is a userform with 20 textboxs to fill out. The data is going on one sheet all in the same row. What I was wondering is if you have to enter the code below in each box,and change the cell reference's or is there a shorter way. The code works fine, I just have 200 boxs's total to complete. Thx Dan

Private Sub txtname_AfterUpdate()
Sheets("Customer").Range("E5").Value = TxtName.Text
End Sub

Juan Pablo González · Mar 28th, 2002, 12:55 PM

I think there's a shorter way using a class module, but, for a faster solution, use Objects. For example, put at the top of the UserForm module

Dim WS as Worksheet

in the Initialize event of the Userform put

Set WS = Sheets("Customer")

and in the AfterUpdate event of the textbox put

WS.[E5] = TextBox

You can drop the .Value and .Text. Those are the default properties.

dantb · Mar 28th, 2002, 12:59 PM

Ok Will try Thanks again for your Help Juan.
Dan

[ This Message was edited by: Juan Pablo G. on 2002-03-28 12:01 ]

davers5 · Mar 28th, 2002, 01:03 PM

If you create the text boxes as a control array you could easily put the values on the sheet using a for...next loop.

For n=0 to 200 'however many text boxes you have
cells(1,n+1).value=txtTextBox(n).text
next n

This will print the value of each text box in the array in a different column.

Good luck!

Dave

Juan Pablo González · Mar 28th, 2002, 01:08 PM

Quote:

On 2002-03-28 12:03, davers5 wrote:
If you create the text boxes as a control array you could easily put the values on the sheet using a for...next loop.

For n=0 to 200 'however many text boxes you have
cells(1,n+1).value=txtTextBox(n).text
next n

This will print the value of each text box in the array in a different column.

Good luck!

Dave

Mhmm, Dave, I'm not sure you can create control Arrays in VBA...

davers5 · Mar 28th, 2002, 01:11 PM

You can't? I've never tried, I just assumed you could! I'm going to try right now...

dantb · Mar 28th, 2002, 01:14 PM

Ive tried to make a Array before and gave up, couldnt figure out how.

davers5 · Mar 28th, 2002, 01:19 PM

Wow! I guess you can't create a control array in VBA. Sorry!

Juan Pablo González · Mar 28th, 2002, 01:20 PM

That's ok, I believe only in VB, not VBA. That's why you would need a Class module to handle things sort of like you suggested !

Dave Hawley · Mar 28th, 2002, 06:42 PM

Hi dantb

There are quite a few ways to do this. One it to simply set the ControSource of each textbox to the cell they should fill.

The other is a simple loop using the Tag property of the TextBox. Place the cell address in the Tag property of the Textboxes, eg A1 , B10, C25 etc

The use

Dim CControl As Control

On Error Resume Next
For Each CControl In Me.Controls
Range(CControl.Tag) = CControl
Next
On Error GoTo 0

Mar 28th, 2002, 12:55 PM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	I think there's a shorter way using a class module, but, for a faster solution, use Objects. For example, put at the top of the UserForm module Dim WS as Worksheet in the Initialize event of the Userform put Set WS = Sheets("Customer") and in the AfterUpdate event of the textbox put WS.[E5] = TextBox You can drop the .Value and .Text. Those are the default properties. __________________ Regards, Juan Pablo González http://www.juanpg.com

Mar 28th, 2002, 01:20 PM	#9
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,927	That's ok, I believe only in VB, not VBA. That's why you would need a Class module to handle things sort of like you suggested ! __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 28th, 2002, 12:52 PM	#1
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	HI All. What I have is a userform with 20 textboxs to fill out. The data is going on one sheet all in the same row. What I was wondering is if you have to enter the code below in each box,and change the cell reference's or is there a shorter way. The code works fine, I just have 200 boxs's total to complete. Thx Dan Private Sub txtname_AfterUpdate() Sheets("Customer").Range("E5").Value = TxtName.Text End Sub

Mar 28th, 2002, 12:59 PM	#3
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	Ok Will try Thanks again for your Help Juan. Dan [ This Message was edited by: Juan Pablo G. on 2002-03-28 12:01 ]

Mar 28th, 2002, 01:03 PM	#4
davers5 Board Regular Join Date: Feb 2002 Posts: 255	If you create the text boxes as a control array you could easily put the values on the sheet using a for...next loop. For n=0 to 200 'however many text boxes you have cells(1,n+1).value=txtTextBox(n).text next n This will print the value of each text box in the array in a different column. Good luck! Dave

Mar 28th, 2002, 01:11 PM	#6
davers5 Board Regular Join Date: Feb 2002 Posts: 255	You can't? I've never tried, I just assumed you could! I'm going to try right now...

Mar 28th, 2002, 01:14 PM	#7
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	Ive tried to make a Array before and gave up, couldnt figure out how.

Mar 28th, 2002, 01:19 PM	#8
davers5 Board Regular Join Date: Feb 2002 Posts: 255	Wow! I guess you can't create a control array in VBA. Sorry!

Mar 28th, 2002, 06:42 PM	#10
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	Hi dantb There are quite a few ways to do this. One it to simply set the ControSource of each textbox to the cell they should fill. The other is a simple loop using the Tag property of the TextBox. Place the cell address in the Tag property of the Textboxes, eg A1 , B10, C25 etc The use Dim CControl As Control On Error Resume Next For Each CControl In Me.Controls Range(CControl.Tag) = CControl Next On Error GoTo 0