BILLYBOB1972

New Member
Joined
Jan 7, 2016
Messages
24
Office Version
  1. 2016
Platform
  1. Windows
I am trying to move data from a user form to an excel sheet and the code below does what I need for the first instance. The form may contain up to 100 text boxes txtUnits1, txtUnits2, txtUnits3 etc

What I need to do is for every text box move the corresponding data. If there isn't any values in the text box nothing gets moved.

I could copy the code below and change values but have been reading about loops but as I'm new to this I don't know where to start.

Can anyone help point me in the right direction to move the same data for txtUnits2 etc

Dim ws As Worksheet
Dim Addto As Range
Set ws = Sheet2
Set Addto = ws.Range("c65356").End(xlUp).Offset(1, 0)


With ws


If txtUnits1.Value > 0 Then Addto = txtName.Value
If txtUnits1.Value > 0 Then Addto_Offset(0, 1).Value = cboStore.Value
If txtUnits1.Value > 0 Then Addto_Offset(0, 2).Value = txtProduct.Value
If txtUnits1.Value > 0 Then Addto_Offset(0, 4).Value = txtUnits1.Value
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi billBob,

please when you post code, paste it between code brackets, see example below in red.

Your textboxes are controls. And controls are held in a collection. And collections allow you to loop through their contents.
This is the case for cells in a range, for sheets in a workbook, for shapes in a sheet, etc.

The way to do that is to declare a variable as the object you want to loop through, and than use the 'For Each [object] in [Collection] ... Next' loop.

In your case something like:
Code:
    Dim wsS2 As Worksheet
    Dim rAddto As Range
    Dim ctTB As MSForms.Control
    
    Set wsS2 = Sheet2
    
    
    With wsS2
        Set rAddto = .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0)
        
        'Loop through all the controls
        For Each ctTB In Me.Controls    ' Me is the UserForm itself
            If Left(ctTB.Name, 8) = "txtUnits" Then
            Debug.Print ctTB.Name       'you will see the names in the immediate window in the VBA editor. _
                                         Press Ctrl-G  to see this immediate window, bottom left below _
                                         the edit window
                
            If ctTB.Value > 0 Then      'test for >0 only once, then list all thethings to do
                rAddto = txtName.Value
                rAddto.Offset(0, 1).Value = cboStore.Value
                rAddto.Offset(0, 2).Value = txtProduct.Value
                rAddto.Offset(0, 4).Value = txtUnits1.Value
            End If
        Next ctTB
Note that I have renamed your variables slightly to indicate the type of variable. i also always use at least one capital letter in a variable when declaring it (Dim wsS2 as Sheet), because, when typing the code, (lower case only , wss2) VBA will then capitalise the letters (wss2 -> wsS2). If my variable in the code thne does not capitalise, it means I have made a typo.
 
Upvote 0
Hi sijpie

Thanks for your help. Thanks for the advice on how to post.

I have tried your code and from how you've explained I can work through how this works. I have run and got a compile error Next without For

Do you have any ideas on why I'm getting this error?
 
Upvote 0
Code:
    With wsS2
        Set rAddto = .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0)
        
        'Loop through all the controls
        For Each ctTB In Me.Controls    ' Me is the UserForm itself
            If Left(ctTB.Name, 8) = "txtUnits" Then
            Debug.Print ctTB.Name       'you will see the names in the immediate window in the VBA editor. _
                                         Press Ctrl-G  to see this immediate window, bottom left below _
                                         the edit window
            ' My guess, is you want an END IF right here...    
            If ctTB.Value > 0 Then      'test for >0 only once, then list all thethings to do
                rAddto = txtName.Value
                rAddto.Offset(0, 1).Value = cboStore.Value
                rAddto.Offset(0, 2).Value = txtProduct.Value
                rAddto.Offset(0, 4).Value = txtUnits1.Value
            End If
        Next ctTB

Hi sijpie

Thanks for your help. Thanks for the advice on how to post.

I have tried your code and from how you've explained I can work through how this works. I have run and got a compile error Next without For

Do you have any ideas on why I'm getting this error?


You have two 'If' statements...but only one 'End If.

This is causing your loop to fail. Close the other If statement and your loop will be fine. I would imagine the missing End If goes right below the Debug.Print
 
Last edited:
Upvote 0
Sorry, missed an End If
Code:
   Dim wsS2 As Worksheet
    Dim rAddto As Range
    Dim ctTB As MSForms.Control
    
    Set wsS2 = Sheet2
    
    
    With wsS2
        Set rAddto = .Range("C" & .Rows.Count).End(xlUp).Offset(1, 0)
        
        'Loop through all the controls
        For Each ctTB In Me.Controls    ' Me is the UserForm itself
            If Left(ctTB.Name, 8) = "txtUnits" Then
                Debug.Print ctTB.Name       'you will see the names in the immediate window in the VBA editor. _
                                         Press Ctrl-G  to see this immediate window, bottom left below _
                                         the edit window
                
                If ctTB.Value > 0 Then      'test for >0 only once, then list all thethings to do
                    rAddto = txtName.Value
                    rAddto.Offset(0, 1).Value = cboStore.Value
                    rAddto.Offset(0, 2).Value = txtProduct.Value
                    rAddto.Offset(0, 4).Value = txtUnits1.Value
                End If
            End If
        Next ctTB
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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