dont understand compile error

[N E Body]

Hello everyone

I am trying to change the name of the open workbook to include th
textvalue of a cell. I am receiving a compile error when I add thi
line to my Before close code

ThisWorkbook.Name = "Defects " & Lists.Range("Q20").Value & ".xls"

The Compile Error states - Cant assign to read-only property.

.Name is highlighted

Where am I going wrong?

Kenny

Excel 9

 

[Chip Pearson]

The Name property of the Workbook property is the file name of
the xls file, and is a read only property. To change the name,
use the SaveAs method to save the file.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[N E Body]

Sorry Chip I think it must be Friday afternoon syndrome but I cannot ge
my head round this one!

I tried changing it to ThisWorkbook.SaveAs and ActiveWorkbook.SaveA
but still get various errors.

Kenn

 

[Chip Pearson]

Post the code you are using with the SaveAs method, and describe
in detail the errors you are getting.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[JE McGimpsey]

You can't change the name of a workbook by changing the .Name property.

The .Name property is read-only. It is set to the filename when you save
the file. If you want to change the workbook name, you need to save the
file under that name, e.g..

ThisWorkbook.SaveAs FileName:="Defects " & Lists.Range("Q20").Value

This will save the file in the current directory. See SaveAs in VBA Help
for more.