Saving textbox data from a userform to Sheet2 cells...

[goldbeje]

**Note: I do not have the ability to upload attachments under my profile for some reason or I would. Is there a way to be able to attach documents??

Hello!

I have a macro that formats "Sheet2" from data in Sheet1 and then launches a user form (UserForm1). From UserForm1 there are (4) textboxes and each one of these text boxes would need to save the data within either 3 or 4 times each. TextBox1 and TextBox2 would need to be saved (4) times. TextBox3 and TextBox4 would need to saved (3) times into the respective cells in Sheet2.

i.e.
PCRLocation - PCR1 (saved 4 times F2:F5)
PCR Plate ID - 119416 (saved 4 times G2:G5)
Source ID - J93174_001 (saved 3 times H2:H4)
DNASource ID - DNA1 (saved 3 times J2:J4)

I have been able to code it to save to the correct cells, but not in replicates. Does anyone know how to approach this?? I cannot seem to get this to work at all.
Currently the code for UserForm1 is as shown below:

Private Sub CommandButton1_Click()

Unload Me
End Sub

Private Sub CommandButton3_Click()

Call UserForm1_Initialize
End Sub

Private Sub CommandButton2_Click()

Dim irow As String
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")

'find first row in Sheet2
irow = ws.Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).Row

With ws

.Range("H" & irow) = TextBox1.Value
.Range("J" & irow) = TextBox2.Value
.Range("G" & irow) = TextBox3.Value
.Range("F" & irow) = TextBox4.Value
   
End With

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""

End Sub

Second question, right now there is no code to decifer if the text boxes are filled with data out of the order listed on Sheet2, but if there is a way to associate the text box data with a specific common name so the save starts at the right row that would be ideal. Currently the saves start at Row2 ColumnF (first empty cell). The ideal would be if a user wants to start with lets say PCR Location (PCR4) they could and the macro would know to start with that row (line 14 on Sheet2) from Sheet2 for saving those records.

Is it possible to code the macro to save with row association? If so please help.

Thanks in advance,
J.

 

[Bagharmin]

As far as saving the textbox values in multiple cells, you could add a simple copy statement to your code. For example:

With ws
.Range("H" & irow) = TextBox1.Value
End With

becomes

With ws
.Range("H" & irow)=TextBox1.Value
.Range("H" & irow).Copy .Range("H" & irow + 1 & ":H" & irow + 2)
End With

Then you can recalculate irow.

 

[goldbeje]

Thank you Bagharmin for your response.

I went with the method below:

With ws
    .Range("H" & irow).Resize(3) = TextBox1.Value
    .Range("J" & irow).Resize(3) = TextBox2.Value
    .Range("G" & irow).Resize(4) = TextBox3.Value
    .Range("F" & irow).Resize(4) = TextBox4.Value