none
Excel Macro/VBA help RRS feed

  • Question

  • The below Macro works great unless I try to add a 3rd case statement it keeps erroring out and I don't know why.  I have setup 3 custom views in my spreadsheet.

    Sub DropDown1_Change()
        Dim c As ControlFormat
        Set pay = ActiveSheet
        Set c = pay.Shapes("Drop Down 7").ControlFormat '
          
        Select Case c.Value
        Case 1: T1
        Case 2: F1
     End Select
    
    End Sub
    Sub T1()
        ActiveWorkbook.CustomViews("PayrollT").Show
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    End Sub
    Sub F1()
        ActiveWorkbook.CustomViews("PayrollF").Show
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    End Sub
    

    now if I used the above exactly as it is and add a 3rd case statement it fails.

    Sub DropDown1_Change()
        Dim c As ControlFormat
        Set pay = ActiveSheet
        Set c = pay.Shapes("Drop Down 7").ControlFormat '
         
        
        Select Case c.Value
        Case 1: T1
        Case 2: F1
        Case 3: A1
     End Select
    
    End Sub
    Sub T1()
        ActiveWorkbook.CustomViews("PayrollT").Show
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    End Sub
    Sub F1()
        ActiveWorkbook.CustomViews("PayrollF").Show
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    End Sub
    Sub A1()
        ActiveWorkbook.CustomViews("PayrollA").Show
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    End Sub

    Now I get a error that says:

    Run-time error '5':

    Invalid Procedure call or argument

    It highlights the line that says

    "ActiveWorkbook.CustomViews("PayrollA").Show"

    Thursday, April 23, 2015 6:23 PM

All replies

  • Hi,

    Can't comment on the CustomViews code, but I would not expect the Select case statement to work properly at all. Firstly the Case statement must resolve to a string or numeric expression. T1, F1 and A1 are subs. They are also valid cell addresses. so anything can be happening!

    I would try replacing the Case 1:T1 with a valid string or number, EG Case "True"

    If you need values returned from the subs, then convert them to Functions with names that are not valid cell addresses, EG Function T1Test() as String


    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, April 24, 2015 12:09 AM