Userform to cycle through inputs and return values in order

[bruce24444]

I have a multipage userform that when finished, will have a combination of 300+ textboxes, comboboxes, checkboxes, combo and Option buttons to which the enduser may or may not enter information into. Because the end user may or may not be required to enter information in any one particular box, I have coded the userform to check each input area and then return the input information if the input area is greater than “”.

What I’m looking for suggestions on or help on is: Is there anyway I can change my code so that when the user clicks “OK” the code will cycle through each input.value and if the end user has input some information it will enter it on the next empty row without me entering a specific row number for each line of code?

The problem I ran into is that I have coded almost half of the userform and then I realized I missed 2 input sections around Cells(eRow, 102).Value. So in order to have that information show up in the proper order, I entered code for lines Cells(eRow, 102).Value and Cells(eRow, 103).Value, but now have to change every one of the following lines in sequential order.

I know the answer lies within the eRow = Sheet1.Range("A:A").End(xlUp).Row line and the Cells(eRow, 102).Value line, but I just can’t get it to work. All I get is the very last input.value.

As a second part of this question, if I can get the input.value to begin at A15 and then go to A16, A17, …. That will save me transposing the entire thing at the end.

Private Sub CommandButtonOK_Click()

Dim eRow As Long
    
'Make Sheet1 Active
Sheets(1).Activate

'Determine eRow
eRow = Sheet1.Range("A:A").End(xlUp).Row

If txt_Inv_Date.Value > "" Then
    Cells(eRow, 1).Value = "Invoice Date: " + txt_Inv_Date.Value
End If
If txt_Total_ILE_Amount.Value > "" Then
    Cells(eRow, 2).Value = "Total ILE: $" + txt_Total_ILE_Amount.Value
End If
If txt_Meals_Inv.Value > "" Then
    Cells(eRow, 3).Value = "Attachment: Meal Receipts #" + txt_Meals_Inv.Value
End If
If txt_Meals_Worksheet.Value > "" Then
    Cells(eRow, 4).Value = "Attachment: Meal Receipts Worksheet #" + txt_Meals_Worksheet.Value
End If
If Check_Per_Diem.Value > "" Then
    Cells(eRow, 5).Value = "Meal allowance as per diem"
End If

'and so on ....... up to

If opt_Pay_Close.Value = True Then
    Cells(eRow, 326).Value = "Report concluded - CLOSE file"
End If

'align info for readability
With Selection
    .HorizontalAlignment = xlGeneral
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
End With
     
' Closes userform when "ok" is selected
Unload Me


'Captures information and changes data from rows to columns
    Range("A1:IT1").Select
    Selection.Copy
    Range("A15").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        True, Transpose:=True

'Review lines 15 to Last Row and delete if empty
Dim Lastrow As Integer
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A15:A" & Lastrow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Selection.CurrentRegion.Select
    Selection.CheckSpelling
    Selection.Copy
    
End Sub

 

[Tinbendr]

I think you're confusion rows and columns.

You code stores the data on one row. But then, I notice later in your code, you're copying and transposing data that isn't going to be there. And you're adding the column header here?

"Invoice Date: " + txt_Inv_Date.Value

This code

eRow = Sheet1.Range("A:A").End(xlUp).Row

Returns the last row WITH data. So you'll need to add one to get the next EMPTY row.

eRow = Sheet1.Range("A:A").End(xlUp).Row + 1

And if you want to start AT LEAST on row 15, then

eRow = Sheet1.Range("A:A").End(xlUp).Row + 1
if eRow < 15 then eRow = 15

As far as renumbering your columns, you can always create a variable to hold the column number. You'll just have to increment the column as you move through the code.

aCol = 1
Cells(eRow, aCol).Value = "Invoice Date: " + txt_Inv_Date.Value
aCol = aCol + 1

Also, I would not bother testing each one for a value, if you're adding to a dataset. The extra time writing a empty value will be gained in giving up the If/Then.

If, in fact, you are trying to create a vertical dataset and then transpose it, then you need to increment the eRow and not the column.

    Cells(eRow, 1).Value = "Invoice Date: " + txt_Inv_Date.Value
eRow = eRow + 1
End If If txt_Total_ILE_Amount.Value > "" Then
    Cells(eRow, 1).Value = "Total ILE: $" + txt_Total_ILE_Amount.Value

Notice, the column number remains the same.

 

[bruce24444]

Thank you very much for the suggestions.

I changed my code as per your instructions and it works much exactly how I wanted it to.

Changed:

'Determine eRow
eRow = Sheet1.Range("A:A").End(xlUp).Row
'TO:
'Determine eRow
eRow = Sheet1.Range("A:A").End(xlUp).Row + 1 'Finds the last blank row
If eRow < 15 Then eRow = 15

Then all of my:

    Cells(eRow, 158).Value
'TO
    Cells(eRow, 1).Value

And finally, added

     eRow = eRow + 1

before each of my End If statements

And it works just as I was trying to make it work, even better because there is no longer a need to transpose columns into a single row.