Runtime Error 1004:Method 'Range' of object'_Worksheet' Failed

  • Thread starter Thread starter Erin Klanderman
  • Start date Start date
E

Erin Klanderman

I have a s imple program that is executed from a button on an excel
spreadsheet. It is suppose to bring up a user form for the user to
enter data and save to a worksheet in the xls. I can't get it to
run. Here is all of the code:


Private Sub cmdAdd_Click()

Dim iRow As Long
Dim iPart As Long
Dim ws As Worksheet
Set ws = Worksheets("ASAPEngines")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row

iPart = Me.cbostatus.ListIndex


'copy the data to the database
With ws
ws.Cells(iRow, 1).Value = Me.Iusername.Value
ws.Cells(iRow, 2).Value = Me.cbostatus.Value
ws.Cells(iRow, 3).Value = Me.Istore.Value
ws.Cells(iRow, 4).Value = Me.cbomake.Value
ws.Cells(iRow, 5).Value = Me.Imodel.Value
ws.Cells(iRow, 6).Value = Me.Itype.Value
ws.Cells(iRow, 7).Value = Me.Ienginemake.Value
ws.Cells(iRow, 8).Value = Me.Icilit.Value
ws.Cells(iRow, 9).Value = Me.Ifuel.Value
ws.Cells(iRow, 10).Value = Me.Iremarks.Value
ws.Cells(iRow, 11).Value = Me.Iserialnumber.Value
ws.Cells(iRow, 12).Value = Me.Iprice.Value
ws.Cells(iRow, 13).Value = Me.Icore.Value
ws.Cells(iRow, 14).Value = Me.Ihours.Value
ws.Cells(iRow, 15).Value = Me.Iopidle.Value
ws.Cells(iRow, 16).Value = Me.Iopft.Value
ws.Cells(iRow, 17).Value = Me.Iblockcasting.Value
ws.Cells(iRow, 18).Value = Me.Ilocation.Value
ws.Cells(iRow, 19).Value = Me.Istocknumber.Value
End With

'clear the data
Me.Iusername.Value = ""
Me.cbostatus.Value = ""
Me.Istore.Value = ""
Me.cbomake.Value = ""
Me.Imodel.Value = ""
Me.Itype.Value = ""
Me.Ienginemake.Value = ""
Me.Icilit.Value = ""
Me.Ifuel.Value = ""
Me.Iremarks.Value = ""
Me.Iserialnumber.Value = ""
Me.Iprice.Value = ""
Me.Icore.Value = ""
Me.Ihours.Value = ""
Me.Iopidle.Value = ""
Me.Iopft.Value = ""
Me.Iblockcasting.Value = ""
Me.Ilocation.Value = ""
Me.Istocknumber.Value = ""
Me.Iusername.SetFocus


End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub


Private Sub UserForm_Initialize()

Dim cStatus As Range
Dim cMake As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cStatus In ws.Range("StatusList")
With Me.cbostatus
.AddItem cStatus.Value
End With
Next cStatus

For Each cMake In ws.Range("MakeList")
With Me.cbomake
.AddItem cMake.Value
End With
Next cMake

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
 
hi,

did you put the buttons property "TakeFocusOnClick" to false ?
Click to expand...

Im not sure where to do that....can you assist? This is my first
attempt at VB coding and Im totally lost...
 
hi,

if the button is on a Excel spreadsheet

ActiveSheet.CommandButton1.TakeFocusOnClick = False
 
This suggestion fixes a bug in xl97. It was fixed in xl2002.

If you're running xl97, then...

Show the Control toolbox toolbar.
Click on the design mode icon
rightclick on the commandbutton
Choose Properties
Scroll to the .TakeFocusOnClick property and change it to false
Click on the design mode icon to get back to normal.
 
Back
Top