Userform data arrays

Nikijune

Board Regular
Joined
Aug 16, 2016
Messages
51
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>
Code:
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>
Code:
'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>
Code:
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">
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Can someone tell me if my question at least makes sense? Or am I asking for something a bit too crazy??
 
Upvote 0
Maybe This ???
Results start "A1"
Code:
[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
 
Last edited:
Upvote 0
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;

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top