Default Workbook Settings

[jdellasala]

I like new Workbooks to have some custom Cell formats, a couple of LAMBDA functions, and column widths of 10 and row heights of 15. I have learned that saving a workbook as an Excel Template with the name Book.xltx with those changes opens a new session of Excel of Excel with that template as Sheet1. Creating a new Worksheet didn't carry over those Row and Column template settings until I copied the Book.xltx to Sheet.xltx. Now, the first time I start Excel, everything is exactly how I'd like it.

HOWEVER, new Workbooks during that Excel session and Worksheets in new (unformatted) Workbooks to not carry the changes noted above.

Does anyone know how to change default Workbook settings like Cell Formats, Row and Column sizes, and maybe even have LAMBDA functions automatically loaded for all instances of New Workbooks and New Worksheets? When I select File -> New -> Blank workbook, I'd like the workbook to at least have Columns and Rows set as above, would like Custom Formats, and maybe even a couple of LAMBDA functions.

Right now I don't bother with the Row/Column sizes unless I'm already there, and have Macros to take care of the other two items, but would really like not having to run Macros to get there.

Thanks.

 

[Micron]

Have to admit that I've no experience with xlt's so can't really follow that. So perhaps if your Personal.xlb had code in it, from any open workbook you could open the xlt file with your "standard" sheet (hidden wb would be fine), copy that sheet to the open workbook x times, then delete Sheet1 that the new wb started out with. AFAIK, code in your personal wb can be run from any other wb.

 

[jdellasala]

Have to admit that I've no experience with xlt's so can't really follow that. So perhaps if your Personal.xlb had code in it, from any open workbook you could open the xlt file with your "standard" sheet (hidden wb would be fine), copy that sheet to the open workbook x times, then delete Sheet1 that the new wb started out with. AFAIK, code in your personal wb can be run from any other wb.

The Personal Workbook is a file which is automatically opened when Excel starts. The only reason you can run Macros in it from other workbooks is because the workbook is referenced to that workbook, not unlike having formulas in one workbook can reference cells in another - the filename prefaces the cell / macro. Other than that it has no effect on the format of a new workbook or the format of a workbook when Excel starts.

 

[RoryA]

You can't, as far as I know, affect the format of a workbook you create with File - New - Blank Workbook. Using Ctrl + N instead will respect your template.

 

[jdellasala]

You can't, as far as I know, affect the format of a workbook you create with File - New - Blank Workbook. Using Ctrl + N instead will respect your template.

Actually you can just by opening a template instead of a new blank workbook. That's not the question though. The question specifically is how to set the default Row Height, Column Width, and Font of a new blank workbook. As I said, I found how to get that for a new Workbook when Excel starts, but after that those defaults go back to values I want to replace for new Workbooks and Sheets. I guess my initial question wasn't specific enough.

 

[RoryA]

Actually you can just by opening a template instead of a new blank workbook.

But that is not the same thing and therefore not relevant to what I said.

The question specifically is how to set the default Row Height, Column Width, and Font of a new blank workbook.

I know - that is what I answered. If you use Ctrl + N to create a new workbook instead of File - New - Blank workbook, then it will respect your Book.xltx template.

 

[jdellasala]

But that is not the same thing and therefore not relevant to what I said.



I know - that is what I answered. If you use Ctrl + N to create a new workbook instead of File - New - Blank workbook, then it will respect your Book.xltx template.

Outstanding! I've never used Ctrl N to create a new Workbook. Go figure!

 

[Micron]

Other than that it has no effect on the format of a new workbook or the format of a workbook when Excel starts.

Thanks but I knew that. I was suggesting that code to copy a sheet from the template be placed there so that you could run it from any (esp. new) wb. But you got a better answer and I learned something. Thanks guys!