asked on

VBA Error when running Excel on Windows Terminal Server 2003

The following error appears when loading an Excel workbook with quite a bit of code behind it:
Invalid procedure call or argument (Error 5)

The error only appears when launching the file in a Citrix/Windows 2003 Terminal Services session. Ordinarily, when the file is launched under Windows XP, the code runs without issue.

We are standardized on Excel XP SP3.

The offending line of code appears to be:
Application.CommandBars.Add(Name:=MacroToolbarName).Visible = True

Thanks for your help!!

Sub zMacroToolbarManager()
 
    Const MacroToolbarName As String = "JSA"
    
    Dim ToolbarItem As Object
    Dim ToolbarFound As Integer
    
    ' See if the toolbar exists
    On Error Resume Next
        Set ToolbarItem = CommandBars(MacroToolbarName).Controls(1)
        ToolbarFound = Err
    On Error GoTo 0
    
    ToolbarFound = (ToolbarFound = 0)
    
    If (ToolbarFound = False) Then
        Application.CommandBars.Add(Name:=MacroToolbarName).Visible = True
    Else
        For Each ToolbarItem In CommandBars(MacroToolbarName).Controls
            ToolbarItem.Delete
        Next ToolbarItem
    End If

Open in new window

Wayne Taylor (webtubbs)

Try this instead.....

    Const MacroToolbarName As String = "JSA"
    Dim ToolbarObject As CommandBar
    On Error Resume Next
    Set ToolbarObject = Application.CommandBars(MacroToolbarName)
    If Err <> 0 Then
        Set ToolbarObject = Application.CommandBars.Add(Name:=MacroToolbarName)
        ToolbarObject.Visible = True
    Else
        For Each ToolbarItem In CommandBars(MacroToolbarName).Controls
            ToolbarItem.Delete
        Next ToolbarItem
    End If
    Err.Clear
    On Error GoTo 0

Open in new window

Cláudio Rodrigues

Another question is, does this happen when the user has administrator rights on the Citrix/TS session?

Cláudio Rodrigues

Microsoft MVP
Windows Server - Terminal Services

swamitommi

ASKER

Yes. It happens regardless of the user's rights on the server.

swamitommi

ASKER

Thanks for the code snippet, webtubbs! Of course, now I have a new error: the good ol' "Can't find project or library". Seems not to like CommandBar. Funny thing is, when I run through the Object Browser, nothing is shown as missing. The CommandBar class is, in fact, right there. Hmmm...

swamitommi

ASKER

Here's a new one, coming off the code snippet you gave me, webtubbs: "Compile error: For Each control variable must be Variant or Object". This is coming off of ToolbarItem in the For Each.

ASKER CERTIFIED SOLUTION

swamitommi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial