Solved

VBA - On Open Go To Worksheet "Home"

Posted on 2014-02-05
4
594 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 80

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now