Userform to populate 2 sheets

jimmisavage

Board Regular
Joined
Jun 28, 2017
Messages
125
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:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,752
Put it after your "End With" and add "End With" to your code. HTH. Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,127,035
Messages
5,622,332
Members
415,894
Latest member
silverhaze

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
Top