[Solved] Data Entry Sheet

S

sems711

New member
Is there a way for a user to be able to fill out the form like this (picture below) and have each number separated by the comma be its own row in the table in its respective sheet or do they have to fill it out one by one?
1721226134226.png

I want the form not to clump the response in one cell.
1721235698737.png

But instead separate them like this.
1721235753014.png


Thank you for all your help!
 
Last edited:
sems711 said:
Is there a way for a user to be able to fill out the form like this (picture below) and have each number separated by the comma be its own row in the table in its respective sheet or do they have to fill it out one by one?
View attachment 1507

I want the form not to clump the response in one cell.
View attachment 1508

But instead separate them like this.
View attachment 1509


Thank you for all your help!
Click to expand...
Hello Sems711,

To split the comma-separated number from userfrom use the following code:
1. Double-click on the CommandButton to open its click event handler.
2. Add the following VBA code to handle the insertion of serial numbers into the Excel sheet:

Code: 
Private Sub CommandButton1_Click()
    Dim serialNumbers As String
    Dim serialArray() As String
    Dim i As Integer
    
    ' Get the serial numbers from the TextBox
    serialNumbers = TextBox1.Value
    
    ' Split the serial numbers into an array
    serialArray = Split(serialNumbers, ",")
    
    ' Find the last row with data in column A
    Dim lastRow As Long
    lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    
    ' Loop through the array and insert serial numbers into the sheet
    For i = LBound(serialArray) To UBound(serialArray)
        ThisWorkbook.Sheets("Sheet1").Cells(lastRow + 1 + i, 1).Value = Trim(serialArray(i))
    Next i
    
    ' Clear the TextBox after insertion
    TextBox1.Value = ""
End Sub
 
sems711 said:
The code is giving me an error message, here are screenshots of it and I also attached my code that it was before.
View attachment 1510
View attachment 1511
View attachment 1512
Click to expand...
sems711 said:
The code is giving me an error message, here are screenshots of it and I also attached my code that it was before.
View attachment 1510
View attachment 1511
View attachment 1512
Click to expand...
Hello,

You were supposed to merge the code in your existing VBA code based on your Excel sheet and UserForm.

You can try this updated code:

Code: 
Private Sub CommandButton1_Click()
    Dim serialNumbers As String
    Dim serialArray() As String
    Dim i As Integer
    Dim Total_Rows As Long
    Dim Active_Column As Integer
    Dim Top_Cell As Range
  
    ' Get the serial numbers from the TextBox
    serialNumbers = TextBox1.Value
  
    ' Split the serial numbers into an array
    serialArray = Split(serialNumbers, ",")
  
    ' Set the range for inserting serial numbers
    Total_Rows = ActiveSheet.UsedRange.Rows.Count + 1
    Active_Column = 1
    Set Top_Cell = ActiveSheet.Range("B2")
  
    ' Find the last row with data in column A
    Dim lastRow As Long
    lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
  
    ' Loop through the array and insert serial numbers into the sheet
    For i = LBound(serialArray) To UBound(serialArray)
        If Top_Cell.Cells(lastRow + i, 1).Value = "" Then
            For Each Ctrl In UserForm1.Controls
                If TypeName(Ctrl) = "TextBox" Then
                    Top_Cell.Cells(lastRow + i, Active_Column).Value = Trim(serialArray(i))
                    Active_Column = Active_Column + 1
                End If
            Next Ctrl
            Exit For
        End If
    Next i
  
    ' Clear the TextBox after insertion
    TextBox1.Value = ""
End Sub

Private Sub ListBox1_Click()
    For i = 0 To UserForm1.ListBox1.ListCount - 1
        If UserForm1.ListBox1.Selected(i) = True Then
            Worksheets(UserForm1.ListBox1.List(i)).Activate
        End If
    Next i
End Sub
 
If you are trying to reference a worksheet or workbook that doesn't exist or is misspelled, you will encounter this error.

Please check out your sheet name. Here I used "Sheet1" based on my workbook. Update the sheet name based on your sheet name.

lastRow = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
 
Is there a way to have it reference the sheet that was chosen in the UserForm? I have the code for that the other section break but I am not sure how to make it populate.
1723728108632.png
 
Hello Sems711,

I used ws = ActiveSheet to dynamically reference the active sheet, avoiding hardcoded sheet names. The ListBox1_Click event activates the sheet selected in the ListBox. Then the data is populated by referencing the selected sheet (ws) and using Cells directly.

Code: 
Private Sub CommandButton1_Click()
    Dim serialNumbers As String
    Dim serialArray() As String
    Dim i As Integer
    Dim Total_Rows As Long
    Dim Active_Column As Integer
    Dim Top_Cell As Range
    Dim ws As Worksheet

    ' Get the serial numbers from the TextBox
    serialNumbers = TextBox1.Value

    ' Split the serial numbers into an array
    serialArray = Split(serialNumbers, ",")

    ' Set the range for inserting serial numbers
    Set ws = ActiveSheet ' Use the currently selected sheet
    Total_Rows = ws.UsedRange.Rows.Count + 1
    Active_Column = 1
    Set Top_Cell = ws.Range("B2")

    ' Find the last row with data in column A
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Loop through the array and insert serial numbers into the sheet
    For i = LBound(serialArray) To UBound(serialArray)
        If ws.Cells(lastRow + i, 1).Value = "" Then
            ws.Cells(lastRow + i, Active_Column).Value = Trim(serialArray(i))
            Active_Column = Active_Column + 1
        End If
    Next i

    ' Clear the TextBox after insertion
    TextBox1.Value = ""
End Sub

Private Sub ListBox1_Click()
    Dim i As Integer
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            Worksheets(ListBox1.List(i)).Activate
            Exit For
        End If
    Next i
End Sub
 
Hi!

I added this code but it was not adding the serial numbers into the table and not all the values were being added. Is there any way to fix this? Thanks for all your help
 
Hello,

The row calculation is adjusted to lastRow + i + 1 to start from the next empty row. Make sure the TextBox1 contains the correct serial numbers separated by commas before hitting the "Add" button.

Code: 
Private Sub CommandButton1_Click()
    Dim serialNumbers As String
    Dim serialArray() As String
    Dim i As Integer
    Dim Total_Rows As Long
    Dim Top_Cell As Range
    Dim ws As Worksheet

    ' Get the serial numbers from the TextBox
    serialNumbers = TextBox1.Value

    ' Split the serial numbers into an array
    serialArray = Split(serialNumbers, ",")

    ' Set the range for inserting serial numbers
    Set ws = ActiveSheet ' Use the currently selected sheet
    Total_Rows = ws.UsedRange.Rows.Count + 1
    Set Top_Cell = ws.Range("B2")

    ' Find the last row with data in column A
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' Loop through the array and insert serial numbers into the sheet
    For i = LBound(serialArray) To UBound(serialArray)
        ws.Cells(lastRow + i + 1, 1).Value = Trim(serialArray(i)) ' Ensure serials are inserted in column A
    Next i

    ' Clear the TextBox after insertion
    TextBox1.Value = ""
End Sub
 
You must log in or register to reply here.

Online statistics

Members online
0
Guests online
1
Total visitors
1
Totals may include hidden visitors.

Forum statistics

Threads
352
Messages
1,541
Members
652
Latest member
William Tang

Share this page

Back
Top