How to open a workbook on a specific sheet everytime?

Chavira55

Board Regular
Joined
Sep 19, 2002
Messages
65
I have my workbook opening on a specific sheet everytime that I don't make any changes to the workbook. ONce I make changes and save them, when I open the workbook again it goes back to the last sheet opened
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
On 2002-09-20 17:33, Chavira55 wrote:
I have my workbook opening on a specific sheet everytime that I don't make any changes to the workbook. ONce I make changes and save them, when I open the workbook again it goes back to the last sheet opened

Hi Chavira55
Welcome to the Board.

What you need is an Event driven macro
Try the following;

<META content=Excel.Sheet name=ProgId><META content="Microsoft Excel 9" name=Generator><LINK href="./sht_files/filelist.xml" rel=File-List><STYLE id=AAA_Instructions_VBAEnter_25753_Styles></STYLE>  <DIV id=AAA_Instructions_VBAEnter_25753 align=center x:publishsource="Excel"><TABLE style="TABLE-LAYOUT: fixed; WIDTH: 456pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=606 border=0 x:str><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16493" width=451><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="WIDTH: 23pt; HEIGHT: 12.75pt" width=30 height=17> </TD><TD class=xl2625753 style="WIDTH: 53pt" width=70> </TD><TD class=xl2625753 style="WIDTH: 338pt" width=451> </TD><TD class=xl2625753 style="WIDTH: 23pt" width=30> </TD><TD class=xl2625753 style="WIDTH: 19pt" width=25> </TD></TR><TR style="HEIGHT: 23.25pt" height=31><TD class=xl2625753 style="HEIGHT: 23.25pt" height=31> </TD><TD class=xl2925753>STEP</TD><TD class=xl3725753><SPAN style="mso-spacerun: yes"> </SPAN>'<FONT class=font1025753> </FONT><FONT class=font725753>HOW<SPAN style="mso-spacerun: yes">  </SPAN></FONT><FONT class=font1125753>J</FONT></TD><TD class=xl2225753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl3525753>1)</TD><TD class=xl3225753 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN>Press <FONT class=font625753>Alt-F11 </FONT><FONT class=font525753>to display the Visual Basic editor (VBE).</FONT></TD><TD class=xl3425753 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3025753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2625753 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3525753>2)</TD><TD class=xl3225753 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN>In the <FONT class=font625753>Project Explorer window</FONT><FONT class=font525753>, find the </FONT><FONT class=font625753>ThisWorkbook</FONT><FONT class=font525753> object. If you don't see the </FONT><FONT class=font625753>Project Explorer window,</FONT><FONT class=font525753> press</FONT><FONT class=font625753> Ctrl-R.</FONT></TD><TD class=xl3425753 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3025753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2625753 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3525753>3)</TD><TD class=xl3225753 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN><FONT class=font625753>Double-click</FONT><FONT class=font525753> the </FONT><FONT class=font625753>Thisworkbook</FONT><FONT class=font525753> object in the </FONT><FONT class=font625753>Project Explorer window</FONT><FONT class=font525753>.</FONT></TD><TD class=xl3425753 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3025753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2625753 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3525753>4)</TD><TD class=xl3225753 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN>Above the code window, you'll find <FONT class=font625753>two drop-down lists</FONT><FONT class=font525753>. In the list on the left, choose </FONT><FONT class=font625753>Workbook</FONT><FONT class=font525753>. In the list on the right, choose</FONT><FONT class=font625753> Open</FONT><FONT class=font525753>.</FONT></TD><TD class=xl3425753 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3025753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl2625753 style="HEIGHT: 38.25pt" height=51> </TD><TD class=xl3525753>5)</TD><TD class=xl3225753 style="WIDTH: 338pt" width=451><SPAN style="mso-spacerun: yes"> </SPAN>A procedure template for<FONT class=font625753> Workbook_Open</FONT><FONT class=font525753> now appears in the code window. Between the</FONT><FONT class=font625753> Private Sub </FONT><FONT class=font525753>line and the</FONT><FONT class=font625753> End Sub</FONT><FONT class=font525753> line, type in your procedure.</FONT></TD><TD class=xl3425753 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3025753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl2625753 style="HEIGHT: 25.5pt" height=34> </TD><TD class=xl3625753>eg.</TD><TD class=xl3225753 style="WIDTH: 338pt" width=451>For examplecode<FONT class=font625753>, for </FONT><FONT class=font525753>your </FONT><FONT class=font625753>Workbook_Open</FONT><FONT class=font525753> procedure would look like this:</FONT></TD><TD class=xl3425753 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl2725753><U style="VISIBILITY: hidden; mso-ignore: visibility"> </U></TD><TD class=xl3025753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 51pt" height=68><TD class=xl2625753 style="HEIGHT: 51pt" height=68> </TD><TD class=xl3625753>Code:</TD><TD class=xl3325753 style="WIDTH: 338pt" width=451>Private Sub<FONT class=font925753> Workbook_Open</FONT><FONT class=font625753>( )
<SPAN style="mso-spacerun: yes">        </SPAN></FONT><FONT class=font925753>Sheets </FONT><FONT class=font625753>("Sheet1").</FONT><FONT class=font925753>Activate</FONT><FONT class=font625753>
</FONT><FONT class=font825753>End Sub</FONT><FONT class=font625753>
</FONT></TD><TD class=xl3425753 style="BORDER-LEFT: medium none">þ</TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2525753> </TD><TD class=xl2325753 style="WIDTH: 338pt" width=451> </TD><TD class=xl3025753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 51pt" height=68><TD class=xl2625753 style="HEIGHT: 51pt" height=68> </TD><TD class=xl2825753> </TD><TD class=xl3225753 style="WIDTH: 338pt" width=451>Just change "Sheet1" to your sheet name.
Note if you want it to always show a particular range then change to;

Application.Goto Sheets("Sheet2").Range("A1"), True</TD><TD class=xl3125753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2525753> </TD><TD class=xl2325753 style="WIDTH: 338pt" width=451> </TD><TD class=xl3025753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2825753> </TD><TD class=xl2425753 style="WIDTH: 338pt" width=451> </TD><TD class=xl3125753> </TD><TD class=xl2625753> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl2625753 style="HEIGHT: 12.75pt" height=17> </TD><TD class=xl2625753> </TD><TD class=xl2625753> </TD><TD class=xl2625753> </TD><TD class=xl2625753> </TD></TR></TBODY></TABLE></DIV>
 

luis.carrion

Board Regular
Joined
Mar 26, 2010
Messages
246
how cant i add the two below macros into one

Private Sub Workbook_Open()
UserForm3.Show 'Where "MYForm" is the name of your form.
End Sub

and

Private Sub Workbook_Open( )
Sheets ("Sheet1").Activate
End Sub
 

DragonWood

Board Regular
Joined
Oct 17, 2010
Messages
97

ADVERTISEMENT

You cannot have two Subs with the same name in a code. You need to either change the name of one, or put them both in the same Sub.
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,893
Code:
Private Sub Workbook_Open()
    Sheets ("Sheet1").Activate
    UserForm3.Show 'Where "MYForm" is the name of your form.
End Sub
 

Secret Chimpanzee

Board Regular
Joined
Jun 21, 2012
Messages
141
Hi,

I'd like to add this code to my workbook, but under "This Workbook", I already have a code. Do you know how I can have this one also included under "This Workbook"?

Hi Chavira55
Welcome to the Board.

What you need is an Event driven macro
Try the following;

****** content=Excel.Sheet name=ProgId>****** content="Microsoft Excel 9" name=Generator><LINK rel=File-List href="./sht_files/filelist.xml"><STYLE id=AAA_Instructions_VBAEnter_25753_Styles></STYLE>**
*****
*STEP*' HOW* J**
*1)*Press Alt-F11 to display the Visual Basic editor (VBE).þ*
*****
*2)*In the Project Explorer window, find the ThisWorkbook object. If you don't see the Project Explorer window, press Ctrl-R.þ*
*****
*3)*Double-click the Thisworkbook object in the Project Explorer window.þ*
*****
*4)*Above the code window, you'll find two drop-down lists. In the list on the left, choose Workbook. In the list on the right, choose Open.þ*
*****
*5)*A procedure template for Workbook_Open now appears in the code window. Between the Private Sub line and the End Sub line, type in your procedure.þ*
*****
*eg.For examplecode, for your Workbook_Open procedure would look like this:þ*
*****
*Code:Private Sub Workbook_Open( )
******* Sheets ("Sheet1").Activate
End Sub
þ*
*****
**Just change "Sheet1" to your sheet name.
Note if you want it to always show a particular range then change to;

Application.Goto Sheets("Sheet2").Range("A1"), True
**
*****
*****
*****

<COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16493" width=451><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><TBODY>
</TBODY>
 

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,893
What code is already in the ThisWorkbook Workbook_Open event?

What exactly do you want the new code to do?
 

Forum statistics

Threads
1,144,326
Messages
5,723,725
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top