UserForm insert data to SpreadSheet- Whats Faster?

[mEt]

I have 13 different "ranges" we'll say of data that has to be inserted from text boxes to specific rows on a spreadsheet. My current setup is as follows:

Private Sub btnSUBMIT_Click() 
     
     ' Define variables
    Dim R1 As Range, R2 As Range, R3 As Range, R4 As Range, R5 As Range, R6 As Range, R7 As Range 
    Dim R8 As Range, R9 As Range, R10 As Range, R11 As Range, R12 As Range, R13 As Range 
    Dim pps As Variant 
    Set R1 = Range("D6") 
    Set R2 = Range("D7") 
    Set R3 = Range("D8") 
    Set R4 = Range("D9") 
    Set R5 = Range("D13") 
    Set R6 = Range("D14") 
    Set R7 = Range("D15") 
    Set R8 = Range("D16") 
    Set R9 = Range("D17") 
    Set R10 = Range("D18") 
    Set R11 = Range("D19") 
    Set R12 = Range("D20") 
    Set R13 = Range("D21") 
     
    Application.ScreenUpdating = False 
     
     ' Insert data
    Range("C5").Value = ppsbox.Value 
    With wbMain 
        R1.Value = smonbox1.Value 
        R1.Offset(0, 1).Value = stuesbox1.Value 
        R1.Offset(0, 2).Value = swedbox1.Value 
        R1.Offset(0, 3).Value = sthurbox1.Value 
        R1.Offset(0, 4).Value = sfribox1.Value 
        R1.Offset(0, 7).Value = smonbox2.Value 
        R1.Offset(0, 8).Value = stuesbox2.Value 
        R1.Offset(0, 9).Value = swedbox2.Value 
        R1.Offset(0, 10).Value = sthurbox2.Value 
        R1.Offset(0, 11).Value = sfribox2.Value 
        R2.Value = lomonbox1.Value 
        R2.Offset(0, 1).Value = lotuesbox1.Value 
        R2.Offset(0, 2).Value = lowedbox1.Value 
        R2.Offset(0, 3).Value = lothurbox1.Value 
        R2.Offset(0, 4).Value = lofribox1.Value 
        R2.Offset(0, 7).Value = lomonbox2.Value 
        R2.Offset(0, 8).Value = lotuesbox2.Value 
        R2.Offset(0, 9).Value = lowedbox2.Value 
        R2.Offset(0, 10).Value = lothurbox2.Value 
        R2.Offset(0, 11).Value = lofribox2.Value 
        R3.Value = limonbox1.Value 
        R3.Offset(0, 1).Value = lituesbox1.Value 
        R3.Offset(0, 2).Value = liwedbox1.Value 
        R3.Offset(0, 3).Value = lithurbox1.Value 
        R3.Offset(0, 4).Value = lifribox1.Value 
        R3.Offset(0, 7).Value = limonbox2.Value 
        R3.Offset(0, 8).Value = lituesbox2.Value 
        R3.Offset(0, 9).Value = liwedbox2.Value 
        R3.Offset(0, 10).Value = lithurbox2.Value 
        R3.Offset(0, 11).Value = lifribox2.Value 
        R4.Value = emonbox1.Value 
        R4.Offset(0, 1).Value = etuesbox1.Value 
        R4.Offset(0, 2).Value = ewedbox1.Value 
        R4.Offset(0, 3).Value = ethurbox1.Value 
        R4.Offset(0, 4).Value = efribox1.Value 
        R4.Offset(0, 7).Value = emonbox2.Value 
        R4.Offset(0, 8).Value = etuesbox2.Value 
        R4.Offset(0, 9).Value = ewedbox2.Value 
        R4.Offset(0, 10).Value = ethurbox2.Value 
        R4.Offset(0, 11).Value = efribox2.Value 
        R5.Value = thmonbox1.Value 
        R5.Offset(0, 1).Value = thtuesbox1.Value 
        R5.Offset(0, 2).Value = thwedbox1.Value 
        R5.Offset(0, 3).Value = ththurbox1.Value 
        R5.Offset(0, 4).Value = thfribox1.Value 
        R5.Offset(0, 7).Value = thmonbox2.Value 
        R5.Offset(0, 8).Value = thtuesbox2.Value 
        R5.Offset(0, 9).Value = thwedbox2.Value 
        R5.Offset(0, 10).Value = ththurbox2.Value 
        R5.Offset(0, 11).Value = thfribox2.Value 
        If billTot >= 2 Then 
            R6.Value = b2monbox1.Value 
            R6.Offset(0, 1).Value = b2tuesbox1.Value 
            R6.Offset(0, 2).Value = b2wedbox1.Value 
            R6.Offset(0, 3).Value = b2thurbox1.Value 
            R6.Offset(0, 4).Value = b2fribox1.Value 
            R6.Offset(0, 7).Value = b2monbox2.Value 
            R6.Offset(0, 8).Value = b2tuesbox2.Value 
            R6.Offset(0, 9).Value = b2wedbox2.Value 
            R6.Offset(0, 10).Value = b2thurbox2.Value 
            R6.Offset(0, 11).Value = b2fribox2.Value 
        End If 
    End With 
     
    Call tstoitsrev3 
    Application.ScreenUpdating = True 
     
End Sub 

Obiviously it isn't complete and will go all the way to R13 with If statements from R6 to R13. My question is, will it run faster running as is or would it be faster to develop some sort of loop? I was thinking something along the lines of:

x = 0 
While x < 10 
    R6.Value = b2monbox2.Value 
    ActiveCell.Offset(0, 1).Select 
    If x = 4 Then ' This is so data goes into proper column
        ActiveCell.Offset(0, 3).Select 
    End If 
    R6 = ActiveCell 
    x = x + 1 
Wend 

With this I would have to rename about 50 text boxes in order to develop some way to cycle through each text box and grab the data from each but if the code will run faster that's what I'll do. Of course if there is another method that's even faster that I'm oblivious to please point it out.

Thanks in advance for your help!

[fwabbas]

I'm not an expert on excel but usually in programming loops or recursions are not considered fast but they certainly save coding time.

it's my opinion but anyone can correct me if i'm worng.

[royUK]

You could store the destination in the TextBox's Tag Property then Llop through each control So in the Properties Window of each TextBox put the destination cell eg, A!, B1 etc. Then in a button

Dim Ctrl As Control 
For Each Ctrl In Me.Controls 
    If Ctrl.Tag > vbNullString Then 
        Range(Ctrl.Tag).Value = Ctrl.Value 
        Ctrl.Value = vbNullString 'oprional - clear TextBox
    End If 
Next Ctrl 

[norie]

What about constructing the names of the controls in code and then using the Controls collection?

I would post some code but I'm not quite sure exactly what you are trying to do and I always wonder why as many as 50 textboxes are needed on a userform.

Is there no other approach you could take?

[mEt]

I'm not real familair with utilizing the control fuctions you're refering to but I'll do a search on it and give it a go. At this point the code is still fast it just looks very messy with all those lines. Unfortunately all the text boxes are required because all the data asked for is a necessity. Believe it or not filling out 50 text boxes is actually speeding up a process.

[norie]

Even if all the data is neccessary it doesn't mean that you need a textbox for each piece of data.

Like I said it isn't clear from the code what you are actually doing.

Perhaps if you could explain that we could give you a few pointers.

I've actually already had a few thoughts but whether they would work really would depend on what you're doing.

[royUK]

Where are you writing the Data to? Is it a fixed Range.

To use my suggestion you need to put the destination into the Tag Property of the TextBoxes. Insert a Row - this is automatic, this means the latest entry is always at the top - you can always sort the data later

[mEt]

Yes, the data is just being written directly to a specific cell. All the data is are times and hours worked over the span of multiple weeks. The reason there are so many entries is because a person can be working on multiple jobs for varying amounts of time so each has to be specified. So I'm assuming inserting the cell name into the tag property box for the textbox on the userform is the fastest way of inserting the data? Not to mention it would clean up the code a lot.

[Aaron Blood]

You could store the destination in the TextBox's Tag Property then Llop through each control So in the Properties Window of each TextBox put the destination cell eg, A!, B1 etc. Then in a button

Dim Ctrl As Control 
For Each Ctrl In Me.Controls 
    If Ctrl.Tag > vbNullString Then 
        Range(Ctrl.Tag).Value = Ctrl.Value 
        Ctrl.Value = vbNullString 'oprional - clear TextBox
    End If 
Next Ctrl 

Yep... I second the notion. No additional coding mods required as new textboxes are added deleted either.