Results 1 to 10 of 10

Thread: UserForm multiple TextBox values need to shorten formula

  1. #1
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default UserForm multiple TextBox values need to shorten formula

    Hello,

    what is the way to shorten this formula:

    Code:
    Private Sub CommandButton1_Click()
    
    Dim ws As Worksheet
    
    
    Set ws = Worksheets("Settings")
    
    
    Unload Menu
    
    
    With Menu_Names
    
    
        .TextBox1.Value = ws.Range("V2").Value
        .TextBox5.Value = ws.Range("V3").Value
        .TextBox9.Value = ws.Range("V4").Value
        .TextBox13.Value = ws.Range("V5").Value
        .TextBox17.Value = ws.Range("V6").Value
        .TextBox21.Value = ws.Range("V7").Value
        .TextBox25.Value = ws.Range("V8").Value
        .TextBox29.Value = ws.Range("V9").Value
        .TextBox33.Value = ws.Range("V10").Value
    
    
        .TextBox2.Value = ws.Range("W2").Value
        .TextBox6.Value = ws.Range("W3").Value
        .TextBox10.Value = ws.Range("W4").Value
        .TextBox14.Value = ws.Range("W5").Value
        .TextBox18.Value = ws.Range("W6").Value
        .TextBox22.Value = ws.Range("W7").Value
        .TextBox26.Value = ws.Range("W8").Value
        .TextBox30.Value = ws.Range("W9").Value
        .TextBox34.Value = ws.Range("W10").Value
    
    
        .TextBox3.Value = ws.Range("X2").Value
        .TextBox7.Value = ws.Range("X3").Value
        .TextBox11.Value = ws.Range("X4").Value
        .TextBox15.Value = ws.Range("X5").Value
        .TextBox19.Value = ws.Range("X6").Value
        .TextBox23.Value = ws.Range("X7").Value
        .TextBox27.Value = ws.Range("X8").Value
        .TextBox31.Value = ws.Range("X9").Value
        .TextBox35.Value = ws.Range("X10").Value
    
    
        .TextBox4.Value = ws.Range("Y2").Value
        .TextBox8.Value = ws.Range("Y3").Value
        .TextBox12.Value = ws.Range("Y4").Value
        .TextBox16.Value = ws.Range("Y5").Value
        .TextBox20.Value = ws.Range("Y6").Value
        .TextBox24.Value = ws.Range("Y7").Value
        .TextBox28.Value = ws.Range("Y8").Value
        .TextBox32.Value = ws.Range("Y9").Value
        .TextBox36.Value = ws.Range("Y10").Value
    
    
    End With
    
    
    Menu_Names.Show
    
    
    Set ws = Nothing
    
    
    Unload Me
    
    
    End Sub

  2. #2
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,361
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    For this section only:

    Code:
     .TextBox1.Value = ws.Range("V2").Value
        .TextBox5.Value = ws.Range("V3").Value
        .TextBox9.Value = ws.Range("V4").Value
        .TextBox13.Value = ws.Range("V5").Value
        .TextBox17.Value = ws.Range("V6").Value
        .TextBox21.Value = ws.Range("V7").Value
        .TextBox25.Value = ws.Range("V8").Value
        .TextBox29.Value = ws.Range("V9").Value
        .TextBox33.Value = ws.Range("V10").Value
    to give you an idea of the logic, how about something like this:

    Code:
    Private Sub CommandButton1_Click()
    
    
        Dim ws As Worksheet
        Dim x As Long, i As Long
        
        Set ws = Worksheets("Settings")
    
    
        Unload Menu
        
        x = 1
        For i = 1 To 33 Step 4
            x = x + 1
             Controls("TextBox" & i).Value = Worksheets("Settings").Range("V" & x).Value
        Next
    
    
    End Sub
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    You could try condensing it down into a loop like this (not tested)

    Code:
    Dim i As Integer
    Dim p As Integer
    Dim rc as Integer
    p = 0
    rc = 1
    
    For i = 1 To 36
        Dim c as String
        c = "V"
        p = p + 1
        rc = rc + 1
    
        If rc => 11 Then rc = 2 
    
        If p = 1 then
            c = "V"
        ElseIf p = 2 then
            c = "W"
        ElseIf p = 3 then
            c = "X"
        Else
            p = 0 
            c = "Y"
        End If
    
        Controls("TextBox" & i).Value = ws.Range(c & rc ).Value
    Next i
    edit* forgot to add in the RC variable
    Last edited by frabulator; Jul 10th, 2019 at 08:31 PM.

  4. #4
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    In either case it bugs out at
    Code:
    Controls("TextBox" & i).Value
    line giving a weird Run-time error '-2147024809 (80070057)': Could not find the specified object.

  5. #5
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,361
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    Well my code is tested and it worked, either way if both responses use the same command with the same syntax then perhaps the code is not the issue..

    Are you running it with the form open and clicking a command button on the form to trigger the code.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  6. #6
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    Basically, it's a UserForm within Userform

    1. a Menu UserForm opens and that contains 4 command buttons
    2. clicking a CommandButton1 unloads the Menu and this code runs before it opens the Menu_Names UserForm

    here is what I've done with your code @igold

    Code:
    Private Sub CommandButton1_Click()
    
    Dim ws As Worksheet
    Dim x As Long, i As Long
        
    Set ws = Worksheets("Settings")
    
    
    Unload Menu
    
    
        x = 1
        For i = 1 To 33 Step 4
            x = x + 1
             Controls("TextBox" & i).Value = ws.Range("V" & x).Value 'this is the line where the code bugs out. It looks like it should work. Perhaps it has to do with how I unload main UserForm "Menu"?
        Next
    
    
    'With Menu_Names
    
    
        '.TextBox1.Value = ws.Range("V2").Value
        '.TextBox5.Value = ws.Range("V3").Value
        '.TextBox9.Value = ws.Range("V4").Value
        '.TextBox13.Value = ws.Range("V5").Value
        '.TextBox17.Value = ws.Range("V6").Value
        '.TextBox21.Value = ws.Range("V7").Value
        '.TextBox25.Value = ws.Range("V8").Value
        '.TextBox29.Value = ws.Range("V9").Value
        '.TextBox33.Value = ws.Range("V10").Value
    
    
        '.TextBox2.Value = ws.Range("W2").Value
        '.TextBox6.Value = ws.Range("W3").Value
        '.TextBox10.Value = ws.Range("W4").Value
        '.TextBox14.Value = ws.Range("W5").Value
        '.TextBox18.Value = ws.Range("W6").Value
        '.TextBox22.Value = ws.Range("W7").Value
        '.TextBox26.Value = ws.Range("W8").Value
        '.TextBox30.Value = ws.Range("W9").Value
        '.TextBox34.Value = ws.Range("W10").Value
    
    
        '.TextBox3.Value = ws.Range("X2").Value
        '.TextBox7.Value = ws.Range("X3").Value
        '.TextBox11.Value = ws.Range("X4").Value
        '.TextBox15.Value = ws.Range("X5").Value
        '.TextBox19.Value = ws.Range("X6").Value
        '.TextBox23.Value = ws.Range("X7").Value
        '.TextBox27.Value = ws.Range("X8").Value
        '.TextBox31.Value = ws.Range("X9").Value
        '.TextBox35.Value = ws.Range("X10").Value
    
    
        '.TextBox4.Value = ws.Range("Y2").Value
        '.TextBox8.Value = ws.Range("Y3").Value
        '.TextBox12.Value = ws.Range("Y4").Value
        '.TextBox16.Value = ws.Range("Y5").Value
        '.TextBox20.Value = ws.Range("Y6").Value
        '.TextBox24.Value = ws.Range("Y7").Value
        '.TextBox28.Value = ws.Range("Y8").Value
        '.TextBox32.Value = ws.Range("Y9").Value
        '.TextBox36.Value = ws.Range("Y10").Value
    
    
    'End With
    
    
    Menu_Names.Show
    
    
    Set ws = Nothing
    
    
    End Sub
    Last edited by marimar02; Jul 10th, 2019 at 09:48 PM.

  7. #7
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,361
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    Can you alter your code so that it opens the Menu_Names Form prior to the triggering the code...
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


  8. #8
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    I think I know what it is. I probably have to define with which UserForm
    Code:
    Controls("TextBox" & i).Value = ws.Range("V" & x).Value
    should run. In this case with "Menu_Names".

    Code:
    Menu_Names.Controls("TextBox" & i).Value = ws.Range("V" & x).Value
    .

    just tried it as I was typing this and it worked. Now I have to do this for remaining TextBoxes

    Thanks so much. I'll also try @frabulator 's code

  9. #9
    Board Regular
    Join Date
    May 2010
    Location
    Laguna Niguel, CA
    Posts
    96
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    This somewhat works. It spreads the data in the correct columns but not rows. I feel I can improve on it. Or maybe simply with @igold 's code. Thank you...

    Quote Originally Posted by frabulator View Post
    You could try condensing it down into a loop like this (not tested)

    Code:
    Dim i As Integer
    Dim p As Integer
    Dim rc as Integer
    p = 0
    rc = 1
    
    For i = 1 To 36
        Dim c as String
        c = "V"
        p = p + 1
        rc = rc + 1
    
        If rc => 11 Then rc = 2 
    
        If p = 1 then
            c = "V"
        ElseIf p = 2 then
            c = "W"
        ElseIf p = 3 then
            c = "X"
        Else
            p = 0 
            c = "Y"
        End If
    
        Controls("TextBox" & i).Value = ws.Range(c & rc ).Value
    Next i
    edit* forgot to add in the RC variable

  10. #10
    Board Regular igold's Avatar
    Join Date
    Jul 2014
    Location
    Delray Beach, FL, USA
    Posts
    2,361
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    1 Thread(s)

    Default Re: UserForm multiple TextBox values need to shorten formula

    You're welcome, I am glad that you have it working. Thanks for the feedback. We were both happy to help.
    ​igold

    I'm a drinker with a coding problem...

    All code is written with Excel 2010 - Please test all code on a backup copy of your data.


Some videos you may like

User Tag List

Tags for this Thread

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
  •