I have a VB in my workbook but I'm having trouble with it (I did not write
this I just made some changes to fit my workbook). I have 26 different sheets
(one for each months individual product movements-we track start and finish
time , one for each months totals, we track the number of times the 30 or so
different products were handled each months (I have a SUMPRODUCT taking care
of this part), plus we have a sheet totalling the years total of all
movements. It took me a month to get all of this setup, what I am trying to
do is have a USERFORM to fill out all of the info of the movements because we
have 15 different people filling out the movement at different times because
we are on a shared drive.

PROBLEM I keep getting a RUN-TIME ERROR '9' when I debug it highlights a
line in me VB, it is the fourth row down. My workbook sheets are like this
JAN 07, JAN TOTALS, FEB 07, FEB TOTALS... plus our total tally sheets.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MAR 07")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a barge number
If Trim(Me.txtBargeID.Value) = "" Then
Me.txtBargeID.SetFocus
MsgBox "Please enter a barge name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtBargeID.Value
ws.Cells(iRow, 2).Value = Me.txtProd.Value
ws.Cells(iRow, 3).Value = Me.txtStartDate.Value
ws.Cells(iRow, 4).Value = Me.txtFinishDate.Value
ws.Cells(iRow, 9).Value = Me.txtLorP.Value


'clear the data
Me.txtBargeID.Value = ""
Me.txtProd.Value = ""
Me.txtStartDate.Value = ""
Me.txtFinishDate.Value = ""
Me.txtLorP.Value = ""
Me.txtBargeID.SetFocus

End Sub

I forgot to mention that I can get the VB to work on my test sheet which has
only 2 sheets but not in my complete work book.

"tankerman" wrote:

I have a VB in my workbook but I'm having trouble with it (I did not write
this I just made some changes to fit my workbook). I have 26 different sheets
(one for each months individual product movements-we track start and finish
time , one for each months totals, we track the number of times the 30 or so
different products were handled each months (I have a SUMPRODUCT taking care
of this part), plus we have a sheet totalling the years total of all
movements. It took me a month to get all of this setup, what I am trying to
do is have a USERFORM to fill out all of the info of the movements because we
have 15 different people filling out the movement at different times because
we are on a shared drive.

PROBLEM I keep getting a RUN-TIME ERROR '9' when I debug it highlights a
line in me VB, it is the fourth row down. My workbook sheets are like this
JAN 07, JAN TOTALS, FEB 07, FEB TOTALS... plus our total tally sheets.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MAR 07")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a barge number
If Trim(Me.txtBargeID.Value) = "" Then
Me.txtBargeID.SetFocus
MsgBox "Please enter a barge name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtBargeID.Value
ws.Cells(iRow, 2).Value = Me.txtProd.Value
ws.Cells(iRow, 3).Value = Me.txtStartDate.Value
ws.Cells(iRow, 4).Value = Me.txtFinishDate.Value
ws.Cells(iRow, 9).Value = Me.txtLorP.Value


'clear the data
Me.txtBargeID.Value = ""
Me.txtProd.Value = ""
Me.txtStartDate.Value = ""
Me.txtFinishDate.Value = ""
Me.txtLorP.Value = ""
Me.txtBargeID.SetFocus

End Sub

Not sure of the problem
I think its becaue the Form subroutine is private. You can try to remove
private and see what happens

Normally the form will get data and the manipulation of the data will take
placve in the main code.

"tankerman" wrote:

I forgot to mention that I can get the VB to work on my test sheet which has
only 2 sheets but not in my complete work book.

"tankerman" wrote:

I have a VB in my workbook but I'm having trouble with it (I did not write
this I just made some changes to fit my workbook). I have 26 different sheets
(one for each months individual product movements-we track start and finish
time , one for each months totals, we track the number of times the 30 or so
different products were handled each months (I have a SUMPRODUCT taking care
of this part), plus we have a sheet totalling the years total of all
movements. It took me a month to get all of this setup, what I am trying to
do is have a USERFORM to fill out all of the info of the movements because we
have 15 different people filling out the movement at different times because
we are on a shared drive.

PROBLEM I keep getting a RUN-TIME ERROR '9' when I debug it highlights a
line in me VB, it is the fourth row down. My workbook sheets are like this
JAN 07, JAN TOTALS, FEB 07, FEB TOTALS... plus our total tally sheets.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MAR 07")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a barge number
If Trim(Me.txtBargeID.Value) = "" Then
Me.txtBargeID.SetFocus
MsgBox "Please enter a barge name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtBargeID.Value
ws.Cells(iRow, 2).Value = Me.txtProd.Value
ws.Cells(iRow, 3).Value = Me.txtStartDate.Value
ws.Cells(iRow, 4).Value = Me.txtFinishDate.Value
ws.Cells(iRow, 9).Value = Me.txtLorP.Value


'clear the data
Me.txtBargeID.Value = ""
Me.txtProd.Value = ""
Me.txtStartDate.Value = ""
Me.txtFinishDate.Value = ""
Me.txtLorP.Value = ""
Me.txtBargeID.SetFocus

End Sub

The subscript out of range error (9) means that there is no worksheet in that
workbook that has the name you're using.

So either you don't have a "Mar 07" worksheet--or you're in the wrong workbook.

Maybe...

Set ws = Worksheets("MAR 07")
should be
Set ws = thisworkbook.Worksheets("MAR 07")

Or it could be a simple spelling error (extra spaces in that name???).

tankerman wrote:

I have a VB in my workbook but I'm having trouble with it (I did not write
this I just made some changes to fit my workbook). I have 26 different sheets
(one for each months individual product movements-we track start and finish
time , one for each months totals, we track the number of times the 30 or so
different products were handled each months (I have a SUMPRODUCT taking care
of this part), plus we have a sheet totalling the years total of all
movements. It took me a month to get all of this setup, what I am trying to
do is have a USERFORM to fill out all of the info of the movements because we
have 15 different people filling out the movement at different times because
we are on a shared drive.

PROBLEM I keep getting a RUN-TIME ERROR '9' when I debug it highlights a
line in me VB, it is the fourth row down. My workbook sheets are like this
JAN 07, JAN TOTALS, FEB 07, FEB TOTALS... plus our total tally sheets.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MAR 07")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a barge number
If Trim(Me.txtBargeID.Value) = "" Then
Me.txtBargeID.SetFocus
MsgBox "Please enter a barge name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtBargeID.Value
ws.Cells(iRow, 2).Value = Me.txtProd.Value
ws.Cells(iRow, 3).Value = Me.txtStartDate.Value
ws.Cells(iRow, 4).Value = Me.txtFinishDate.Value
ws.Cells(iRow, 9).Value = Me.txtLorP.Value

'clear the data
Me.txtBargeID.Value = ""
Me.txtProd.Value = ""
Me.txtStartDate.Value = ""
Me.txtFinishDate.Value = ""
Me.txtLorP.Value = ""
Me.txtBargeID.SetFocus

End Sub

--

Dave Peterson

I had the sheet name in caps when I changed the name case it works fine

Thanks everyone for the help.
"Dave Peterson" wrote:

The subscript out of range error (9) means that there is no worksheet in that
workbook that has the name you're using.

So either you don't have a "Mar 07" worksheet--or you're in the wrong workbook.

Maybe...

Set ws = Worksheets("MAR 07")
should be
Set ws = thisworkbook.Worksheets("MAR 07")

Or it could be a simple spelling error (extra spaces in that name???).

tankerman wrote:

I have a VB in my workbook but I'm having trouble with it (I did not write
this I just made some changes to fit my workbook). I have 26 different sheets
(one for each months individual product movements-we track start and finish
time , one for each months totals, we track the number of times the 30 or so
different products were handled each months (I have a SUMPRODUCT taking care
of this part), plus we have a sheet totalling the years total of all
movements. It took me a month to get all of this setup, what I am trying to
do is have a USERFORM to fill out all of the info of the movements because we
have 15 different people filling out the movement at different times because
we are on a shared drive.

PROBLEM I keep getting a RUN-TIME ERROR '9' when I debug it highlights a
line in me VB, it is the fourth row down. My workbook sheets are like this
JAN 07, JAN TOTALS, FEB 07, FEB TOTALS... plus our total tally sheets.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MAR 07")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a barge number
If Trim(Me.txtBargeID.Value) = "" Then
Me.txtBargeID.SetFocus
MsgBox "Please enter a barge name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtBargeID.Value
ws.Cells(iRow, 2).Value = Me.txtProd.Value
ws.Cells(iRow, 3).Value = Me.txtStartDate.Value
ws.Cells(iRow, 4).Value = Me.txtFinishDate.Value
ws.Cells(iRow, 9).Value = Me.txtLorP.Value

'clear the data
Me.txtBargeID.Value = ""
Me.txtProd.Value = ""
Me.txtStartDate.Value = ""
Me.txtFinishDate.Value = ""
Me.txtLorP.Value = ""
Me.txtBargeID.SetFocus

End Sub

--

Dave Peterson

The case of the name (Mar 07 or MAR 07 or MaR 07 or...) wouldn't matter. But
when you changed the case, you fixed the other (unknown) problem.

tankerman wrote:

I had the sheet name in caps when I changed the name case it works fine

Thanks everyone for the help.
"Dave Peterson" wrote:

The subscript out of range error (9) means that there is no worksheet in that
workbook that has the name you're using.

So either you don't have a "Mar 07" worksheet--or you're in the wrong workbook.

Maybe...

Set ws = Worksheets("MAR 07")
should be
Set ws = thisworkbook.Worksheets("MAR 07")

Or it could be a simple spelling error (extra spaces in that name???).

tankerman wrote:

I have a VB in my workbook but I'm having trouble with it (I did not write
this I just made some changes to fit my workbook). I have 26 different sheets
(one for each months individual product movements-we track start and finish
time , one for each months totals, we track the number of times the 30 or so
different products were handled each months (I have a SUMPRODUCT taking care
of this part), plus we have a sheet totalling the years total of all
movements. It took me a month to get all of this setup, what I am trying to
do is have a USERFORM to fill out all of the info of the movements because we
have 15 different people filling out the movement at different times because
we are on a shared drive.

PROBLEM I keep getting a RUN-TIME ERROR '9' when I debug it highlights a
line in me VB, it is the fourth row down. My workbook sheets are like this
JAN 07, JAN TOTALS, FEB 07, FEB TOTALS... plus our total tally sheets.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MAR 07")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a barge number
If Trim(Me.txtBargeID.Value) = "" Then
Me.txtBargeID.SetFocus
MsgBox "Please enter a barge name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtBargeID.Value
ws.Cells(iRow, 2).Value = Me.txtProd.Value
ws.Cells(iRow, 3).Value = Me.txtStartDate.Value
ws.Cells(iRow, 4).Value = Me.txtFinishDate.Value
ws.Cells(iRow, 9).Value = Me.txtLorP.Value

'clear the data
Me.txtBargeID.Value = ""
Me.txtProd.Value = ""
Me.txtStartDate.Value = ""
Me.txtFinishDate.Value = ""
Me.txtLorP.Value = ""
Me.txtBargeID.SetFocus

End Sub

--

Dave Peterson


--

Dave Peterson

Thanks everyone for the help
"Dave Peterson" wrote:

The case of the name (Mar 07 or MAR 07 or MaR 07 or...) wouldn't matter. But
when you changed the case, you fixed the other (unknown) problem.

tankerman wrote:

I had the sheet name in caps when I changed the name case it works fine

Thanks everyone for the help.
"Dave Peterson" wrote:

The subscript out of range error (9) means that there is no worksheet in that
workbook that has the name you're using.

So either you don't have a "Mar 07" worksheet--or you're in the wrong workbook.

Maybe...

Set ws = Worksheets("MAR 07")
should be
Set ws = thisworkbook.Worksheets("MAR 07")

Or it could be a simple spelling error (extra spaces in that name???).

tankerman wrote:

I have a VB in my workbook but I'm having trouble with it (I did not write
this I just made some changes to fit my workbook). I have 26 different sheets
(one for each months individual product movements-we track start and finish
time , one for each months totals, we track the number of times the 30 or so
different products were handled each months (I have a SUMPRODUCT taking care
of this part), plus we have a sheet totalling the years total of all
movements. It took me a month to get all of this setup, what I am trying to
do is have a USERFORM to fill out all of the info of the movements because we
have 15 different people filling out the movement at different times because
we are on a shared drive.

PROBLEM I keep getting a RUN-TIME ERROR '9' when I debug it highlights a
line in me VB, it is the fourth row down. My workbook sheets are like this
JAN 07, JAN TOTALS, FEB 07, FEB TOTALS... plus our total tally sheets.

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MAR 07")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a barge number
If Trim(Me.txtBargeID.Value) = "" Then
Me.txtBargeID.SetFocus
MsgBox "Please enter a barge name"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtBargeID.Value
ws.Cells(iRow, 2).Value = Me.txtProd.Value
ws.Cells(iRow, 3).Value = Me.txtStartDate.Value
ws.Cells(iRow, 4).Value = Me.txtFinishDate.Value
ws.Cells(iRow, 9).Value = Me.txtLorP.Value

'clear the data
Me.txtBargeID.Value = ""
Me.txtProd.Value = ""
Me.txtStartDate.Value = ""
Me.txtFinishDate.Value = ""
Me.txtLorP.Value = ""
Me.txtBargeID.SetFocus

End Sub

--

Dave Peterson


--

Dave Peterson