Compile Error

**Mooseman60** · 12-04-2010, 09:06 AM

I have a Compile error:
Invalid or unqualified reference

and can't find the problem

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(",")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1)
  .End(xlUp).Offset(1, 0).Row _
  
'check for a Category
If Trim(Me.cboCategory.Value) = "" Then
  Me.cboCategory.SetFocus
  MsgBox "Please Select a Category"
  Exit Sub
End If

'check for a Product
If Trim(Me.txtProduct.Value) = "" Then
  Me.cboCategory.SetFocus
  MsgBox "Please Enter a Product Description"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtProduct.Value
ws.Cells(iRow, 2).Value = Me.cboUnit.Value
ws.Cells(iRow, 3).Value = Me.txtPrice.Value
ws.Cells(iRow, 4).Value = Me.txtLabour.Value



'clear the data
Me.cboCategory.Value = ""
Me.txtProduct.Value = ""
Me.cboUnit.Value = ""
Me.txtPrice.Value = ""
Me.txtLabour.Value = ""

Me.cboCategory.SetFocus

End Sub

**TMS** · 12-04-2010, 09:13 AM

The problem is:

iRow = ws.Cells(Rows.Count, 1)
.End(xlUp).Offset(1, 0).Row _

Should be:

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Or

iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row

Regards

**snb** · 12-04-2010, 09:44 AM

You'd better not show this commanbutton, unless all compulsory data have been entered.

Private Sub cmdAdd_Click()
  sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize=Array(txtProduct.Txt,cboUnit.Value,txtPrice.Txt,txtLabour.Value)
  for j=1 to 4
    controls(choose(j,"cboCategory","txtProduct","cboUnit","txtPrice","txtLabour").Value = ""
  next
End Sub

Private Sub A_check()
  cmdAdd.visible=cboCategory.value<>"" and txtProduct.Text<>"" and cboCategory.value<>""
End Sub

Private Sub txtProduct_Change()
  A_check
End Sub

Private Sub cboCategory_Change()
  A_check
End Sub

**Mooseman60** · 12-04-2010, 10:07 AM

I entered your code but got a syntax error on this line of code

controls(choose(j,"cboCategory","txtProduct","cboUnit","txtPrice","txtLabour").Value = ""

Private Sub cmdAdd_Click()
  Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize = Array(txtProduct.Value, cboUnit.Value, txtPrice.Value, txtLabour.Value)
  For j = 1 To 4
    controls(choose(j,"cboCategory","txtProduct","cboUnit","txtPrice","txtLabour").Value = ""
  Next
End Sub

Private Sub A_check()
  cmdAdd.Visible = cboCategory.Value <> "" And txtProduct.Text <> "" And cboCategory.Value <> ""
End Sub

Private Sub txtProduct_Change()
  A_check
End Sub

Private Sub cboCategory_Change()
  A_check
End Sub

**snb** · 12-04-2010, 11:40 AM

Then you'll have to debug it (Lesson 1 of programming).
Clue: the number of ( should equal the number of ).

**Mooseman60** · 12-04-2010, 09:48 AM

I now have an error on this line of code

Set ws = Worksheets(",")

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(",")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  
'check for a Category
If Trim(Me.cboCategory.Value) = "" Then
  Me.cboCategory.SetFocus
  MsgBox "Please Select a Category"
  Exit Sub
End If

'check for a Product
If Trim(Me.txtProduct.Value) = "" Then
  Me.cboCategory.SetFocus
  MsgBox "Please Enter a Product Description"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtProduct.Value
ws.Cells(iRow, 2).Value = Me.cboUnit.Value
ws.Cells(iRow, 3).Value = Me.txtPrice.Value
ws.Cells(iRow, 4).Value = Me.txtLabour.Value



'clear the data
Me.cboCategory.Value = ""
Me.txtProduct.Value = ""
Me.cboUnit.Value = ""
Me.txtPrice.Value = ""
Me.txtLabour.Value = ""

Me.cboCategory.SetFocus

End Sub