How to open a workbook on a specific sheet everytime?

C

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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
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
Click to expand...

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>
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Code: 
Private Sub Workbook_Open()
    Sheets ("Sheet1").Activate
    UserForm3.Show 'Where "MYForm" is the name of your form.
End Sub
 
Upvote 0
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"?

Ivan F Moala said:
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>**
[TABLE="width: 456"]
<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]
[TD="class: xl2625753, width: 30"]*[/TD]
[TD="class: xl2625753, width: 70"]*[/TD]
[TD="class: xl2625753, width: 451"]*[/TD]
[TD="class: xl2625753, width: 30"]*[/TD]
[TD="class: xl2625753, width: 25"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2925753"]STEP[/TD]
[TD="class: xl3725753"]*' HOW* J[/TD]
[TD="class: xl2225753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl3525753"]1)[/TD]
[TD="class: xl3225753, width: 451"]*Press Alt-F11 to display the Visual Basic editor (VBE).[/TD]
[TD="class: xl3425753"]þ[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl3025753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl3525753"]2)[/TD]
[TD="class: xl3225753, width: 451"]*In the Project Explorer window, find the ThisWorkbook object. If you don't see the Project Explorer window, press Ctrl-R.[/TD]
[TD="class: xl3425753"]þ[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl3025753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl3525753"]3)[/TD]
[TD="class: xl3225753, width: 451"]*Double-click the Thisworkbook object in the Project Explorer window.[/TD]
[TD="class: xl3425753"]þ[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl3025753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl3525753"]4)[/TD]
[TD="class: xl3225753, width: 451"]*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.[/TD]
[TD="class: xl3425753"]þ[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl3025753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl3525753"]5)[/TD]
[TD="class: xl3225753, width: 451"]*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.[/TD]
[TD="class: xl3425753"]þ[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl3025753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl3625753"]eg.[/TD]
[TD="class: xl3225753, width: 451"]For examplecode, for your Workbook_Open procedure would look like this:[/TD]
[TD="class: xl3425753"]þ[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl2725753"]*[/TD]
[TD="class: xl3025753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl3625753"]Code:[/TD]
[TD="class: xl3325753, width: 451"]Private Sub Workbook_Open( )
******* Sheets ("Sheet1").Activate
End Sub
[/TD]
[TD="class: xl3425753"]þ[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2525753"]*[/TD]
[TD="class: xl2325753, width: 451"]*[/TD]
[TD="class: xl3025753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2825753"]*[/TD]
[TD="class: xl3225753, 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]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2525753"]*[/TD]
[TD="class: xl2325753, width: 451"]*[/TD]
[TD="class: xl3025753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2825753"]*[/TD]
[TD="class: xl2425753, width: 451"]*[/TD]
[TD="class: xl3125753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
[TR]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[TD="class: xl2625753"]*[/TD]
[/TR]
</TBODY>[/TABLE]
​
Click to expand...
 
Upvote 0
What code is already in the ThisWorkbook Workbook_Open event?

What exactly do you want the new code to do?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
vba enter password everytime i activate workbook
Replies
1
Views
150
daveyc18
D
Y
  • Question Question
Pivot Table deletes on opening of workbook
Replies
3
Views
168
cooleomter
C
A
  • Solved
Copy Entire Workbook, save as sheet name with current date, hide all sheets except one, & attach to email
Replies
2
Views
321
Amo840812
A
P
  • Question Question
How protect file but not ask for a password
Replies
9
Views
288
mumps
mumps
Skrej
  • Question Question
Random page margin spilling over
Replies
4
Views
176
Skrej
Skrej

Forum statistics

Threads
1,222,146
Messages
6,164,231
Members
451,881
Latest member
John kaiser

Share this page

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
Back
Top