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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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