Solved

VBA - On Open Go To Worksheet "Home"

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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

627 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