run-time error
 

I am creating a form that when you save it it will create
a new sheet in a seperate excel file with the name of the
sheet being one of the user entries....i am having two
problems. the first is that when the user begins to enter
the name (which will be the name of the tab) the other
file opens and every letter that is typed the message
saying file is already open. reopening will cause any
changes to be discarded. reopen anywa? or something like
that. The second is that after the name is entered when i
click the command button to save it i get a run-time error
(9) subscript out of range and when i debug it goes to the
line that references the sheet. the code to create the
sheet is:

Workbooks.Open Filename:="C:\Documents and
Settings\ddd\Desktop\temp.xls"
Sheets("Template").Select
Sheets("Template").Copy After:=Sheets(1)
Sheets("Template (2)").Name = "" & TextBox1.Value & ""
Range("A1:P1").Select
created = True

and the code that references it is

Workbooks.Open Filename:="C:\Documents and
Settings\ddd\Desktop\temp.xls"

Sheets("" & Candidate & "").Select
Cells(1, 2).Select


Any thoughts? Thanks a lot

run-time error
 

Hi Jimmy,

the first is that when the user begins to enter
the name (which will be the name of the tab) the other
file opens and every letter that is typed the message
saying file is already open. reopening will cause any
changes to be discarded. reopen anywa? or something like
that.

Don't put the code in the EditBox_Change event! Leave it until the
button is clicked.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk