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/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.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (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.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.9BLeCWVhxdG9wgNPOdiDuv)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg (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=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg (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=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg.9C-br0lEl8V9xI0_6pCaR9 (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=Ugz5DDCMqmHLeEjUU8t4AaABAg.9bl7m03Onql9xI-ar3Z0ME)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg.9gdrYDocLIm9xI-2ZpVF-q)
https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I (https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg.9id_Q3FO8Lp9xHyeYSuv 1I)
https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3 (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=UgxYgiEZuS9I3xkjJv54AaABAg)
https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M (https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm9wlhQrYJP 3M)
ttps://www.youtube.com/watch?v=LP9fz2DCMBE (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)
https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8 (https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg.9wdo_rWgxSH9wdpcYqrv p8)
ttps://www.youtube.com/watch?v=bFxnXH4-L1A (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=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG)
https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg (https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg)
ttps://www.youtube.com/watch?v=GqzeFYWjTxI (ttps://www.youtube.com/watch?v=GqzeFYWjTxI)
https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg (https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (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).Contro ls(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).Contro ls(lngCount)) = "TextBox" Then
lngCounter = lngCounter + 1
Cells(2, lngCounter).Value = Me.MultiPage1.Pages(lngMultiPages).Controls(lngCou nt).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.
1406