UserForm multiple TextBox values need to shorten formula

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
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
 

frabulator

Board Regular
Joined
Jun 27, 2014
Messages
108
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:

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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.
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
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.
 

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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:

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
Can you alter your code so that it opens the Menu_Names Form prior to the triggering the code...
 

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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
 

marimar02

Board Regular
Joined
May 21, 2010
Messages
104
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...

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
 

igold

Well-known Member
Joined
Jul 8, 2014
Messages
2,493
Office Version
365, 2010
Platform
Windows
You're welcome, I am glad that you have it working. Thanks for the feedback. We were both happy to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,095,399
Messages
5,444,249
Members
405,277
Latest member
Stu96

This Week's Hot Topics

Top