Insert data from user form to first empty cells within a row

[dhdnguyen]

Hey there,
I'm currently stuck on how to do this.
Below is an example of what the worksheet looks like. I have provided what userform1 does and performs the way i want it to.

First row that contains data begins at "4".
Rows 1 through 3 have merged cells, not sure if thought would effect the code, but thought I'd mention it.

I'm trying to create userform2 so that it will enter data to fill the first blank spaces labed "A".
Then next entry using userform2 will fill spaces "B".

In addtion, trying to create userform3 to fill info in columns I and J per entry.
I tried copying code I used in userform1 and changed Range("A:A") to Range("I:I"), but doesn't work.

Any help would be greatly appreciated!


[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]"A"[/TD]
[TD="align: center"]"A"[/TD]
[TD="align: center"]"A"[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]"B"[/TD]
[TD="align: center"]"B"[/TD]
[TD="align: center"]"B"[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]data[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"]N/A[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

userform1:

Private Sub cmdSend_Click()

Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

Cells(emptyRow, 1).Value = data1.Value
Cells(emptyRow, 2).Value = data2.Value
Cells(emptyRow, 3).Value = data3.Value
Cells(emptyRow, 4).Value = data4.Value


If Cells(emptyRow, 4).Value = Cells(emptyRow - 1, 4) Then
    Cells(emptyRow, 5).Value = "N"
    Cells(emptyRow, 6).Value = "NA"
    Cells(emptyRow, 7).Value = "NA"
    Cells(emptyRow, 8).Value = "NA"
End If
    
If Cells(emptyRow, 4).Value <> Cells(emptyRow - 1, 4) Then
    Cells(emptyRow, 5).Value = "Y"
End If

Unload Me

End Sub

userform3:

Sub cmdSend_Click()


Dim emptyRow As Long
emptyRow = WorksheetFunction.CountA(Range("I:I")) + 1




Cells(emptyRow,9).Value = data1.Value
Cells(emptyRow, 10).Value = data2.Value


Unload Me


End Sub

Thanks in advance!

 

[NdNoviceHlp]

This seems to work for your Userform2 request. I really don't understand the need for all the userforms? I also don't understand what your trying to achieve with your 3rd userform and/or where the data is? You didn't include any sheet name in your code so I assume it's the active sheet? Anyways, this code refers to sheet2 which U will have to change to your sheet name (use edit replace with your sheet name). HTH. Dave

 Sub test()
Dim Lastrow As Integer, RowCnt As Integer, ColCnt As Integer, Flag As Boolean
With Sheets("Sheet2")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
For ColCnt = 6 To 8
Flag = True
For RowCnt = 4 To Lastrow
If Not Flag And Sheets("Sheet2").Cells(RowCnt, ColCnt) = vbNullString Then
Sheets("Sheet2").Cells(RowCnt, ColCnt) = "B"
Flag = False
End If
If Flag And Sheets("Sheet2").Cells(RowCnt, ColCnt) = vbNullString Then
Sheets("Sheet2").Cells(RowCnt, ColCnt) = "A"
Flag = False
End If
Next RowCnt
Next ColCnt
End Sub

 

[dhdnguyen]

Dave,

Sorry, I didn't provide enough information as for what the purpose of the userforms are for. I will provide a full breakdown of it.
The userforms will input data in activesheet. The same userforms will be used across 100+ sheets. Each sheet will have data input in cols A through L and each row is a new inventory receipt.

Here are the purposes for each of the userforms and breakdown of what textbox fields are and what I would like to accomplish:

#1 : This form is used to record inventory information on the date of receipt from staff "A". Information gets added to the next row once they click the cmd button. Currently userform1 does it what I want it to do.
textbox1 = col A (date)
textbox2 = col B (staff "A" initials)
textbox3 = col C (lot #)
textbox4 = col D (exp date)
textbox5 = col E (qty)
textbox6 = col F (manufacturer's insert publication date)

Based on inputs in this form, it will autocomplete cols G, H, I and J.

col G: compares current col F and previous col F.
If the same then col G = "N"; H, I and J are filled with "NA".
If different then col G = "Y"; H, I and J are blank. If H, I and J are blank, userform2 is used.

#2 : This form is used to review the changes by staff "B" if col G = "Y"; Since col G = "Y" and col H, I and J are blank, I would need col H, I and J to be completed as such once I click the cmd button. It will need to fill the first blanks starting from the top as review doesn't happen as frequently as it should where I work.
textbox7 = col H (date)
textbox8 = col I (staff "B" initials)
textbox9 = col J (comment: what the changes are in the new manufacturer's insert from the previous)

#3 : This form is used for final review by staff "C" IFF cols A though J are completed for that row. Again data input from the top and will continue to the next row. Click cmd button and the following to be filled:
textbox10 = col K (date)
textbox11 = col L (staff "C" initials)

TIA. I should have mentioned that I'm very new to VBA as of 2 weeks ago with no background in coding... I was able to figure out userform1 on my own, but tried googling for userform2 and 3 and couldn't get it to perform the way I need them to.

 

[NdNoviceHlp]

Thanks for the info. Did U trial the code I posted to see if it works to create the "A" & "B" 's for your data set? This will be a start for placing the real info. Maybe. Not sure if it helps after further reviewing your info. U may need to private message me then e-mail me a copy of your wb as U don't have enough posts to post your wb. Dave

 

[dhdnguyen]

Hi Dave,

Yes, I gave the code a try. It ended up filling all the empty rows and only in col 6 at once.
Didn't quite work.
I'll contact you via PM soon.

Thanks!

 

[NdNoviceHlp]

Your previous post #3 doesn't have the same columns as the data that U first posted? Anyways, this is based on post #3 . Data in sheet1. Dave
Userform2...

Sub User2Form()
Dim Lastrow As Integer, RowCnt As Integer
With Sheets("sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
If UserForm2.textbox7.Value = vbNullString Or _
   UserForm2.textbox8.Value = vbNullString Or _
   UserForm2.textbox9.Value = vbNullString Then
MsgBox "All textboxes must be filled!"
End If
For RowCnt = 4 To Lastrow
If Sheets("sheet1").Cells("G", RowCnt) = "Y" Then
Sheets("sheet1").Cells("H", RowCnt) = UserForm2.textbox7.Value
Sheets("sheet1").Cells("I", RowCnt) = UserForm2.textbox8.Value
Sheets("sheet1").Cells("J", RowCnt) = UserForm2.textbox9.Value
Exit For
End If
Next RowCnt
End Sub

Userform3...

Sub User3Form()
Dim Lastrow As Integer, RowCnt As Integer
With Sheets("sheet1")
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
End With
If UserForm3.textbox10.Value = vbNullString Or _
   UserForm3.textbox11.Value = vbNullString Then
MsgBox "All textboxes must be filled!"
End If
For RowCnt = 4 To Lastrow
If Sheets("sheet1").Cells("K", RowCnt) = vbNullString Or _
Sheets("sheet1").Cells("L", RowCnt) Then
Sheets("sheet1").Cells("K", RowCnt) = UserForm2.textbox10.Value
Sheets("sheet1").Cells("L", RowCnt) = UserForm2.textbox11.Value
Exit For
End If
Next RowCnt
End Sub

 

[dhdnguyen]

Hi Dave,

Thanks for all your help! Yeah the information from post #1 were not the same from post #3 . I figured I just needed to provide as little info as possible and still get the same results.

Anyway, I was able to get the forms working the way I needed them to. What fixed if for me was unmerging the cells that I had merged in the first 4 rows.

Ended up using this for userform2:

Private Sub cmdSend_Click()


ActiveSheet.Unprotect Password:="password"


On Error Resume Next
Dim xCell As Range


If textbox8.Value = "" Or textbox9.Value = "" Or textbox10.Value = "" Then
    If MsgBox("Data Entry is not complete. Do you want to continue?", vbQuestion + vbYesNo) <> vbYes Then
    Exit Sub
    End If
End If


    For Each xCell In ActiveSheet.Columns(8).Cells
        If Len(xCell) = 0 Then
        xCell.Value = textbox8.Value
        xCell.Offset(0, 1).Value = textbox9.Value
        xCell.Offset(0, 2).Value = textbox10.Value
        Exit For
    End If
    Next
    
ActiveSheet.Protect Password:="password"


Unload Me


End Sub

userform3:

Private Sub cmdSend_Click()


ActiveSheet.Unprotect Password:="password"


Dim emptyRow As Long


emptyRow = WorksheetFunction.CountA(Range("K:K")) + 1


If textbox11.Value = "" Or textbox12.Value = "" Then
    If MsgBox("Data Entry is not complete. Do you want to continue?", vbQuestion + vbYesNo) <> vbYes Then
    Exit Sub
    End If
End If


Cells(emptyRow, 11).Value = textbox11.Value
Cells(emptyRow, 12).Value = textbox12.Value


ActiveSheet.Protect Password:="password"


Unload Me


End Sub

Again, thanks for helping me with this. Greatly appreciated.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Dave