Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled

[Historik]

Hi,

I have a problem with automating and refreshing my TFS (Team Foundation Server addin tool for Excel). The code itself is actually working and running a public sub is also refreshing my input live.

However, when I want to automate this with an ontime code, it's not working. Giving the error "Cannot run the macro "macroname". The macro may not be available in this workbook or all macros may be disabled".

I've pasted the following code in Sheet2 (Microsoft Objects)

Private Sub RefreshTeamQueryOnWorksheet(worksheetName As String)

    Dim activeSheet As Worksheet
    Dim teamQueryRange As Range
    Dim refreshControl As CommandBarControl


    Set refreshControl = FindTeamControl("IDC_REFRESH")


    If refreshControl Is Nothing Then
        MsgBox "Could not find Team Foundation commands in Ribbon. Please make sure that the Team Foundation     Excel plugin is installed.", vbCritical
        Exit Sub
    End If


    ' Disable screen updating temporarily so that the user doesn’t see us selecting a range
    Application.ScreenUpdating = False


    ' Capture the currently active sheet, we will need it later
    Set activeSheet = ActiveWorkbook.activeSheet
    Set teamQueryRange = Worksheets(worksheetName).ListObjects(1).Range


    teamQueryRange.Worksheet.Select
    teamQueryRange.Select
    refreshControl.Execute


    activeSheet.Select


    Application.ScreenUpdating = True
End Sub

Private Function FindTeamControl(tagName As String) As CommandBarControl


    Dim commandBar As commandBar
    Dim teamCommandBar As commandBar
    Dim control As CommandBarControl


    For Each commandBar In Application.CommandBars
        If commandBar.Name = "Team" Then
            Set teamCommandBar = commandBar
            Exit For
        End If
    Next


    If Not teamCommandBar Is Nothing Then
        For Each control In teamCommandBar.Controls
            If InStr(1, control.Tag, tagName) Then
                Set FindTeamControl = control
                Exit Function
            End If
        Next
    End If
End Function

Public Sub UpdateTFS()
RefreshTeamQueryOnWorksheet ("Sheet2")
End Sub

I've inserted a module with the following code run the UpdateTFS code per 10 minutes.

sub test()
Application.OnTime Now + TimeValue("00:10:00"), "UpdateTFS" 
end sub

Any suggestions?

 

[wideboydixon]

Try putting the name of the Sheet code module that contains the macro in front of it like this:

Application.OnTime Now + TimeValue("00:10:00"), "Sheet1.UpdateTFS"

The name is the one next to the icon and not the one in brackets (which is the display name of the sheet).

WBD

 

[Historik]

Wow.. totally overlooked that part. Works perfectly now.

Thanks WBD!

Topic can be closed

 

[GTO]

Hi there,

At least as shown, you could also ditch UpdateTFS() by including the argument with OnTime. Please note the doubled-up quote marks as well as the single quotes included.

  Application.OnTime Now + TimeSerial(0, 10, 0), "'Sheet2.RefreshTeamQueryOnWorksheet ""Sheet2""'"

Hope that helps,

Mark