Run-time error: 9 Subscript Out of Range

[Mooseman60]

:banghead:Can anyone tell me why this error Run-time error: 9 Subscript Out of Range
comes up on this line of code in the attached code
Workbooks("Defined Name Lists.xls").Worksheets("ContractorsDetails").Range("A2").Value = txtContractorsName.Text

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Workbooks("Defined Name Lists.xls").Worksheets("ContractorsDetails").Range("A2").Value = txtContractorsName.Text

'find first empty row in database
Set ws = Workbooks("Defined Name Lists.xls").Worksheets("ContractorsDetails")
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a ContractorsName
If Trim(Me.txtContractorsName.Value) = "" Then
Me.txtContractorsName.SetFocus
MsgBox "Please enter a Contractors Name"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtContractorsName.Value
'clear the data
Me.txtContractorsName.Value = ""
Me.txtContractorsName.SetFocus
End Sub

[Bob Phillips]

Either the workbook is not open or that worksheet does not exist?

[shrivallabha]

I will refer to the old post I had made about a solution on this particular error
http://www.vbaexpress.com/forum/showthread.php?t=32359

You will have to change explorer settings as shown or remove the extension (.xls) like below. Let me know if this removes the error.



Workbooks("Defined Name Lists").Worksheets("ContractorsDetails").Range("A2").Value = txtContractorsName.Text

[Mooseman60]

How do I get the workbook to open when I open my Template workbook

[Bob Phillips]

Workbooks.Open(filename)?