Userform to populate 2 sheets

jimmisavage

Board Regular
Joined
Jun 28, 2017
Messages
130
So I have this code to enter data from a userform.



Code:
Private Sub cmdAdd_Click()Dim iRow As Long
Dim ws As Worksheet, ws2 As Worksheet
Dim CopyToSheet As String
Set ws = Worksheets("Ingredients")
'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check meal name
If Trim(Me.TextBox1.Value) = "" Then
  Me.TextBox1.SetFocus
  MsgBox "Enter Meal Name"
  Exit Sub
End If


'check for an ingredient
If Trim(Me.TextBox2.Value) = "" Then
  Me.TextBox2.SetFocus
  MsgBox "Please begin adding Ingredients"
  Exit Sub
End If


'check for a Measurement
If Trim(Me.TextBox3.Value) = "" Then
  Me.TextBox3.SetFocus
  MsgBox "Entering Measurements will make life easier when buying food"
  Exit Sub
End If


'check for a Mealtime
If Trim(Me.mealtime1.Value) = "" Then
  Me.TextBox3.SetFocus
  MsgBox "Please use the dropdown list to choose which meal to assign this recipe to"
  Exit Sub
End If


'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
  '.Unprotect Password:="savage"
  .Cells(iRow, 1).Value = Me.TextBox1.Value
  .Cells(iRow, 2).Value = Me.TextBox2.Value
  .Cells(iRow, 3).Value = Me.TextBox3.Value
  .Cells(iRow, 4).Value = Me.TextBox4.Value
  .Cells(iRow, 5).Value = Me.TextBox5.Value
  .Cells(iRow, 6).Value = Me.TextBox6.Value
  .Cells(iRow, 7).Value = Me.TextBox7.Value
  .Cells(iRow, 8).Value = Me.TextBox8.Value
  .Cells(iRow, 9).Value = Me.TextBox9.Value
  .Cells(iRow, 10).Value = Me.TextBox10.Value
  .Cells(iRow, 11).Value = Me.TextBox11.Value
  .Cells(iRow, 12).Value = Me.TextBox12.Value
  .Cells(iRow, 13).Value = Me.TextBox13.Value
  .Cells(iRow, 14).Value = Me.TextBox14.Value
  .Cells(iRow, 15).Value = Me.TextBox15.Value
  .Cells(iRow, 16).Value = Me.TextBox16.Value
  .Cells(iRow, 17).Value = Me.TextBox17.Value
  .Cells(iRow, 18).Value = Me.TextBox18.Value
  .Cells(iRow, 19).Value = Me.TextBox19.Value
  .Cells(iRow, 20).Value = Me.TextBox20.Value
  .Cells(iRow, 21).Value = Me.TextBox21.Value
  .Cells(iRow, 22).Value = Me.TextBox22.Value
  .Cells(iRow, 23).Value = Me.TextBox23.Value
  .Cells(iRow, 24).Value = Me.TextBox24.Value
  .Cells(iRow, 25).Value = Me.TextBox25.Value
  .Cells(iRow, 26).Value = Me.TextBox26.Value
  .Cells(iRow, 27).Value = Me.TextBox27.Value
  .Cells(iRow, 28).Value = Me.TextBox28.Value
  .Cells(iRow, 29).Value = Me.TextBox29.Value
  .Cells(iRow, 30).Value = Me.TextBox30.Value
  .Cells(iRow, 31).Value = Me.TextBox31.Value
  .Cells(iRow, 32).Value = Me.TextBox32.Value
  .Cells(iRow, 33).Value = Me.TextBox33.Value
  .Cells(iRow, 34).Value = Me.TextBox34.Value
  .Cells(iRow, 35).Value = Me.TextBox35.Value
  .Cells(iRow, 36).Value = Me.TextBox36.Value
  .Cells(iRow, 37).Value = Me.TextBox37.Value
  .Cells(iRow, 38).Value = Me.TextBox38.Value

  .Cells(iRow, 39).Value = Me.TextBox39.Value
  .Cells(iRow, 40).Value = Me.TextBox40.Value
  .Cells(iRow, 41).Value = Me.TextBox41.Value
  .Cells(iRow, 42).Value = Me.TextBox42.Value
  .Cells(iRow, 43).Value = Me.TextBox43.Value
  .Cells(iRow, 44).Value = Me.TextBox44.Value
  .Cells(iRow, 45).Value = Me.TextBox45.Value
  .Cells(iRow, 46).Value = Me.TextBox46.Value
  .Cells(iRow, 47).Value = Me.TextBox47.Value
  .Cells(iRow, 48).Value = Me.mealtime1.Value
  .Cells(iRow, 49).Value = Me.mealtime2.Value
  
  '.Protect Password:="savage"
 
End With
'---------------------------------------------------
'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox6.Value = ""
Me.TextBox7.Value = ""
Me.TextBox8.Value = ""
Me.TextBox9.Value = ""
Me.TextBox10.Value = ""
Me.TextBox11.Value = ""
Me.TextBox12.Value = ""
Me.TextBox13.Value = ""
Me.TextBox14.Value = ""
Me.TextBox15.Value = ""
Me.TextBox16.Value = ""
Me.TextBox17.Value = ""
Me.TextBox18.Value = ""
Me.TextBox19.Value = ""
Me.TextBox20.Value = ""
Me.TextBox21.Value = ""
Me.TextBox22.Value = ""
Me.TextBox23.Value = ""
Me.TextBox24.Value = ""
Me.TextBox25.Value = ""
Me.TextBox26.Value = ""
Me.TextBox27.Value = ""
Me.TextBox28.Value = ""
Me.TextBox29.Value = ""
Me.TextBox30.Value = ""
Me.TextBox31.Value = ""
Me.TextBox32.Value = ""
Me.TextBox33.Value = ""
Me.TextBox34.Value = ""
Me.TextBox35.Value = ""
Me.TextBox36.Value = ""
Me.TextBox37.Value = ""
Me.TextBox38.Value = ""
Me.TextBox39.Value = ""
Me.TextBox40.Value = ""
Me.TextBox41.Value = ""
Me.mealtime1.Value = ""
Me.mealtime2.Value = ""
Unload Me
thankyou.Show
End Sub

I would like 2 bits of data to also input in a sheet called MasterSheet. I thought adding something like this would work but i'm not sure where (or if it's even right)?

Code:
Set ws = Worksheets("MasterSheet")
With ws
  .Cells(iRow, 8).Value = Me.mealtime1.Value
  .Cells(iRow, 9).Value = Me.mealtime2.Value
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top