How to open a workbook on a specific sheet everytime?

[Chavira55]

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

 

[Ivan F Moala]

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 xublishsource="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>

 

[Chavira55]

Thanks for the prompt answer...it works wonderfully...

 

[luis.carrion]

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]

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]

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

 

[Secret Chimpanzee]

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]

What code is already in the ThisWorkbook Workbook_Open event?

What exactly do you want the new code to do?