User Input forms - wont switch worksheets

[numba]

I have created a series of forms that input job tracking data into two different worksheets - Hours into one and Materials into another.
The main menu is opens the form but it doesnt change the worksheet that it is populated to for some reason?
When I click "Enter Hours" It opens the form for staff hours BUT if the last thing I did previously was enter materials it attempts to populate that sheet.

Even though my code specifies the sheet here

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Value = txtDate
Cells(erow, 2).Value = CboStaff
Cells(erow, 3).Value = Val(txtNormalHrs.Text)


All the worksheets do seem to stay open in the background. When I am finished I dont think I want the person inputting to see that as much unless they have to perhaps look for an error but I can deal with that once I make sure that the data goes into the right sheet!

 

[gallen]

erow is just a number (.row) so the only place you specify the sheet is when you specify the row. You'll need to specify sheet2 before all the lines.

 

[My Aswer Is This]

Try this:

With Sheet2
erow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(erow, 1).Value = txtDate
.Cells(erow, 2).Value = CboStaff
.Cells(erow, 3).Value = Val(txtNormalHrs.Text)
End With

 

[numba]

Try this:

With Sheet2
erow = .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
.Cells(erow, 1).Value = txtDate
.Cells(erow, 2).Value = CboStaff
.Cells(erow, 3).Value = Val(txtNormalHrs.Text)
End With

Thankyou! Makes sense but didnt work. Will I need to do before each row?
I have ;


Private Sub cmdEnter_Click()


Dim erow As Long
With Sheet6
erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1).Value = txtDate
Cells(erow, 2).Value = CboStaff
Cells(erow, 3).Value = txtMaterial
Cells(erow, 4).Value = ChkCoil
Cells(erow, 5).Value = Val(txtPrice.Text)
Cells(erow, 7).Value = CboPriceSource
Cells(erow, 8).Value = txtComments
End With
End Sub

 

[My Aswer Is This]

The script I sent you should work I test all my scripts but you could use this:

When things do not work you should say what happened. Did you get a error or what. Do not just say
"Did not Work"

Dim erow As Long
 
 erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
 Sheet6.Cells(erow, 1).Value = txtDate
 Sheet6.Cells(erow, 2).Value = CboStaff
 
 etc
 etc

 

[Raymondc190466]

Could this help to solve your problem:

[COLOR=#333333]Private Sub cmdEnter_Click()[/COLOR]

[COLOR=#333333]Dim erow As Long
[/COLOR]Dim ws as Worksheet

Set ws = sheets("Sheet6")

[COLOR=#333333]With Sheet6[/COLOR]
[COLOR=#333333]erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/COLOR]
[COLOR=#333333]Cells(erow, 1).Value = txtDate[/COLOR]
[COLOR=#333333]Cells(erow, 2).Value = CboStaff[/COLOR]
[COLOR=#333333]Cells(erow, 3).Value = txtMaterial[/COLOR]
[COLOR=#333333]Cells(erow, 4).Value = ChkCoil[/COLOR]
[COLOR=#333333]Cells(erow, 5).Value = Val(txtPrice.Text)[/COLOR]
[COLOR=#333333]Cells(erow, 7).Value = CboPriceSource[/COLOR]
[COLOR=#333333]Cells(erow, 8).Value = txtComments[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[numba]

No this line threw up the error - Set ws = sheets("Sheet6")
Runtime error - subscript out of range

Could this help to solve your problem:

[COLOR=#333333]Private Sub cmdEnter_Click()[/COLOR]

[COLOR=#333333]Dim erow As Long
[/COLOR]Dim ws as Worksheet

Set ws = sheets("Sheet6")

[COLOR=#333333]With Sheet6[/COLOR]
[COLOR=#333333]erow = Sheet6.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row[/COLOR]
[COLOR=#333333]Cells(erow, 1).Value = txtDate[/COLOR]
[COLOR=#333333]Cells(erow, 2).Value = CboStaff[/COLOR]
[COLOR=#333333]Cells(erow, 3).Value = txtMaterial[/COLOR]
[COLOR=#333333]Cells(erow, 4).Value = ChkCoil[/COLOR]
[COLOR=#333333]Cells(erow, 5).Value = Val(txtPrice.Text)[/COLOR]
[COLOR=#333333]Cells(erow, 7).Value = CboPriceSource[/COLOR]
[COLOR=#333333]Cells(erow, 8).Value = txtComments[/COLOR]
[COLOR=#333333]End With[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[numba]

Thanks for the explanation (sorry for not giving error). Yes. Worked perfectly when I added to each line. Thank you!