Userform data arrays

[Nikijune]

Hello Excel Friends,<br>
<br>
I have a user form that currently publishes the data to a database sheet in one row, however, what I need it to do is publish the data vertically instead of horizontally, but I need 2 of the form sources to be replicated in each row.<br>
<br>
My current user form control has one array. Code below;<br>
<br>

Function FormControls() As Variant<br>
    FormControls = Array("DateTextBox", "ComboBox1", "Refunds", "Refunds_Date", "WriteOffs", "WriteOffs_Date", "Cancellations", _<br>
    "Cancellations_Date", "Modifications", "Modifications_Date", "Indemnities", "Indemnities_Date", "Clearances", "Clearances_Date", _<br>
    "PartialPaymentsNew", "PartialPaymentsNew_Date", "PartialPaymentsIp", "PartialPaymentsIp_Date", "RightOfWithdrawalSpread", _<br>
    "RightOfWithdrawalSpread_Date", "RightOfWithdrawalCRM", "RightOfWithdrawalCRM_Date", "RefundExceptions", "RefundExceptions_Date", _<br>
    "PSOreport", "PSOreport_Date", "CSD", "CSD_Date", "RefundFixes", "RefundFixes_Date", "RefundsCRM", "RefundsCRM_Date", "BankReport", _<br>
    "BankReport_Date", "DeletionReport", "DeletionReport_Date")<br>
<br>
End Function

<br>
<br>
I then have the below code that calls the form controls;<br>
<br>
<br>

'references Form Controls macro with tasks detailed<br>
        ReDim data(1 To UBound(FormControls))<br>
'build array<br>
        For i = 1 To UBound(FormControls)<br>
            With Form.Controls(FormControls(i))

<br>
<br>
I am trying to work out how to get the data to publish in separate rows but with the data and combobox1 fields in every row.<br>
<br>
So I will end up with 5 headers;<br>
<br>
Date Name Task Quantity completed Oldest date worked<br>
<br>
I think I want something like this;<br>
<br>
<br>

Function FormControls() As Variant<br>
    FormControls = Array 1("DateTextBox", "ComboBox1", "Refunds", "Refunds_Date")<br>
Array 2("DateTextBox", "ComboBox1", "WriteOffs", "WriteOffs_Date")<br>
Array 3("DateTextBox", "ComboBox1", "Cancellations", "Cancellations_Date")<br>
Array 4("DateTextBox", "ComboBox1", "Modifications", "Modifications_Date")<br>
Array 5("DateTextBox", "ComboBox1",  "Indemnities", "Indemnities_Date")<br>
Array 6("DateTextBox", "ComboBox1", "Clearances", "Clearances_Date")<br>
Array 7("DateTextBox", "ComboBox1", "PartialPaymentsNew", "PartialPaymentsNew_Date")<br>
Array8("DateTextBox", "ComboBox1", "PartialPaymentsIp", "PartialPaymentsIp_Date")<br>
Array 9("DateTextBox", "ComboBox1", "RightOfWithdrawalSpread", "RightOfWithdrawalSpread_Date")<br>
Array10("DateTextBox", "ComboBox1", "RightOfWithdrawalCRM", "RightOfWithdrawalCRM_Date")<br>
Array11("DateTextBox", "ComboBox1", "RefundExceptions", "RefundExceptions_Date")<br>
Array12("DateTextBox", "ComboBox1", "PSOreport", "PSOreport_Date")<br>
Array13("DateTextBox", "ComboBox1", "CSD", "CSD_Date")<br>
Array14("DateTextBox", "ComboBox1", "RefundFixes", "RefundFixes_Date")<br>
Array15("DateTextBox", "ComboBox1", "RefundsCRM", "RefundsCRM_Date")<br>
Array16("DateTextBox", "ComboBox1", "BankReport", "BankReport_Date")<br>
Array17("DateTextBox", "ComboBox1", "DeletionReport", "DeletionReport_Date")<br>
<br>
End Function

<br>
<br>
I am not really sure if I need to do this to the form controls or if I need to amend the code that calls the form controls and get that to do something different.<br>
<br>
Any help is greatly appreciated. This is the final piece to my jigsaw.....<br>
<br><br type="_moz">

 

[Nikijune]

Anyone?

 

[Nikijune]

Can someone tell me if my question at least makes sense? Or am I asking for something a bit too crazy??

 

[MickG]

Maybe This ???
Results start "A1"

[COLOR="Navy"]Sub[/COLOR] MG07Nov05
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Data() [COLOR="Navy"]As[/COLOR] Variant
        
      [COLOR="Navy"]For[/COLOR] i = 2 To UBound(FormControls) [COLOR="Navy"]Step[/COLOR] 2
           c = c + 1
           ReDim Preserve Data(1 To 4, 1 To c)
           Data(1, c) = FormControls(0)
           Data(2, c) = FormControls(1)
           Data(3, c) = FormControls(i)
           Data(4, c) = FormControls(i + 1)
        [COLOR="Navy"]Next[/COLOR] i

[COLOR="Navy"]With[/COLOR] Range("A1").Resize(c, 4)
    .Value = Application.Transpose(Data)
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Function FormControls() [COLOR="Navy"]As[/COLOR] Variant
    FormControls = Array("DateTextBox", "ComboBox1", "Refunds", "Refunds_Date", "WriteOffs", "WriteOffs_Date", "Cancellations", _
    "Cancellations_Date", "Modifications", "Modifications_Date", "Indemnities", "Indemnities_Date", "Clearances", "Clearances_Date", _
    "PartialPaymentsNew", "PartialPaymentsNew_Date", "PartialPaymentsIp", "PartialPaymentsIp_Date", "RightOfWithdrawalSpread", _
    "RightOfWithdrawalSpread_Date", "RightOfWithdrawalCRM", "RightOfWithdrawalCRM_Date", "RefundExceptions", "RefundExceptions_Date", _
    "PSOreport", "PSOreport_Date", "CSD", "CSD_Date", "RefundFixes", "RefundFixes_Date", "RefundsCRM", "RefundsCRM_Date", "BankReport", _
    "BankReport_Date", "DeletionReport", "DeletionReport_Date")

[COLOR="Navy"]End[/COLOR] Function

Regards Mick

 

[Nikijune]

Hi Mick,

Thank you for coming back to me

I have tried slotting your suggestion into my code, but Its not quite working. Below is the full code to add to the database. I've tried taking out the bit that references the form controls but it keeps falling over;

Sub DatabaseAdd(ByVal Form As Object)
Dim wbDatabase As Workbook
    Dim FileName As String, UsersName As String
    Dim Data() As Variant
    Dim Lastrow As Long
    Dim i As Integer
    
'References cells that have filepath in and agents DKX Number
    FileName = Settings.Range("D17").Value
    UsersName = Settings.Range("D24").Value
    
    On Error GoTo ExitSub
    
'references Form Controls macro with tasks detailed
        ReDim Data(1 To UBound(FormControls))
'build array
        For i = 1 To UBound(FormControls)
            With Form.Controls(FormControls(i))
'check required field
            If IsRequired(Form, .Name) Then Exit Sub
                If IsDate(.Text) Then
                    Data(i) = DateValue(.Text)
                Else
                    Data(i) = .Text
                End If
            End With
        Next i
            
            Application.ScreenUpdating = False
'open database
            Set wbDatabase = DatabaseOpen(FileName:=FileName, ReadOnly:=False)
            If wbDatabase Is Nothing Then GoTo ExitSub
            
            With wbDatabase
                With .Sheets(1)
'get next row
                    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
'place array to range
                    .Cells(Lastrow, 1).Resize(1, UBound(Data)).Value = Data
'format record
                DatabaseFormat Target:=.Cells(Lastrow, 1).Resize(1, UBound(Data))
                End With
'sort record
                DatabaseSort sh:=.Sheets(1), SortDirection:=xlDescending
'close file & save
                .Close True
            End With
'clear from memory
        Set wbDatabase = Nothing
'clear form
        ClearForm Form
 'report success
        MsgBox UsersName & Chr(10) & Chr(10) & "New Record Added To Database", 48, "New Record Added"

ExitSub:
'close database if open
If Not wbDatabase Is Nothing Then wbDatabase.Close False
Application.ScreenUpdating = True
'tell user what went wrong
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub