Hi
I have created an addin to delete unused styles. This addin is working fine. It is deleting all unused styles as expected. But the problem is that the styles do not go away until I save the workbook and re-open. Is there any way I can avoid this closing and reopening the workbook an instead the deleted styles go away from the toolbar instantly after deleting the styles?
Below is my addin code:
Sub RemoveStyles()
Dim rg As Range, c As Range
Dim sts As Styles, st As Style
Dim collStyles As Collection
Dim wb As Workbook, ws As Worksheet
Dim vSts() As String
Dim i As Long
'Set wb = ThisWorkbook
Set wb = Workbooks(1)
Set collStyles = New Collection
'Get list of used styles
On Error Resume Next
Application.ScreenUpdating = True
For Each ws In wb.Worksheets
Set rg = ws.UsedRange
For Each c In rg
collStyles.Add Item:=c.Style, Key:=CStr(c.Style)
Next c
Next ws
On Error GoTo 0
'Delete unused styles
'Set sts = ThisWorkbook.Styles
Set sts = Workbooks(1).Styles
For Each st In sts
On Error GoTo DeleteStyle
Debug.Print collStyles.Item(st.Name)
On Error GoTo 0
Next st
'Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
ActiveWorkbook.Activate
Exit Sub
DeleteStyle:
st.Delete
Resume Next
End Sub
I have created an addin to delete unused styles. This addin is working fine. It is deleting all unused styles as expected. But the problem is that the styles do not go away until I save the workbook and re-open. Is there any way I can avoid this closing and reopening the workbook an instead the deleted styles go away from the toolbar instantly after deleting the styles?
Below is my addin code:
Sub RemoveStyles()
Dim rg As Range, c As Range
Dim sts As Styles, st As Style
Dim collStyles As Collection
Dim wb As Workbook, ws As Worksheet
Dim vSts() As String
Dim i As Long
'Set wb = ThisWorkbook
Set wb = Workbooks(1)
Set collStyles = New Collection
'Get list of used styles
On Error Resume Next
Application.ScreenUpdating = True
For Each ws In wb.Worksheets
Set rg = ws.UsedRange
For Each c In rg
collStyles.Add Item:=c.Style, Key:=CStr(c.Style)
Next c
Next ws
On Error GoTo 0
'Delete unused styles
'Set sts = ThisWorkbook.Styles
Set sts = Workbooks(1).Styles
For Each st In sts
On Error GoTo DeleteStyle
Debug.Print collStyles.Item(st.Name)
On Error GoTo 0
Next st
'Application.ScreenUpdating = False
ActiveWorkbook.RefreshAll
ActiveWorkbook.Activate
Exit Sub
DeleteStyle:
st.Delete
Resume Next
End Sub