Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: UserForm insert data to SpreadSheet- Whats Faster?

  1. #1
    Join Date
    2nd May 2006
    Posts
    16

    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:

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

    VB:
    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 at 03:21.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th April 2006
    Location
    Canada
    Posts
    13

    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 at 03:41.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,897

    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

    VB:
    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 at 04:32.
    Hope that Helps

    Roy

    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.

  4. #4
    Join Date
    14th July 2004
    Posts
    10,539

    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 at 05:23.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    2nd May 2006
    Posts
    16

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th July 2004
    Posts
    10,539

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    17,897

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by royUK; May 9th, 2006 at 03:41.
    Hope that Helps

    Roy

    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.

  8. #8
    Join Date
    2nd May 2006
    Posts
    16

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    Re: UserForm insert data to SpreadSht- Whats Faster?

    Quote 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

    VB:
    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)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Insert Data From UserForm Corresponding To Date
    By aslamw2 in forum EXCEL HELP
    Replies: 1
    Last Post: August 16th, 2008, 07:38
  2. Insert data into spreadsheet
    By portucale in forum EXCEL HELP
    Replies: 1
    Last Post: April 17th, 2007, 17:15
  3. Replies: 2
    Last Post: January 13th, 2006, 10:53
  4. Entering Data into Spreadsheet using a Userform
    By csbinion in forum EXCEL HELP
    Replies: 6
    Last Post: January 7th, 2006, 04:54
  5. Insert Data from UserForm
    By thompssm in forum EXCEL HELP
    Replies: 3
    Last Post: December 17th, 2005, 16:10

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno