Running a macro through Terminal Server

Costas · May 28, 2015

Hi Chandoo,

I'm running some code on my PC and it works fine but when I try to run the same code through a Terminal Server, I get this error message when I reach the FormatConditions.Add line.

Below is an extract of my code

Code:

With Range("B" & MyFirstRow, "B" & MyMidRow - 1)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=isnumber(search(""Not"",$B" & MyFirstRow & "))"
        .FormatConditions(1).Interior.ColorIndex = 3 'Red
End With

Have you ever come across something like this before?

Thanks
Costas

Hui · May 28, 2015

Does the server version of Excel have VBA installed?

Costas · May 28, 2015

Yes and I have been using it on other macros and have never come across this problem.

Hui · May 28, 2015

Can you share the file or the macro ?

NARAYANK991 · May 28, 2015

Hi Costas ,

Going by your post that the error is on the line where a format condition is being added , it may be that the Excel version which is installed does not support the number of format conditions. How many do you have in your code ?

Narayan

Costas · May 28, 2015

Hi Hui,

I'm calling the ConditionFormat macro during my on open procedure. Below is the code for both, a bit long.

Code:

Sub Workbook_Open()
    Dim MyUser, MyName, MyAccess As String
    Dim MyTable As Range
    Dim MySheet As Worksheet
    Dim MyBook As Workbook
   
    Application.ScreenUpdating = False
   
    Get_User_Name lpBuff, 25
    Set MyTable = Range("Tbl_Users")
    MyUser = GetUserName
    MyName = Application.WorksheetFunction.VLookup(MyUser, MyTable, 2, False)
    MyAccess = Application.WorksheetFunction.VLookup(MyUser, MyTable, 3, False)
   
    Set MyBook = ActiveWorkbook
    UnprotectWorkbook
    Select Case MyAccess
 
    Case "Full"
        For Each MySheet In MyBook.Worksheets
            MySheet.Visible = True
            MySheet.Unprotect Password:=DontTell
            MySheet.Range("A:CB").EntireColumn.Hidden = False
            MySheet.Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
        Next MySheet
       
    Case "All branches"
        For Each MySheet In MyBook.Worksheets
                MySheet.Visible = True
            If MySheet.Name = "Summary" Or MySheet.Name = "List" Or _
                MySheet.Name = "SYD_P" Or MySheet.Name = "SYD_R" Or MySheet.Name = "SYD_S" Or _
                MySheet.Name = "SYD" Or MySheet.Name = "MEL" Or MySheet.Name = "BRS" Then
                MySheet.Visible = True
                MySheet.Unprotect Password:=DontTell
                MySheet.Range("A:CB").EntireColumn.Hidden = False
                MySheet.Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
            Else
                MySheet.Visible = False
            End If
        Next MySheet

    Case "Summary"
        For Each MySheet In MyBook.Worksheets
            MySheet.Visible = True
            If MySheet.Name <> "Summary" Or MySheet.Name <> "List" Then MySheet.Visible = False
        Next MySheet
        Sheets("List").Unprotect Password:=DontTell
        Sheets("List").Cells.EntireColumn.Hidden = False
        Sheets("List").Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
   
    Case "SYD_All"
        For Each MySheet In MyBook.Worksheets
                MySheet.Visible = True
            If MySheet.Name = "SYD_P" Or MySheet.Name = "SYD_R" Or MySheet.Name = "SYD_S" Or MySheet.Name = "SYD" Then
                MySheet.Visible = True
                MySheet.Unprotect Password:=DontTell
                MySheet.Range("A:BQ,BX").EntireColumn.Hidden = False
                MySheet.Range("BQ:BW,BY:BY,CA:CA").EntireColumn.Hidden = True
                MySheet.Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
            Else
                MySheet.Visible = False
            End If
        Next MySheet 
 
    Case Else
        For Each MySheet In MyBook.Worksheets
            MySheet.Visible = True
            If MySheet.Name <> MyAccess Then MySheet.Visible = False
            MySheet.Unprotect Password:=DontTell
            MySheet.Range("A:BQ,BX:BX").EntireColumn.Hidden = False
            MySheet.Range("BQ:BW,BY:CA").EntireColumn.Hidden = True
            MySheet.Protect Contents:=True, AllowFormattingColumns:=False, AllowFiltering:=True, userInterfaceOnly:=True, Password:=DontTell
        Next MySheet

    End Select
    Sheets("Sheet1").Visible = False
    ProtectAllSheets
    ProtectWorkbook
    'Activate timesheet
    Sheets(Range("N_Branch").Value).Activate
    ConditionFormat
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Sub ConditionFormat(Optional CondiForm As String)
   
    Sheets(Range("N_Branch").Value).Activate
    UnprotectSheet
   
'  Find First, Mid and Last rows
    Set FindRow = Columns("A:A").Find(What:="Week ending:", LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    MyFirstRow = FindRow.Row + 2
    Set FindRow = Columns("A:A").Find(What:="FROM OTHER BRANCHES:", LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    MyMidRow = FindRow.Row
    Set FindRow = Columns("A:A").Find(What:="TOTAL", LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
    MyLastRow = FindRow.Row - 2

'  Delete all conditional formatting rules in sheet
   
    Cells.FormatConditions.Delete

'  Conditional formatting for duplicate names
    With Range("A" & MyFirstRow, "A" & MyLastRow)
        .FormatConditions.AddUniqueValues
        With .FormatConditions.AddUniqueValues
            .DupeUnique = xlDuplicate
            .Interior.ColorIndex = 3 'Red
        End With
    End With
'  Conditional formatting for other branch employees shown in branch
    With Range("B" & MyFirstRow, "B" & MyMidRow - 1)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=isnumber(search(""Not"",$B" & MyFirstRow & "))"
        .FormatConditions(1).Interior.ColorIndex = 3 'Red
    End With
'  Conditional formatting for Branch employees shown as From other branches
    With Range("B" & MyMidRow + 1, "B" & MyLastRow)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=$B$2"
        .FormatConditions(1).Interior.ColorIndex = 3 'Red
    End With
'  Conditional formatting for negative numbers
    With Range("D" & MyFirstRow & ":" & "I" & MyLastRow _
      & "," & "M" & MyFirstRow & ":" & "R" & MyLastRow _
      & "," & "V" & MyFirstRow & ":" & "AA" & MyLastRow _
      & "," & "AE" & MyFirstRow & ":" & "AJ" & MyLastRow _
      & "," & "AN" & MyFirstRow & ":" & "AO" & MyLastRow _
      & "," & "AS" & MyFirstRow & ":" & "AT" & MyLastRow _
      & "," & "AX" & MyFirstRow & ":" & "BC" & MyLastRow)
        .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=0"
        .FormatConditions(1).Interior.ColorIndex = 3 'Red
        .FormatConditions(1).Font.ColorIndex = 1 'Black
    End With

'  Conditional formatting for Normal hours
    With Range("D" & MyFirstRow & ":" & "D" & MyLastRow _
      & "," & "M" & MyFirstRow & ":" & "M" & MyLastRow _
      & "," & "V" & MyFirstRow & ":" & "V" & MyLastRow _
      & "," & "AE" & MyFirstRow & ":" & "AE" & MyLastRow _
      & "," & "AX" & MyFirstRow & ":" & "AX" & MyLastRow)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(D4<>0,D4<>$C4)"
        .FormatConditions(2).Interior.ColorIndex = 44 'Orange
    End With
'  Conditional formatting for OTx1.5
    With Range("E" & MyFirstRow & ":" & "E" & MyLastRow _
      & "," & "N" & MyFirstRow & ":" & "N" & MyLastRow _
      & "," & "W" & MyFirstRow & ":" & "W" & MyLastRow _
      & "," & "AF" & MyFirstRow & ":" & "AF" & MyLastRow _
      & "," & "AY" & MyFirstRow & ":" & "AY" & MyLastRow)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(E4<>0,D4<>$C4)"
        .FormatConditions(2).Interior.ColorIndex = 44 'Orange
    End With
 
'  Conditional formatting for OTx2
    With Range("F" & MyFirstRow & ":" & "F" & MyLastRow _
      & "," & "O" & MyFirstRow & ":" & "O" & MyLastRow _
      & "," & "X" & MyFirstRow & ":" & "X" & MyLastRow _
      & "," & "AG" & MyFirstRow & ":" & "AG" & MyLastRow _
      & "," & "AZ" & MyFirstRow & ":" & "AZ" & MyLastRow)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(F4<>0,D4<>$C4)"
        .FormatConditions(2).Interior.ColorIndex = 44 'Orange
    End With
'  Conditional formatting for RDO
    With Range("G" & MyFirstRow & ":" & "G" & MyLastRow _
      & "," & "P" & MyFirstRow & ":" & "P" & MyLastRow _
      & "," & "Y" & MyFirstRow & ":" & "Y" & MyLastRow _
      & "," & "AH" & MyFirstRow & ":" & "AH" & MyLastRow _
      & "," & "BA" & MyFirstRow & ":" & "BA" & MyLastRow)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(G4<>0,D4<>0)"
        .FormatConditions(2).Interior.ColorIndex = 44 'Orange
    End With
'  Conditional formatting for Sick
    With Range("H" & MyFirstRow & ":" & "H" & MyLastRow _
      & "," & "Q" & MyFirstRow & ":" & "Q" & MyLastRow _
      & "," & "Z" & MyFirstRow & ":" & "Z" & MyLastRow _
      & "," & "AI" & MyFirstRow & ":" & "AI" & MyLastRow _
      & "," & "BB" & MyFirstRow & ":" & "BB" & MyLastRow)
        .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(H4<>0,D4<>0)"
        .FormatConditions(2).Interior.ColorIndex = 44 'Orange
    End With
'  Data validation for up to 1 decimal
    With Range("C" & MyFirstRow & ":" & "C" & MyLastRow _
      & "," & "D" & MyFirstRow & ":" & "I" & MyLastRow _
      & "," & "M" & MyFirstRow & ":" & "R" & MyLastRow _
      & "," & "V" & MyFirstRow & ":" & "AA" & MyLastRow _
      & "," & "AE" & MyFirstRow & ":" & "AJ" & MyLastRow _
      & "," & "AN" & MyFirstRow & ":" & "AO" & MyLastRow _
      & "," & "AS" & MyFirstRow & ":" & "AT" & MyLastRow _
      & "," & "AX" & MyFirstRow & ":" & "BC" & MyLastRow).Validation
        .Delete
        .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
        xlGreaterEqual, Formula1:="=MOD(10*D4,1)=0"
        .IgnoreBlank = True
        .InCellDropdown = False
        .InputTitle = ""
        .ErrorTitle = "Too many decimals"
        .InputMessage = ""
        .ErrorMessage = "Only one decimal allowed."
        .ShowInput = False
        .ShowError = True
    End With
    ProtectSheet
    ProtectWorkbook
End Sub

Costas · May 28, 2015

Hi Narayan,

Both excel versions are the same

NARAYANK991 · May 28, 2015

Hi Costas ,

From the code you have posted , there are 9 CF rules you have coded for , out of which only column B has 2 rules.

Can you comment out the section of code which is erroring out , and see whether all other rules are applied ?

That may give a clue as to why the error is being generated.

Narayan

Niels · May 29, 2015

In our office we have the same problems, its like microsoft did an update where they removed all VB(a) commands in office.
It doesnt even recognise any of the most simple commands you try to execute.

We have integrations running in terminal server enviremonts that block everytime you try to execute a chain of commands that use VB(a) commands.

All worked fine before today, in & outside the terminal server.

After installing the updates of yesterday night it only works outside the terminal server.

I think we won't be the only ones... I hope MS fixes this asap.
Many company's relie on this...

Costas · Jun 1, 2015

Hi Narayan,

I tried commenting out the problematic ones and none of the other rules work. Maybe it's an MS bug as Niels suggests?

Costas · Jun 1, 2015

Hi Niels,

Thanks for your reply. Still don't know what's happening. Maybe it's true that MS is responsible.

NARAYANK991 · Jun 1, 2015

Hi Costas ,

It may be as Niels has posted ; why don't you check with your IT department to see whether any updates have been applied in the recent past ? At least to confirm , they can see whether these recent updates can be rolled back ?

Narayan

Running a macro through Terminal Server

Costas

Member

Hui

Excel Ninja

Costas

Member

Hui

Excel Ninja

NARAYANK991

Excel Ninja

Costas

Member

Costas

Member

Attachments

NARAYANK991

Excel Ninja

Niels

New Member

Costas

Member

Costas

Member

NARAYANK991

Excel Ninja