Default Font when Inserting a new Worksheet in a Workbook

[RexJ]

Using Excel 2010 under Windows 8.
The default font options set in my Excel for New Workbooks are: Body Font (which is Calibri on my computer) and size 11. If I open a new Workbook this font type and size are applied. If I add a new worksheet in this workbook, again this font type and size are applied.
Now for my problem. I have an existing Workbook, with lots of macros and several populated Worksheets. As part of using this Workbook I need to add new Worksheets to it (Insert Worksheet). When I insert a Worksheet, the default font for all the cells is Arial, size 10 (which is also the font used on the existing Worksheets).
Is there a way to change the default font used when Inserting a Worksheet in this Workbook? Where is this information stored? Can I access this information using either the Excel GUI or through VBA?
Thanks in advance for your help.

 

[Michael M]

Have a look at
File >> Options >>General >>When Creating new workbooks.....modify as required.

 

[RexJ]

Michael, Thanks you for the quick reply. Unfortunately this did not address my question / concern. I am looking for a way to change the default font when I add a Worksheet (Insert Worksheet) to an EXISTING Workbook, not a new Workbook. I am assuming the default font information is contained somewhere in the existing Workbook. I'd like to know how to access this information and change it. Thanks.e

 

[Michael M]

Ok, you either use the Default font as applied by windows ( Excel)
OR
Have a look here at a previous thread

http://www.mrexcel.com/forum/excel-questions/367153-change-formatting-new-worksheet.html

 

[Momentman]

I dont know if this is an overkill

But what of this in the "thisworkbook" module

The code runs everytime a new sheet is created. Just an idea

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Set wks = ActiveWorkbook.ActiveSheet
    With wks
        .Cells.Font.Size = 12
        .Cells.Font.Name = "Cambria"
    End With
End Sub

I personally think the template approach is better than using codes

 

[Michael M]

@Momentman....I agree.
I was trying to avoid the NewSheet approach as well.

 

[RexJ]

I want this file to be self-contained. It is distributed to many people in our company, so I don't want to have to distribute and document the installation of a template file. Therefore using the VBA code is better for my use case. This file already contains ~750 lines of code so a few more aren't a big deal.

However, I’m still left with the question where in the Excel Object Model the information is located about the default font used when Inserting Worksheet is performed. I know it is contained in the file somewhere as I have different Workbooks created with different default fonts (in File>>Options>>General) defined at the time the Workbook was created. When Insert Worksheet is used in any of these Workbooks, the original default font in place when that particular Workbook was created is used…..so it has to remember this somewhere.

 

[Michael M]

Have a look here....


Excel, Change default font for new sheet?