Error while creating a table style in excel
- by Rashmi Pandit
Hi,
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.
