Shroter Way Text boxes

dantb

Active Member
Joined
Mar 20, 2002
Messages
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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.
 
Upvote 0
Ok Will try Thanks again for your Help Juan.
Dan
This message was edited by Juan Pablo G. on 2002-03-28 12:01
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top