Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Shroter Way Text boxes

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok Will try Thanks again for your Help Juan.
    Dan

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

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Arkansas
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •