none
Loop through excel UserForm text/combo box and write to worksheet

    Question

  • Hello All,

      I have a userform where I collect some information and I may add or remove fields in the future and I am looking for a way to write the content of the text/combo boxes to the worksheet in tab index order.  I have made several attempt to search the internet and find someone that has asked the same question and I found a passing reference to making a class and collection then sort the collections in tab index order then write to the worksheet.  I have tried to read up on classes and collection, but it is a little over my head.

     My next thought was to write the contents of the text/combo boxes to and array and sort the array, but again that is a little above my coding skills.  Most of what I have done I have found on various websites and have pieced together my spreadsheet.  Can someone show me or explain a way of writing userform contents, in tab index order, to a worksheet?  I am surprised that the userform objects do not take on there tab index order but instead take on the order of when they were added to the form.

    Thanks,

    Harry

     Here is the code I currently use:

    Dim NumOfControls As Long
    Dim cCont As Control
        For Each cCont In Me.Controls
            If TypeName(cCont) = "TextBox" Or TypeName(cCont) = "ComboBox" Then
                lCount = lCount + 1
            End If
        Next cCont
        NumOfControls = lCount

    Dim ctlCont As Control
        For i = 1 To NumOfControls
            For Each ctlCont In Me.Controls
                If TypeName(ctlCont) = "TextBox" Or TypeName(ctlCont) = "ComboBox" _
                Then
                ActiveCell.Offset(0, i) = ctlCont.Value
                i = i + 1
                End If
            Next ctlCont
        Next i

    Sunday, October 16, 2011 8:52 PM

Answers

  • Hello Harry,

    Code has been extensively edited since original post.

    The code adds the required controls and their TabIndex to a 2 dimensional array.

    It then sorts the array on the TabIndex and then copies the values only to a single dimensional array.

    The single dimensional array is then copied to the worksheet.

    Rather than use Option Base 1 to force arrays to start counting from 1 instead of zero I have dimensioned the arrays using the method that starts them at 1 instead of the default.

    Where the array is copied to the worksheet, it is necessary to transpose the array if the contents are to go down rows. No transpose if across the columns.

    For testing purposes I just ran it from a Command Button.

    Private Sub CommandButton1_Click()

    Dim ctrl As Control
    Dim i As Long
    Dim j As Long
    Dim temp1 As Variant
    Dim temp2 As Variant
    Dim arrCtrls()
    Dim arrFinal()

      For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Or _
            TypeName(ctrl) = "ComboBox" Then
           
          i = i + 1
         
          'With Redim Preserve can only change the last dimension
          ReDim Preserve arrCtrls(1 To 2, 1 To i)
          arrCtrls(1, i) = ctrl.TabIndex  'In 1st dimension of array
          arrCtrls(2, i) = ctrl.Value     'In 2nd dimension of array
        End If
      Next ctrl
     
      'Sort array by Tab Index which is in first dimension of array
      For i = 1 To UBound(arrCtrls, 2)  'Max element 2nd dimension
        For j = 1 To UBound(arrCtrls, 2) - 1
          If arrCtrls(1, j) > arrCtrls(1, j + 1) Then
            temp1 = arrCtrls(1, j)
            temp2 = arrCtrls(2, j)
            arrCtrls(1, j) = arrCtrls(1, j + 1)
            arrCtrls(2, j) = arrCtrls(2, j + 1)
            arrCtrls(1, j + 1) = temp1
            arrCtrls(2, j + 1) = temp2
          End If
        Next j
      Next i
     
      'Create single dimension array with only control values
      ReDim arrFinal(1 To UBound(arrCtrls, 2))
     
      For i = 1 To UBound(arrCtrls, 2)
        'Note that control values are in the 2nd dimension of arrCtrls
        arrFinal(i) = arrCtrls(2, i)
      Next i
      
      'Following resizes one cell to size of array
      'and copies contents of array to rows of worksheet.
      'Edit "F1" to first cell of destination
      ActiveSheet.Range("F1").Resize(UBound(arrFinal), 1) _
            = WorksheetFunction.Transpose(arrFinal)
           
      '*********************************************************
      'Between asterisk lines information only.
     
      'Copies the array across the columns (No transpose)
      'Edit "J1" to first cell of destination
      'ActiveSheet.Range("J1").Resize(1, UBound(arrFinal)) = arrFinal
     
      'Following copies entire 2 dimensional array to worksheet
      'Note Resize uses UBound of both dimensions
      'ActiveSheet.Range("J1") _
            .Resize(UBound(arrCtrls, 2), UBound(arrCtrls, 1)) _
            = WorksheetFunction.Transpose(arrCtrls)
      '***********************************************************
    End Sub


    Regards, OssieMac
    • Edited by OssieMac Monday, October 17, 2011 2:20 AM
    • Marked as answer by Harry Stevens Wednesday, October 19, 2011 12:04 AM
    Sunday, October 16, 2011 11:50 PM

All replies

  • Hello Harry,

    Code has been extensively edited since original post.

    The code adds the required controls and their TabIndex to a 2 dimensional array.

    It then sorts the array on the TabIndex and then copies the values only to a single dimensional array.

    The single dimensional array is then copied to the worksheet.

    Rather than use Option Base 1 to force arrays to start counting from 1 instead of zero I have dimensioned the arrays using the method that starts them at 1 instead of the default.

    Where the array is copied to the worksheet, it is necessary to transpose the array if the contents are to go down rows. No transpose if across the columns.

    For testing purposes I just ran it from a Command Button.

    Private Sub CommandButton1_Click()

    Dim ctrl As Control
    Dim i As Long
    Dim j As Long
    Dim temp1 As Variant
    Dim temp2 As Variant
    Dim arrCtrls()
    Dim arrFinal()

      For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Or _
            TypeName(ctrl) = "ComboBox" Then
           
          i = i + 1
         
          'With Redim Preserve can only change the last dimension
          ReDim Preserve arrCtrls(1 To 2, 1 To i)
          arrCtrls(1, i) = ctrl.TabIndex  'In 1st dimension of array
          arrCtrls(2, i) = ctrl.Value     'In 2nd dimension of array
        End If
      Next ctrl
     
      'Sort array by Tab Index which is in first dimension of array
      For i = 1 To UBound(arrCtrls, 2)  'Max element 2nd dimension
        For j = 1 To UBound(arrCtrls, 2) - 1
          If arrCtrls(1, j) > arrCtrls(1, j + 1) Then
            temp1 = arrCtrls(1, j)
            temp2 = arrCtrls(2, j)
            arrCtrls(1, j) = arrCtrls(1, j + 1)
            arrCtrls(2, j) = arrCtrls(2, j + 1)
            arrCtrls(1, j + 1) = temp1
            arrCtrls(2, j + 1) = temp2
          End If
        Next j
      Next i
     
      'Create single dimension array with only control values
      ReDim arrFinal(1 To UBound(arrCtrls, 2))
     
      For i = 1 To UBound(arrCtrls, 2)
        'Note that control values are in the 2nd dimension of arrCtrls
        arrFinal(i) = arrCtrls(2, i)
      Next i
      
      'Following resizes one cell to size of array
      'and copies contents of array to rows of worksheet.
      'Edit "F1" to first cell of destination
      ActiveSheet.Range("F1").Resize(UBound(arrFinal), 1) _
            = WorksheetFunction.Transpose(arrFinal)
           
      '*********************************************************
      'Between asterisk lines information only.
     
      'Copies the array across the columns (No transpose)
      'Edit "J1" to first cell of destination
      'ActiveSheet.Range("J1").Resize(1, UBound(arrFinal)) = arrFinal
     
      'Following copies entire 2 dimensional array to worksheet
      'Note Resize uses UBound of both dimensions
      'ActiveSheet.Range("J1") _
            .Resize(UBound(arrCtrls, 2), UBound(arrCtrls, 1)) _
            = WorksheetFunction.Transpose(arrCtrls)
      '***********************************************************
    End Sub


    Regards, OssieMac
    • Edited by OssieMac Monday, October 17, 2011 2:20 AM
    • Marked as answer by Harry Stevens Wednesday, October 19, 2011 12:04 AM
    Sunday, October 16, 2011 11:50 PM
  • Hi again Harry,

    I have extensively edited the code that I originally posted. I thought that an addtional post so that you get a notification in case you are already trying to use the original code that has some problems.

    Hope that I have not caused you too many problems.


    Regards, OssieMac
    Monday, October 17, 2011 2:24 AM
  • OssieMac,

      Thanks for the code!  I am trying to understand it.  I forgot to mention in my first post that the userform contains 3 frames that contain some of the tabbed fields.  I did not think it would matter since I was looking for tab order, but I think it must because the data written to the active sheet is not in tab index order.  I am trying to figure out the order your code writes to the active sheet to see if there is a pattern.

    Thanks

    Harry

    Monday, October 17, 2011 5:04 PM
  •  I am trying to figure out the order your code writes to the active sheet to see if there is a pattern.

    The code sorts the 2 dimensional array on the TabIndex. It then creates a single dimensional array with just the control values and they are kept in the same order as the sort. The single dimensional array is then written to the worksheet with the following line of code. (You should have edited edit "F1" to the first cell of where you want it.

    ActiveSheet.Range("F1").Resize(UBound(arrFinal), 1) _
            = WorksheetFunction.Transpose(arrFinal)

    If for testing purposes you want to see the TabIndexes listed beside the values to ensure they are sorted on the tab indexes, then use the code below (Which was the last line between the asterisk lines and commented out in my previous post). The code copies the 2 dimensional array with both the TabIndexes and the values to the worksheet.

    You will need to edit "J1" to a cell that is out of the way of your main worksheet data.

    The first column will be the TabIndexes and the second column the control values.

    No need to remove any of the other code; just allow the following line to run at the end of the code.

    ActiveSheet.Range("J1") _
            .Resize(UBound(arrCtrls, 2), UBound(arrCtrls, 1)) _
            = WorksheetFunction.Transpose(arrCtrls)


    Regards, OssieMac
    Monday, October 17, 2011 7:54 PM
  • OssieMac,

      Once I modified my form and did away with the frames, your script worked as designed!  Thanks!  With the frames the loops output started at frame 1 tab 1, then frame 2 tab 1, frame 3 tab 1, main form tab1, then it went from main form tab 2 to frame 1 tab 2, frame 2 tab 2, frame 3 tab 2, and then repeated this order.

    Once I did away with the frames and put all fields on the main form and adjusted all fields tab positions your code worked like a charm.

    Best Regards,

    Harry

    Wednesday, October 19, 2011 12:07 AM