1
Vote

Runtime Error -91 when launching Microsoft Excel 2007 with only this addin enabled

description

We have recently updated RemoveStyles to the latest version from your site. Once t has been installed and we launch Excel 2007 SP3 we are presented with an error that states" Run-Time error '91': Object variable or With block Variable not set.

Once we click on debug it highlights a line that says module1.MyRibbon.Invalidate
Any help would be greatly appreciated as we use this addin on aprox. 350 PCs.

The add-in seems to work when we end the runtime error.

Your help is truly appreciated.

file attachments

comments

dougscobb wrote Jan 9, 2014 at 1:25 PM

I am having the same issue. Has anyone resolved this yet?

wrote Jan 9, 2014 at 2:46 PM

jstulberger wrote Jan 9, 2014 at 2:46 PM

We modified the code to fix this issue..i have attached the file.
We added MyRibbon.Invalidate to the end of the module
Sub RemoveTheStyles(control As IRibbonControl)
    Dim s As Style, i As Long, c As Long
    On Error Resume Next
    If ActiveWorkbook.MultiUserEditing Then
        If MsgBox("You cannot remove Styles in a Shared workbook." & vbCr & vbCr & _
                  "Do you want to unshare the workbook?", vbYesNo + vbInformation) = vbYes Then
            ActiveWorkbook.ExclusiveAccess
            If Err.Description = "Application-defined or object-defined error" Then
                Exit Sub
            End If
        Else
            Exit Sub
        End If
    End If
    c = ActiveWorkbook.Styles.Count
    Application.ScreenUpdating = False
    For i = c To 1 Step -1
        If i Mod 600 = 0 Then DoEvents
        Set s = ActiveWorkbook.Styles(i)
        Application.StatusBar = "Deleting " & c - i + 1 & " of " & c & " " & s.Name
        If Not s.BuiltIn Then
            s.Delete
            If Err.Description = "You cannot use this command on a protected sheet. To use this command, you must first unprotect the sheet (Review tab, Changes group, Unprotect Sheet button). You may be prompted for a password." Then
                MsgBox Err.Description & vbCr & "You have to unprotect all of the sheets in the workbook to remove styles.", vbExclamation, "Remove Styles AddIn"
                Exit For
            End If
        End If
    Next
    Application.ScreenUpdating = True
    Application.StatusBar = False
    MyRibbon.Invalidate
End Sub