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

[szchris384]

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

[Excel Fox]

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.

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

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

[szchris384]

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