Results 1 to 3 of 3

Thread: How To Save Data To SpreadSheet From Only TextBoxes In All MultiPages Of A User Form

  1. #1
    Junior Member
    Join Date
    Oct 2013
    Posts
    2
    Rep Power
    0

    Lightbulb How To Save Data To SpreadSheet From Only TextBoxes In All MultiPages Of A User Form

    I've created a userform with several multipage controls on it. Inside the multipage controls are lots of other controls. Probably have about 100 controls total from txtBoxes, cboBoxes to chkBoxes.

    It's a form that is used once, then saved as (user inputted file name), so the data resides on row 1 only. Note: once saved, it will be open by another user for review and editing.

    I know how to code all 100 controls to save to a row manually but this is very time consuming and if the userform changes, reviewing the saving and loading routine could be daunting.

    Is there a way to loop through all the controls and only save the DATA controls (not Label, Frame or Button Controls) to the next column on Row1.

    If so, what order would they be saved in? Tab order?
    Ie 10 controls (in tab order) in page 1 of multipage first, then 12 controls (in tab order) in page 2 of multipage, etc.
    Or would it be an Alphanumeric order? Or custom order. Ideally by Custom if possible, but Tab order would suffice.

    Thanks for any help.
    Chris

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-20-2023 at 03:39 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Happy new year szchris384. And welcome to ExcelFox

    If you use a for each loop, the loop order will take it by tab order. However, to keep things more in control, it would be better to give specific names to your controls. Like txtMP1Data1, txtMP1Data2, txtMP1Data3, txtMP2Data1, txtMP2Data2, txtMP2Data3, txtMP3Data1 etc

    That way, you can do a loop like this.

    Code:
    Sub GetDataFromAllTextBoxesByName()
    
        Dim lngMultiPages   As Long
        Dim lngCount        As Long
        Dim lngCounter      As Long
        Dim lngColIndex     As Long
        For lngMultiPages = 0 To Me.MultiPage1.Pages.Count - 1
            For lngCount = 0 To Me.MultiPage1.Pages(lngMultiPages).Controls.Count - 1
                If TypeName(Me.MultiPage1.Pages(lngMultiPages).Controls(lngCount)) = "TextBox" Then
                    lngCounter = lngCounter + 1
                End If
            Next lngCount
            For lngCounter = 0 To lngCounter - 1
                lngColIndex = lngColIndex + 1
                Cells(3, lngColIndex).Value = Me.MultiPage1.Pages(lngMultiPages).Controls("txtMP" & lngMultiPages & "Data" & lngCounter).Text
            Next lngCounter
            lngCounter = 0
        Next lngMultiPages
        
    End Sub
    The other way of course is to go with the tab order

    Code:
    Sub GetDataFromAllTextBoxesByTabOrder()
    
        Dim lngMultiPages   As Long
        Dim lngCount        As Long
        Dim lngCounter      As Long
        
        For lngMultiPages = 0 To Me.MultiPage1.Pages.Count - 1
            For lngCount = 0 To Me.MultiPage1.Pages(lngMultiPages).Controls.Count - 1
                If TypeName(Me.MultiPage1.Pages(lngMultiPages).Controls(lngCount)) = "TextBox" Then
                    lngCounter = lngCounter + 1
                    Cells(2, lngCounter).Value = Me.MultiPage1.Pages(lngMultiPages).Controls(lngCount).Text
                End If
            Next lngCount
        Next lngMultiPages
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Oct 2013
    Posts
    2
    Rep Power
    0
    Thanks for the quick reply and the sample code. I've been playing with your sample code.

    I've opted to use the Tab order code, but it's doesn't seem to save in the spreadsheet columns in tabindex order?
    Not certain, but looks like it's saving in the order the controls were put on the userform.
    Not the physical order of the controls on the userform but the time the control was created and added to the userform.

    If I take your userform and add another textbox and give it a tabindex of 0, change the tabindex order of the original 2 textboxes to 1, 2 respectfully, it saves the new textbox that I added to the 3rd column of the spreadsheet, not the 1st like you would expect, based on the tabindex value.

    Don't know if that's normal, or if it's supposed to follow the tabindex value of the control?
    Chris

    Your sample code modified with new TextBox control added with tabindex=0.
    Get Data From All TextBoxes In All MultiPages Of A User Form.xls

Similar Threads

  1. Inserting Image In VBA User Form Caption
    By littleiitin in forum Download Center
    Replies: 3
    Last Post: 02-22-2021, 03:07 PM
  2. Replies: 11
    Last Post: 10-13-2013, 10:53 PM
  3. Sending Data From User Form To First Empty Row Of Sheets
    By paul_pearson in forum Excel Help
    Replies: 21
    Last Post: 08-14-2013, 11:04 PM
  4. Populate data in form
    By Ryan_Bernal in forum Excel Help
    Replies: 4
    Last Post: 02-01-2013, 10:18 AM
  5. Adding A Menu Bar To A User Form
    By Rasm in forum Excel Help
    Replies: 14
    Last Post: 05-05-2011, 04:05 AM

Tags for this Thread

Posting Permissions

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