run time error 9 subscript out of range for other user

[Bryancarlo]

Hi, Im getting the error even if the sheet is existing. Here is my code:

Private Sub save_Click()
Dim irow As Long
Dim icol As Long
Dim ws As Worksheet
If TextBox1.Value = "" Or TextBox2.Value = "" Then
MsgBox "Enter Consumer ID and/or Name"
ElseIf status.Value = "" Then
MsgBox "Choose status"
ElseIf auditend.Value = "" Then
MsgBox "Choose status"
Else

Set ws = Worksheets("Prod_data")
'find first row in database
irow = ws.Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).Row
With ws
.Range("A" & irow) = TextBox1.Value
.Range("B" & irow) = TextBox2.Value
.Range("C" & irow) = account.Value
.Range("D" & irow) = status.Value
.Range("E" & irow) = transdate.Value
.Range("F" & irow) = auditdate.Value
.Range("G" & irow) = qa.Value
.Range("H" & irow) = comment.Value
.Range("I" & irow) = auditstart.Value
.Range("J" & irow) = auditend.Value
.Range("BP" & irow) = datetoday.Caption

End With
TextBox1.Value = ""
TextBox2.Value = ""
status.Value = ""
transdate.Value = ""
comment.Value = ""

TextBox1.Enabled = False
TextBox2.Enabled = False
account.Enabled = True
transdate.Enabled = False
auditdate.Enabled = False
qa.Enabled = True
auditstart.Enabled = False
auditend.Enabled = False
status.Enabled = False
save.Enabled = False
CommandButton3.Enabled = False
auditstart.Value = ""
auditend.Value = ""
Lunch.Enabled = True
stop1.Enabled = True
nonprod.Enabled = True
done.Enabled = True
prod.Enabled = True

End If
End Sub

 

[bobsan42]

You will have to provide a bit more information to elaborate on the problem.
Which line triggers the error and in what circumstances?
if it is the line set ws = .... try to modify it in this way:

Set ws = Thisworkbook.Worksheets("Prod_data")

Generally you need to reference your objects explicitly to make sure you will get the correct result.
But you need to explain more to get adequate answers.