Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

UserForm insert data to SpreadSheet- Whats Faster?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • UserForm insert data to SpreadSheet- Whats Faster?

    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:

    Code:
    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:

    Code:
    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!
    Last edited by mEt; May 6th, 2006, 03:21.

  • #2
    Re: UserForm insert data to SpreadSht- Whats Faster?

    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.
    Last edited by fwabbas; May 6th, 2006, 03:41.

    Comment


    • #3
      Re: UserForm insert data to SpreadSht- Whats Faster?

      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

      Code:
      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
      Last edited by royUK; May 6th, 2006, 04:32.
      Hope that Helps

      Roy

      New users should read the Forum Rules before posting

      For free Excel tools & articles visit my web site

      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

      RoyUK's Web Site

      royUK's Database Form

      Where to paste code from the Forum

      About me.

      Comment


      • #4
        Re: UserForm insert data to SpreadSht- Whats Faster?

        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?
        Last edited by norie; May 6th, 2006, 05:23.
        Boo!

        Comment


        • #5
          Re: UserForm insert data to SpreadSht- Whats Faster?

          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.

          Comment


          • #6
            Re: UserForm insert data to SpreadSht- Whats Faster?

            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.
            Boo!

            Comment


            • #7
              Re: UserForm insert data to SpreadSht- Whats Faster?

              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
              Attached Files
              Last edited by royUK; May 9th, 2006, 03:41.
              Hope that Helps

              Roy

              New users should read the Forum Rules before posting

              For free Excel tools & articles visit my web site

              If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

              RoyUK's Web Site

              royUK's Database Form

              Where to paste code from the Forum

              About me.

              Comment


              • #8
                Re: UserForm insert data to SpreadSht- Whats Faster?

                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.

                Comment


                • #9
                  Re: UserForm insert data to SpreadSht- Whats Faster?

                  Originally posted by 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

                  Code:
                  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.
                  Sub All_Macros(Optional control As Variant)

                  Comment

                  Trending

                  Collapse

                  There are no results that meet this criteria.

                  Working...
                  X