Can I code one user form to input the data to another worksheet column depending on which week button is selected or do I need to create a new userfor

J1149

New Member
Joined
Jan 20, 2007
Messages
39
Current Code for week 1:

Private Sub LbrOptionBtn1_Click()
Select Case Frame2.ActiveControl.Name
Case "LbrOptionBtn1"
UserForm2.Hide
UserForm1.Show
End Select

Private Sub DataButton1_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("LaborDelivery Worksheet")
' Inserts Data to very hidden labor worksheet
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 2).Row
With ws
.Range("B" & irow) = TextBox1.Value
.Range("B" & irow + 1) = TextBox2.Value
.Range("B" & irow + 2) = TextBox3.Value
.Range("B" & irow + 3) = TextBox4.Value
End Sub.Range("B" & irow + 4) = TextBox5.Value
.Range("B" & irow + 5) = TextBox6.Value
.Range("B" & irow + 6) = TextBox7.Value
.Range("C" & irow + 1) = TextBox8.Value
.Range("C" & irow + 2) = TextBox9.Value
.Range("C" & irow + 3) = TextBox10.Value
.Range("C" & irow + 4) = TextBox11.Value
.Range("C" & irow + 5) = TextBox12.Value
.Range("D" & irow) = TextBox13.Value
.Range("D" & irow + 1) = TextBox14.Value
.Range("D" & irow + 2) = TextBox15.Value
.Range("D" & irow + 3) = TextBox16.Value
.Range("D" & irow + 4) = TextBox17.Value
.Range("D" & irow + 5) = TextBox18.Value
.Range("D" & irow + 6) = TextBox19.Value
End With
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox13.Value = ""
TextBox14.Value = ""
TextBox15.Value = ""
TextBox16.Value = ""
TextBox17.Value = ""
TextBox18.Value = ""
TextBox19.Value = ""
Unload Me
End Sub
 
Last edited:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

bertie

Well-known Member
Joined
Jun 12, 2009
Messages
1,869
When I work with UserForms I usually have a global variable for the current row as you use this in multiple procedures.

Rich (BB code):
Public iRow As Long  'row the current record is on

And I would split out the procedures to do specific thing, e.g., ClearControls():
Rich (BB code):
Private Sub ClearControls()
   TextBox1.Value = ""
   TextBox2.Value = ""
   TextBox3.Value = ""
   TextBox4.Value = ""
   TextBox5.Value = ""
   TextBox6.Value = ""
   TextBox7.Value = ""
   TextBox8.Value = ""
   TextBox9.Value = ""
   TextBox10.Value = ""
   TextBox11.Value = ""
   TextBox12.Value = ""
   TextBox13.Value = ""
   TextBox14.Value = ""
   TextBox15.Value = ""
   TextBox16.Value = ""
   TextBox17.Value = ""
   TextBox18.Value = ""
   TextBox19.Value = ""
End Sub

And PopulateSpreadsheet():
Rich (BB code):
Private Sub PopulateSpreadsheet(ByVal SheetName As String)


   With Sheets(SheetName)
      iRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 2).Row
   
      .Range("B" & iRow) = TextBox1.Value
      .Range("B" & iRow + 1) = TextBox2.Value
      .Range("B" & iRow + 2) = TextBox3.Value
      .Range("B" & iRow + 3) = TextBox4.Value
      .Range("B" & iRow + 4) = TextBox5.Value
      .Range("B" & iRow + 5) = TextBox6.Value
      .Range("B" & iRow + 6) = TextBox7.Value
      .Range("C" & iRow + 1) = TextBox8.Value
      .Range("C" & iRow + 2) = TextBox9.Value
      .Range("C" & iRow + 3) = TextBox10.Value
      .Range("C" & iRow + 4) = TextBox11.Value
      .Range("C" & iRow + 5) = TextBox12.Value
      .Range("D" & iRow) = TextBox13.Value
      .Range("D" & iRow + 1) = TextBox14.Value
      .Range("D" & iRow + 2) = TextBox15.Value
      .Range("D" & iRow + 3) = TextBox16.Value
      .Range("D" & iRow + 4) = TextBox17.Value
      .Range("D" & iRow + 5) = TextBox18.Value
      .Range("D" & iRow + 6) = TextBox19.Value
   End With
End Sub

Note this this procedure has an argument so you can pass the name of the worksheet you want to poulate.

You would call this by:
Rich (BB code):
Private Sub DataButton1_Click()
   PopulateSpreadsheet "LaborDelivery Worksheet"
   ClearControls
   Unload Me
End Sub

So, the answer is, you only need one UserForm.

[Hint] You could write yet another procedure to PopulateControls()

Hope this helps,
Bertie

[Edit] This code is untested as I didn't want to set up all those controls.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,518
Messages
5,529,309
Members
409,862
Latest member
lbisacca
Top