VBA to Populate an array with values from multiple Textboxes then outputting array to range in worksheet

[Flexcel22]

Hi,

I'm very new with Array in vba and find it hard to adapt the tutorials I find on webs to my task.

I understand that array can store variables for later use.

I want to populate a multi column array only with enabled textboxes values. I have 40 textboxes in frame1. If I check checkbox1 outside frame1 Textboxes 1, 2, 3 and 4 are enabled. Checking checkbox2 enabled textboxes 5, 6, 7 and 8. Checkbox3, Textboxes 9, 10, 11, 12 until textbox40. There are 10 checkboxes outside frame1. Each corresponded to every 4 textboxes.

It's like the columns are fixed (4 columns) in an array and the rows are to be dynamically increase or increment depending on the checkboxes.

By a button click, the stored array are to be transferred ( emptying array) to a worksheet range as is.

Can anybody help me out!

Thanks very much in advance and looking forward for any tips or solutions.

 

[DanteAmor]

You can pass the texbox data directly to the sheet, but with the following you fill an array and then pass the array to the sheet.
Controls should be called "CheckBox" from 1 to 10 and "TextBox" from 1 to 40.

Private Sub CommandButton1_Click()
  Dim arr As Variant, i As Long, j As Long
  Dim k As Long, m As Long, n As Long
  ReDim arr(1 To 10, 1 To 4)
  
  n = 1
  For i = 1 To 10
    If Controls("CheckBox" & i) Then
      k = k + 1
      m = 0
      For j = n To n + 3
        m = m + 1
        arr(k, m) = Controls("TextBox" & j)
      Next
    End If
    n = n + 4
  Next
  
  Range("A2").Resize(k, 4).Value = arr
End Sub

 

[Flexcel22]

Thanks DanteAmor.

I will revert to you as soon as I execute your code in my task but would appreciate if you could kindly comment each line of code so I can comprehend and at the same time learning what the code does.

Thanks

 

[DanteAmor]

how about

Private Sub CommandButton1_Click()
  Dim arr As Variant, i As Long, j As Long
  Dim k As Long, m As Long, n As Long
  ReDim arr(1 To 10, 1 To 4)
  
  n = 1
  
  For i = 1 To 10
    'Cycles from 1 to 10 to check if the checkbox is selected.
    If Controls("CheckBox" & i) Then
      k = k + 1
      m = 0
      '4 times cycle to check 4 textboxes, first from 1 to 4,
      'in another cycle check from 5 to 8 and so on.
      For j = n To n + 3
        m = m + 1
        'stores the value of the textbox in the array
        arr(k, m) = Controls("TextBox" & j)
      Next
    End If
    'Increase the variable n by 4 for the following 4 textboxes
    n = n + 4
  Next
  'Unload the array on the sheet, resize the output in k rows and 4 columns.
  'k has the number of rows that had the selected checkbox.
  Range("A2").Resize(k, 4).Value = arr
End Sub

 

[Flexcel22]

Fabulously your code works as desired DanteAmor!

The comments are vividly complete the whole picture of what your code does. I will continue to study and practice how the cycle works.

Thank you DanteAmor for your help. You have opened up a new door for me.

God bless you always

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[Flexcel22]

Hello Again DanteAmor,

I know I could post or raise another thread but I think this one is related.

What if I want to store my array somewhere for later use and from another sub, redim preserved it content while add more rooms for 2 more columns, fill up those columns then finally output the result in a range.

I am referring to the example I above.

Thank you again in advance

 

[DanteAmor]

What if I want to store my array somewhere

The data and controls of the userform are already in memory, I don't think it is necessary to store the results in another section of memory, and then go to that other section and "manipulate", that is, you already have the data in the controls, you just have to apply the routine you already have to recover the data.

 

[Flexcel22]

Thanks DanteAmor,

Sorry for messed question. I now understand that array had been stored in memory and since I declared publicly, I am able to reference it from another sub.

However in my other sub I wanted to redim and preserve the existed array and add more columns and refill a newly created elements visible controls(e.g combobox.count or something like that)

How can I go about to do it?

Thanks in advance