UserForm multiple TextBox values need to shorten formula

M

marimar02

Board Regular
Joined
May 21, 2010
Messages
128
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Can you alter your code so that it opens the Menu_Names Form prior to the triggering the code...
 
Upvote 0
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
 
Upvote 0
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...

frabulator said:
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
Click to expand...
 
Upvote 0
You're welcome, I am glad that you have it working. Thanks for the feedback. We were both happy to help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
format Date in a Userform TextBox (yyyy-mm-dd) from data entered in a cell
Replies
9
Views
927
dmt32
D
P
  • Question Question
USERFORM button for UPDATE error "block if without end if"
2
Replies
16
Views
855
dmt32
D
P
  • Question Question
Userform which stops duplicates entering data sheet
Replies
1
Views
510
RoryA
RoryA
L
  • Question Question
Userform help , sending data to sheet
Replies
8
Views
591
learnig1
L
dss28
  • Question Question
VBA excel code for saving task with frequency based on incremental date value
Replies
3
Views
993
dss28
dss28

Forum statistics

Threads
1,223,898
Messages
6,175,274
Members
452,628
Latest member
dd2

Share this page

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