Solved

VBA - On Open Go To Worksheet "Home"

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

910 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

22 Experts available now in Live!

Get 1:1 Help Now