I have a userform to gather data. I need a way to read the data and
write it to a worksheet. The form has seven identical rows of combo
and text boxes to accommodate up to seven records at a time. In
practice, most entries will be only one record, but two or three are
very common. There are also three combo boxes at the top of the form
for “header” data, fields that would be the same for each record for
any person each day. I’ve numbered the controls in each row in a way
so that the first record would consist of Header01, Header02, Header
03, FirstField01, SecondField01, ThirdField01, FourthField01. If there
is data for a second record it would consist of Header01, Header02,
Header 03, FirstField02, SecondField02, ThirdField02, FourthField02,
etc.
My idea was to declare seven arrays of 7 elements each but use only as
many as are needed at any time, only if all controls in a row are
filled in. Can anyone help me get started with this?

Hi there

Probably no one replied to you as it seems unsure if you need VBA knowledge
from scratch. You can do all you want by using VBA. However this forum is not
for learning vba but to review specific problems. There is a RowSource
Property for each field but I suggest you check the help on the following:
activesheet.cells(1,1).value=Userform1.Header01.te xt

Maybe this is not exactly what you want but if you specify your problem more
detailed and what you already programmed, this makes it easier to help.

Regards,
Alex
http://www.excel-support.ch


"Slim Slender" wrote:

I have a userform to gather data. I need a way to read the data and
write it to a worksheet. The form has seven identical rows of combo
and text boxes to accommodate up to seven records at a time. In
practice, most entries will be only one record, but two or three are
very common. There are also three combo boxes at the top of the form
for €œheader€� data, fields that would be the same for each record for
any person each day. Ive numbered the controls in each row in a way
so that the first record would consist of Header01, Header02, Header
03, FirstField01, SecondField01, ThirdField01, FourthField01. If there
is data for a second record it would consist of Header01, Header02,
Header 03, FirstField02, SecondField02, ThirdField02, FourthField02,
etc.
My idea was to declare seven arrays of 7 elements each but use only as
many as are needed at any time, only if all controls in a row are
filled in. Can anyone help me get started with this?
.

On Feb 21, 7:32*am, Alex wrote:
Hi there

Probably no one replied to you as it seems unsure if you need VBA knowledge
from scratch. You can do all you want by using VBA. However this forum is not
for learning vba but to review specific problems. There is a RowSource
Property for each field but I suggest you check the help on the following:
activesheet.cells(1,1).value=Userform1.Header01.te xt

Maybe this is not exactly what you want but if you specify your problem more
detailed and what you already programmed, this makes it easier to help.

Regards,
Alexhttp://www.excel-support.ch



"Slim Slender" wrote:
I have a userform to gather data. I need a way toreadthe data and
write it to a worksheet. Theformhas seven identical rows of combo
and text boxes to accommodate up to seven records at a time. In
practice, most entries will be only one record, but two or three are
very common. There are also three combo boxes at the top of theform
for “header” data, fields that would be the same for each record for
any person each day. I’ve numbered the controls in each row in a way
so that the first record would consist of Header01, Header02, Header
03, FirstField01, SecondField01, ThirdField01, FourthField01. If there
is data for a second record it would consist of Header01, Header02,
Header 03, FirstField02, SecondField02, ThirdField02, FourthField02,
etc.
My idea was to declare seven arrays of 7 elements each but use only as
many as are needed at any time, only if all controls in a row are
filled in. Can anyone help me get started with this?
.- Hide quoted text -

- Show quoted text -

Okay, Alex, are you in Switzerland? Hey, how about that Ammann? Skinny
dude can ski, eh?
Well, here's my go at it. It works but I know it could be a lot more
'elegant' like with a loop or two
instead of repeating so much code.

Public Sub WriteData()
Dim i As Integer
Dim StartHere As Integer

Dim myArray01 As Variant
Dim myArray02 As Variant
Dim myArray03 As Variant
Dim myArray04 As Variant
'
myArray01 = Array(txtReportingDate.Value, cmbTeammate.Text,
txtHomeTeam.Text, cmbTask01.Text, _
cmbDepartment01.Text, cmbTimeType01.Text, txtUnits01.Value,
txtHours01.Value, txtComment01.Text)
'
If cmbTask02.Text < "" And cmbDepartment02.Text < "" And
cmbTimeType02.Text < "" And _
txtUnits02.Value < "" And txtHours02.Value < "" Then _
myArray02 = Array(txtReportingDate.Value, cmbTeammate.Text,
txtHomeTeam.Text, cmbTask02.Text, _
cmbDepartment02.Text, cmbTimeType02.Text, txtUnits02.Value,
txtHours02.Value, txtComment02.Text)
'
If cmbTask03.Text < "" And cmbDepartment03.Text < "" And
cmbTimeType03.Text < "" And _
txtUnits03.Value < "" And txtHours03.Value < "" Then _
myArray03 = Array(txtReportingDate.Value, cmbTeammate.Text,
txtHomeTeam.Text, cmbTask03.Text, _
cmbDepartment03.Text, cmbTimeType03.Text, txtUnits03.Value,
txtHours03.Value, txtComment03.Text)
'
If cmbTask04.Text < "" And cmbDepartment04.Text < "" And
cmbTimeType04.Text < "" And _
txtUnits04.Value < "" And txtHours04.Value < "" Then _
myArray04 = Array(txtReportingDate.Value, cmbTeammate.Text,
txtHomeTeam.Text, cmbTask04.Text, _
cmbDepartment04.Text, cmbTimeType04.Text, txtUnits04.Value,
txtHours04.Value, txtComment04.Text)
'
Sheets("Data").Activate

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
StartHere = ActiveCell.Offset(1, 0).Row - 1

With ActiveSheet
.Range(.Cells(StartHere, 1), .Cells(StartHere, 9)) =
myArray02
End With

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
StartHere = ActiveCell.Offset(1, 0).Row - 1

With ActiveSheet
.Range(.Cells(StartHere, 1), .Cells(StartHere, 9)) =
myArray03
End With

Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
StartHere = ActiveCell.Offset(1, 0).Row - 1

With ActiveSheet
.Range(.Cells(StartHere, 1), .Cells(StartHere, 9)) =
myArray04
End With
End Sub

There will always be at least one array of data to write. Beyond that
it is variable.