Error 9 - Subscript out of range

[jerry]

I am using a form for the users to fill in. The values filled in by
user is populated in a sheet when the user clicks submit button . This
sheet alone is emailed using the mail system.

This code works for all except one user. That user keeps getting Error
9 ( subscript out of range)/ 91 (object variable or with block
variable not set) and the debug cursor points to worksheet select
code.

Workbooks("Form.xls").Sheets("sheet2").Activate

'Delete values in column B if there is any data
Columns("B:B").Select
Selection.ClearContents

Range("B1").Select
ActiveCell.Value = txtname.Value

Not sure what is wrong. May i request your help.

Thanks

 

[Patrick Molloy]

with Workbooks("Form.xls").Sheets("sheet2")
.Columns("B:B").ClearContents
.Range("B1").Value = txtname.Value
End WIth

this code is tidier anyway
presumably the workbook name is correct and the sheet name too?

 

[Jim Thomlinson]

Subscript out of range means that you are asking the code to take you to a
place that does not exist or can not be accessed. Assuming your problem is on
this line

Workbooks("Form.xls").Sheets("sheet2").Activate

Then my guess would be
1. The workbook name is changed
2. The sheet name is changed
3. The sheet is hidden
4. Form.xls is not the active workbook when this code is executed.