Excel UserForm - "Automation Error"

Question

Hello everyone,

I keep getting this error which doesn't give me the chance to debug at all. It's very strange.

Here's what I've found out so far:

When I load the workbook and immediately click the button to show the userform, the error appears. If I load the workbook and view the macro code, it works fine.

Here's the error message I get shown:

I'm honestly at a loose end with this. I know that it's happening in the UserForm_Activate procedure, if that helps.

Thanks,

Daniel

Answer 1

So I've narrowed the error down to this sub. Any thoughts?

This sub creates a hidden folder and stores 2 images in it. Those images are applied to the UserForm. This was working fine but ever since yesterday it's been causing this Automation Error problem.

Private Sub doTask_Charts(WBLoad As Boolean)

    Dim objFSO As Object

    Dim objFolder As Object

    If WBLoad = True Then

        ' Create Charts Folder

        On Error Resume Next

            If Dir(Charts_Path, vbDirectory) = "" Then MkDir Charts_Path

            ' Hide Charts Folder

            Set objFSO = CreateObject("Scripting.FileSystemObject")

            Set objFolder = objFSO.GetFolder(Charts_Path)

            If objFolder.Attributes = objFolder.Attributes And 2 Then

                objFolder.Attributes = objFolder.Attributes Xor 2

            End If

            ' Hide Charts Folder

        On Error GoTo 0

        ' Line Split Chart

        Set ChartToUse = wsCCG.ChartObjects("CR_LineSplitChart")

        ChartToUse.Height = 224

        ChartToUse.Width = 397

        Set ChartExport = ChartToUse.Chart

        PName = Charts_Path & "\CR_LineSplitChart" & ".gif"

        ChartExport.Export Filename:=PName, FilterName:="GIF"

        Me.CR_LineSplitChart.Picture = LoadPicture(PName)

        ' Average KPI Line Split Chart

        Set ChartToUse = wsCCG.ChartObjects("CR_LineKPIChart")

        ChartToUse.Height = 224

        ChartToUse.Width = 397

        Set ChartExport = ChartToUse.Chart

        PName = Charts_Path & "\CR_LineKPIChart" & ".gif"

        ChartExport.Export Filename:=PName, FilterName:="GIF"

        Me.CR_LineKPIChart.Picture = LoadPicture(PName)

    Else

        ' Delete chart file/folder

        Me.CR_LineSplitChart.Picture = Nothing

        Me.CR_LineKPIChart.Picture = Nothing

        Me.I_O_CG_I.Picture = Nothing

        Set objFSO = CreateObject("Scripting.FileSystemObject")

        Set objFolder = objFSO.GetFolder(Charts_Path)

        If objFolder.Attributes = objFolder.Attributes And 2 Then

            objFolder.Attributes = objFolder.Attributes Xor 2

        End If

        If Not Dir(objFolder, vbDirectory) = "" Then

            On Error Resume Next

            Kill objFolder & "\*.*"

            RmDir objFolder

            On Error GoTo 0

        End If

    End If

    Set objFolder = Nothing

    Set objFSO = Nothing

End Sub

Answer 2

IMHO the effort with the hidden folder makes no sense, because you can delete the exported file directly after you loaded the file into the picture.

Try the code below, works on my system with any error, even if I start the Userform in Workbook_Open.

If the issue persists it might be due to your file and the charts are not ready to use if you call the code.

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

A macro to anonymize data in selected cells can be downloaded here:

https://www.dropbox.com/s/rkfxuh85j5wyj9y/modAn...

Andreas.

Option Explicit

Dim wsCCG As Worksheet

Private Sub doTask_Charts(ByVal WBLoad As Boolean)
  Dim PName As String
  Dim ChartExport As Chart
  
  If WBLoad Then
    Set ChartExport = wsCCG.ChartObjects("CR_LineSplitChart").Chart
    PName = Environ$("TEMP") & "\CR_LineSplitChart" & ".gif"
    ChartExport.Export Filename:=PName, FilterName:="GIF"
    Me.CR_LineSplitChart.Picture = LoadPicture(PName)
    Kill PName
    
    Set ChartExport = wsCCG.ChartObjects("CR_LineKPIChart").Chart
    PName = Environ$("TEMP") & "\CR_LineKPIChart" & ".gif"
    ChartExport.Export Filename:=PName, FilterName:="GIF"
    Me.CR_LineKPIChart.Picture = LoadPicture(PName)
    Kill PName
  Else
    Me.CR_LineSplitChart.Picture = Nothing
    Me.CR_LineKPIChart.Picture = Nothing
  End If
End Sub

Private Sub UserForm_Activate()
  Set wsCCG = ActiveSheet
  doTask_Charts True
End Sub

Answer 3

I've used your code and it works perfectly and does what I was originally doing in a much better way, so thank you for that.

Sadly however it's still causing the error. When I load the workbook and click the button it generates the error. It's a strange one.

Sadly I can't post the workbook as it contains too much confidential information.

Answer 4

Since 2010 Excel works more and more asynchronous, means if you see the first screen update, not all objects (worksheet, chart, etc.) are ready to use.

Just an idea, add this loop at the top of your main routine:

  Do While Not Application.Ready
    DoEvents
  Loop

I would guess that the issue comes from the worksheet object...

Do you have any references added to the VBAProject?

Andreas.