Error while creating a table style in excel

Question

I am using the following function to create a TableStyle:

Public Function CreateTableStyle()
ActiveWorkbook.Unprotect

Dim objTS As TableStyle
On Error Resume Next
Set objTS = ActiveWorkbook.TableStyles("MyTableStyle")
On Error GoTo err_CreateTableStyle
If Not objTS Is Nothing Then
    Exit Function
End If

Set objTS = ActiveWorkbook.TableStyles.Add("MyTableStyle")
With ActiveWorkbook.TableStyles("MyTableStyle")
    .ShowAsAvailablePivotTableStyle = True
    .ShowAsAvailableTableStyle = False
End With
With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _
    xlHeaderRow).Font
    .FontStyle = "Bold"
    .TintAndShade = 0
    .ThemeColor = xlThemeColorDark1
End With
With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _
    xlHeaderRow).Interior
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.249946592608417
End With
With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _
    xlTotalRow).Font
    .FontStyle = "Bold"
    .TintAndShade = 0
    .ThemeColor = xlThemeColorDark1
End With
With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _
    xlTotalRow).Interior
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = -0.249946592608417
End With
ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _
    xlSubtotalRow1).Font.FontStyle = "Bold"
With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _
    xlSubtotalRow1).Interior
    .Color = 16764828
    .TintAndShade = 0
End With
ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _
    xlSubtotalRow2).Font.FontStyle = "Bold"
With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _
    xlSubtotalRow2).Interior
    .Color = 16777164
    .TintAndShade = 0
End With

ActiveWorkbook.Protect

Exit Function

err_CreateTableStyle:
    Call Common.ErrRaise(Erl, "Common", "CreateTableStyle", "CreateTableStyle")

End Function

At the line below:

With ActiveWorkbook.TableStyles("MyTableStyle").TableStyleElements( _ xlHeaderRow).Font .FontStyle = "Bold"

I am getting an error:

Run-time error '1004' Unable to set the FontStyle property of the Font class.

Can someone please identify the issue? I am not able to figure why it is not letting me set the property.

Answer 1

XLA's may behave differently some time .... they are loaded/executed when Excel starts, before any sheet (even an empty one) is opened, so an XLA asuming an open book will fail

If this code is intended to set some "default formattings" ...

... I would rather set up the formatting and save the whole sheet as a sheet or workbook template (XLT), then create new files using that template,

... or - again in a template, store the above code and call it from an event macro like Workbook_NewSheet() or Workbook_Open()

Answer 2

Try .font.bold = true