Is it possible to record multiple records from a single Userfowm

[6741bob]

I am working on creating a wellness and physical fitness dashboard and understand how I can create a Userform using VBA to add/edit a single record but am wondering if it is possible to add multiple records when entering groups of like data (Body Msmts, Hematology, Cardivascular, Chemistry, Lipids, Endocrine, etc.). My table has the following headings with some sample data:
Date Variable Value Units
1/27/21 Weight 90 kgs
1/27/21 Height 187 cms
1/27/21 Waist 90 cms
1/27/21 Hips 80 cms
1/27/21 Chest 250 cms
1/27/21 BP-Systolic 120 mm hg
1/27/21 BP-Diastolic 80 mm hg
Ideally, I would like a Userform (or set of Userforms for each typical data grouping) where I could input the date of the test/measurement at the top of the form and then have cells for each of the other three values, Variable, Value, Units that would look something like below
Body Measurements
Msmt. Date 1/27/2021
Variable Value Units
Weight 90 kgs
Height 187 cms
Waist 90 cms
Hips 80 cms
Chest 187 cms
BP-Systolic 120 mm hg
BP-Diastolic 80 mm hg

Is it possible or am I asking too much of Excel Userforms/vba?

 

[My Aswer Is This]

Trying to have Excel Build you a User Form and then the User form to perform multiple tasks requires a lot of very complicate Vba code.
Why not you build the User form and then write code to perform the task you want.

 

[6741bob]

Trying to have Excel Build you a User Form and then the User form to perform multiple tasks requires a lot of very complicate Vba code.
Why not you build the User form and then write code to perform the task you want.

Thanks for the quick response. Not sure I understand what you are proposing. I am looking to simplify input by adding multiple records with the same date on a single form. I know I can enter single records using a Userform but that is a lot of extra keying when records that I am keying share the same date.

 

[My Aswer Is This]

Ok.
I'm not sure I understand what you're wanting to do:
You said:
I am looking to simplify input by adding multiple records with the same date on a single form. I know I can enter single records using a Userform but that is a lot of extra keying when records that I am keying share the same date.

Define records
Define form

 

[6741bob]

Ok.
I'm not sure I understand what you're wanting to do:
You said:
I am looking to simplify input by adding multiple records with the same date on a single form. I know I can enter single records using a Userform but that is a lot of extra keying when records that I am keying share the same date.

Define records
Define form

Sorry if I my example above was not clear. In the first part of my question, I showed an example of my table with the following data fields along with some sample records (also see attached)
Date - Variable - Value - Units
In the second part of my questions, I tried to show in a textual format, an example of the Userform (Form) I have envisioned which had a field for a date that would be applied to all records on execution along with each of the Variable - Value - Units records associated with that date. See upgraded sample attached.
As I said, I know that I can enter records individually using a Userform but that is time consuming to be rekeying the date for every record when often I have 5-10 data records that all share the same date. I have done business design on enough software systems to know it can be done with code, just wanting to know if it is possible in Excel using a Userform.
Or maybe my question should be, "What is the most efficient way for a user to manually enter multiple records that all share the same date. (i.e. blood test results, body/fitness measurements etc.)
I appreciate any help you might be able to provide. If I am asking too much of a Userform in Excel, then that would be good to know too.

 

[dmt32]

Hi,
You can use For Next Loops to cycle through each Row / Column of your textboxes & read the textbox values into a 2D variant array
at each column cycle, add the date from your textbox to the first column. You can then output the array to your table.

Based on your userform image where you have three rows of textboxes & assuming that the textboxes all retain their default names and are organised as in image

then see if this code helps you

Private Sub CommandButton1_Click()
    Dim arr(1 To 3, 1 To 4) As Variant, myDate As Variant
    Dim r                   As Long, c As Long, box As Long
    Dim tbl                 As ListObject
    Dim NewRow              As ListRow
   
    myDate = Me.TextBox1.Value
    If IsDate(myDate) Then myDate = DateValue(myDate)
   
    box = 2
    For r = 1 To UBound(arr, xlRows)
        For c = 1 To UBound(arr, xlColumns)
            arr(r, c) = IIf(c = 1, myDate, Me.Controls("TextBox" & box).Value)
            If c > 1 Then box = box + 1
        Next c
    Next r
   
    'Add New row to the table
    Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    'insert new row
    Set NewRow = tbl.ListRows.Add(AlwaysInsert:=True)
    'resize table & enter array values
    NewRow.Range.Resize(UBound(arr, xlRows), UBound(arr, xlColumns)).Value = arr
   
End Sub

If you have more textbox Rows than shown in your form then increase the size of the array shown in BOLD
Also, change sheet & table names shown in BOLD as required

Hope Helpful

Dave

 

[6741bob]

Hi,
You can use For Next Loops to cycle through each Row / Column of your textboxes & read the textbox values into a 2D variant array
at each column cycle, add the date from your textbox to the first column. You can then output the array to your table.

Based on your userform image where you have three rows of textboxes & assuming that the textboxes all retain their default names and are organised as in image

View attachment 69138

then see if this code helps you

Private Sub CommandButton1_Click()
    Dim arr(1 To 3, 1 To 4) As Variant, myDate As Variant
    Dim r                   As Long, c As Long, box As Long
    Dim tbl                 As ListObject
    Dim NewRow              As ListRow
  
    myDate = Me.TextBox1.Value
    If IsDate(myDate) Then myDate = DateValue(myDate)
  
    box = 2
    For r = 1 To UBound(arr, xlRows)
        For c = 1 To UBound(arr, xlColumns)
            arr(r, c) = IIf(c = 1, myDate, Me.Controls("TextBox" & box).Value)
            If c > 1 Then box = box + 1
        Next c
    Next r
  
    'Add New row to the table
    Set tbl = ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1")
    'insert new row
    Set NewRow = tbl.ListRows.Add(AlwaysInsert:=True)
    'resize table & enter array values
    NewRow.Range.Resize(UBound(arr, xlRows), UBound(arr, xlColumns)).Value = arr
  
End Sub

If you have more textbox Rows than shown in your form then increase the size of the array shown in BOLD
Also, change sheet & table names shown in BOLD as required

Hope Helpful

Dave

Thanks Dave, good to know it is possible. This will be a steep learning curve on vba for me to be sure. I'll get to work on a basic test of this in the next week or so and let you know how it goes. I greatly appreciate you sharing your knowledge.

Cheers,
Bob

 

[dmt32]

Thanks Dave, good to know it is possible. This will be a steep learning curve on vba for me to be sure. I'll get to work on a basic test of this in the next week or so and let you know how it goes. I greatly appreciate you sharing your knowledge.

Cheers,
Bob

welcome - hope suggestions helps & appreciate your feedback

Dave