Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VBA - On Open Go To Worksheet "Home"

Posted on 2014-02-05
4
Medium Priority
?
704 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
[X]
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
  • 2
4 Comments
 
LVL 20

Accepted Solution

by:
ltlbearand3 earned 1000 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 1000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

715 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