Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VBA - On Open Go To Worksheet "Home"

Posted on 2014-02-05
4
635 Views
Last Modified: 2014-02-12
Hello Experts,

I would like my workbook to always open up to worksheet name "Home" whenever the workbook is opened.  Is this possible with VBA?

Thank you in advance for your help!

~ Geekamo
0
Comment
Question by:Geekamo
  • 2
4 Comments
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 250 total points
ID: 39837782
Sure.  Just open up the VBA Editor in your workbook (Alt-F11)
Then click on the ThisWorkbook module
There will be a dropdown that says "General" Click that drop down and choose Workbook

This will start a workbookopen subroutine.

Just copy and paste this code in that window over top of the code it has:
Private Sub Workbook_Open()
    Dim objHome As Worksheet
    Set objHome = ActiveWorkbook.Worksheets("Home")
    objHome.Activate
End Sub

Open in new window


That should do the trick for you.
0
 
LVL 1

Author Comment

by:Geekamo
ID: 39837793
@ ltlbearand3,

While I was waiting for an expert to respond to this question, I stumbled upon a line of code - that I placed into my already existing Workbook Open code.

Private Sub Workbook_Open()
wsTTC.Protect UserInterfaceOnly:=True

Splash.Show

Worksheets("Home").Select

End Sub

Open in new window


I do prefer the code that I stumbled upon, only because it's just one line of code and it works.

But are there any benefits to using your code, over the one I found?

Let me know your thoughts.

Thanks
0
 
LVL 20

Expert Comment

by:ltlbearand3
ID: 39837799
Your code will work fine.  That method just does not show up when using intellinsense and I just prefer when possible to use objects that do show up as they are easier to remember what I did and figure stuff out latter when I need to review my code.
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 250 total points
ID: 39837907
Since you are already comfortable using codenames for the worksheets, I suggest that you do likewise with worksheet Home.

You will also want to put the sheet select statement before the one showing the userform. Otherwise, you will see the userform against a different worksheet, and only go to worksheet Home after you have dismissed the userform.
Private Sub Workbook_Open()
wsTTC.Protect UserInterfaceOnly:=True
wsHome.Select           'I'm guessing on the codename here
Splash.Show
End Sub 

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question