Activate worksheet with custom view?

[Ironman]

Hi

I have a custom view named "All Routes List"

I'm trying to activate this view when the worksheet named "Analysis" is opened.

I've tried inserting the below code in the worksheet:

Private Sub Worksheet_Activate()
CustomViews("All Routes List").Show
End Sub

And it returns "Compile error - Sub or function not defined"

Please can you help?

Thank you.

 

[Michael M]

So, does this work....but shouldn't custom views have no spaces or underscores??

Private Sub Worksheet_Activate()
CustomView.Show
End Sub

If you need to check more than one view

Try

For Each CustomView In ThisWorkbook.CustomViews
        If CustomView.Name = "All_Route_List") Then
            CustomView.Show
            Exit For
        End If
    Next CustomView

 

[Ironman]

Thanks.

Your first suggestion errored 424 - Object Required.

I renamed the custom view without underscores/spaces as you suggested, although it had worked from the toolbar before I renamed it.

I tried your code but as I don't need to check more than one view I didn't understand how to amend the sub.

Thanks again.

 

[Michael M]

Try

If CustomView.Name = ("All_Route_List") Then
            CustomView.Show
End If

 

[Ironman]

I tried

Private Sub Worksheet_Activate()
        If CustomView.Name = "Analysis_All_Routes_List" Then
            CustomView.Show
        End If
End Sub

and it errored 'Object Required'

 

[Michael M]

This worked for me....

Private Sub MM1()
       CustomViews("Analysis_All_Routes_List").Show 
End Sub

 

[Ironman]

Sure, but I can do that from the Custom View toolbar dropdown.

I wanted this to run on sheet activation.

I tried the below but I got 'Sub or Function Not Defined'

Private Sub Worksheet_Activate()
       CustomViews("Analysis_All_Routes_List").Show
End Sub

 

[Michael M]

OK, try this....

Private Sub Worksheet_Activate()
       ThisWorkbook.CustomViews("Analysis_All_Routes_List").Show
End Sub

 

[Ironman]

Thanks

Runtime Error 5
Invalid Procedure Call or Argument

 

[Michael M]

Hmm, works fine for me using "Analysis_All_Routes_List" !!
Couple of things to check
1. make sure the syntax of the view is EXACTLY correct, no leading trailling spaces, etc
2. Make sure the sheet in question isn't protected