Using a form to input data into a schedule

[Chris]

Hello to all:

I'm quite new to VBA and was working on a form to input data into a
schedule. I am updating certain fields within the schedule as others
have formulas based on the input. Not sure if I've approached it in
the correct manner but I cannot get the second data entry to go to the
next row below for each field. Please find the code that I used below:

Private Sub CommandButton1_Click()

Worksheets("Schedule").Activate
Dim Port As Object

Set Port = Worksheets("Schedule").Range("B7")
Port.Value = TextBox1.Text
ActiveCell.Offset(1, 0).Range("B7").Select

Dim ESOP As Object

Set ESOP = Worksheets("Schedule").Range("E7")
ESOP.Value = TextBox2.Text
ActiveCell.Offset(1, 0).Range("E7").Select

Dim Cargo As Object

Set C argo = Worksheets("Schedule").Range("H7")
Cargo.Value = TextBox3.Text
ActiveCell.Offset(1, 0).Range("H7").Select

Dim Prod As Object

Set Prod = Worksheets("Schedule").Range("I7")
Prod.Value = TextBox4.Text
ActiveCell.Offset(1, 0).Range("I7").Select




MsgBox "One record written to Schedule"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""

TextBox1.SetFocus

Else
Unload Me
End If

End Sub

Can someone please assist me in getting back on track with this one?
Thanks in advance for your help.

Kind Regards,
Chris McCourt

 

[Tom Ogilvy]

opening the workbook read only and checking the writereservedby property has
worked for me - but others have said it hasn't worked for them. I don't
know of another way than that.