Activate worksheet with custom view?

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
1,069
Office Version
  1. 365
Platform
  1. Windows
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:

Code:
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.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
So, does this work....but shouldn't custom views have no spaces or underscores??
Code:
Private Sub Worksheet_Activate()
CustomView.Show
End Sub
If you need to check more than one view

Try

Code:
For Each CustomView In ThisWorkbook.CustomViews
        If CustomView.Name = "All_Route_List") Then
            CustomView.Show
            Exit For
        End If
    Next CustomView
 
Upvote 0
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.
 
Last edited:
Upvote 0
Try

Code:
If CustomView.Name = ("All_Route_List") Then
            CustomView.Show
End If
 
Upvote 0
I tried
Code:
Private Sub Worksheet_Activate()
        If CustomView.Name = "Analysis_All_Routes_List" Then
            CustomView.Show
        End If
End Sub
and it errored 'Object Required'
 
Last edited:
Upvote 0
This worked for me....

Code:
Private Sub MM1()
       CustomViews("Analysis_All_Routes_List").Show 
End Sub
 
Last edited:
Upvote 0
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'

Code:
Private Sub Worksheet_Activate()
       CustomViews("Analysis_All_Routes_List").Show
End Sub
 
Upvote 0
OK, try this....

Code:
Private Sub Worksheet_Activate()
       ThisWorkbook.CustomViews("Analysis_All_Routes_List").Show
End Sub
 
Upvote 0
Thanks

Runtime Error 5
Invalid Procedure Call or Argument
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top