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


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 = ""

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

Erin Klanderman


did you put the buttons property "TakeFocusOnClick" to false ?

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



if the button is on a Excel spreadsheet

ActiveSheet.CommandButton1.TakeFocusOnClick = False

Dave Peterson

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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question