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

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
 
E

Erin Klanderman

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...
 
I

isabelle

hi,

if the button is on a Excel spreadsheet

ActiveSheet.CommandButton1.TakeFocusOnClick = False
 
D

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

Similar Threads

User Form's 1
USERFORM's 0
How to insert row after data from user form 1
Userform VBA Complie Error: Method or data memeber not found 3
User form with VBA 10
listbox -shows table of information - option to select and edit ro 2
User input from excel form not populating Access Database 3
Option Button in User Form 9

Top