Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Opens on Sheet2 when opening workbook

I am trying to help someone figure out why their new workbooks open on Sheet2 instead of Sheet1 when they create a new workbook.

Is there any option to uncheck that may start the new workbook on Sheet2 or could that only be happening with a Workbook_Open event?

I do not have access to the workbook to be able to check myself.

Thank You
Ken


Post your answer or comment

comments powered by Disqus
Hi there.

I know about some of the macros that can be put in the "ThisWorkbook" location so they'll run when a file is opened or closed, or when something changes on a sheet.

Is there a way to have a macro run, not on every open, but only when it's a new workbook being created from someone opening a template file (.xlt). In Word I use "Sub Document_New()" and it does the trick...does Excel have an equivalent and how do I find this sort of thing in the VBA help files?!? I haven't had any luck with it.

I have code that runs in the ThisWorkbook section, set to run when the workbook opens. The purpose of the code is to automatically resize the window for the user upon opening. The code works fine in Excel 2003, but when the workbook is opened in Excel 2010, I get the message:

Run-time error '1004':
Application-defined or object-defined error

How can I get the code to work properly with both instances of Excel 2003 and Excel 2010?
Here is the code:


	VB:
	
 Workbook_Open() 
     'Resizes window upon opening
    Application.Left = 1 
    Application.Top = 1 
    Application.Width = 580 
    Application.Height = 462 
     'Opens the userform when the Workbook is opened
     'UserForm1.Show
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, I made a macro that runs when the workbook is open and the last line of code closes that workbook so I can't get into the workbook to edit the code.

Hi --

I have used

Code:
Private Sub Workbook_Open()
    ThisWorkbook.Sheets("Sheet2").Select
End Sub
to make Excel open on Sheet2. However, Sheet2 is usually divided into vertical panes, and what I would like to tell Excel is to open the worksheet so that the left hand pane shows A1 in the top left corner, and the right hand pane shows Z1 in its top left corner.

So far, my code defaults both panes to loading cell A1 into their top left corners, which means I have to scroll to the right place, every time the workbook opens.

How would I modify/refine my code above to specify how I would like each pane to load?

Thank you!

When I open a file (template) in Excel 2007 from an http address an "Opening..." message is briefly displayed

Workbooks.Open "http://server/folder/filename.xltm"

On other pc's connecting to other servers I do not see this "Opening.." message.
Opening the same file from within Excel File |Open etc does not display this message
No message is displayed using the same code in 2003 on the same pc from the same server.
Does anyone know if this is an Excel or pc setting that I can turn off?
Thanks

I'm using Excel 2003, and every time I save the file, no matter what
worksheet I was last on, when reloading the file, it defaults to opening the
3rd worksheet, and places the focus on the same cell. I want it to open on
the last worksheet that was being viewed/used when the file was last saved,
like previous versions have always done!

Hi All

My problem is that when I open a file, called "A", I want it to automatically open another file, which is shared on the network, called "B". I've tried using the "IsFileOpen" routine and a few other sets of code that I've found on the internet but none of them solve my problem.

If "B" isn't open on any computer and I open "A", there is no problem, and if "B" is already open on my computer when I open "A" again, there is no problem, but if "B" isn't open on my computer and is open on somebody else computer, "B" won't open on mine when I open "A", the code assumes that because "B" is open on someone elses computer, "B" is already open on mine.

Any help would be greatly appreciated.

Cheers
Sean

Using WindowsXP on MSOffice2003.
When opening and closing Microsoft Excel an error message displayed as :
Compile error in hidden module: autoexecnew
DistMon
It does not affet my worksheets but it's a bit irritating to receive this
type of message all the time.
Appreciate if some one can guide me or give me an instruction on how to
clear this type of message.
Thanks you very much.
Regards,
SweeTan

I double click on a spreadsheet and it opens, but it also opens the default
book1.
If I only open excel and close book1 and then open a spreadsheet there is no
issue.

So why is excel wanting to open the default book when opening an already
created excel doc and second, how can I disable or resolve this issue to not
open the book1 when opening a spreadsheet but still have it open book1 when I
simply open excel only?

Thanks in advance,

Newtek

We have a user of our Excel application having problems with other open Excel spreadsheets when opening our application. When the user opens our application, the current excel file closes down (happens approx 25% of the time when opening). Any ideas as to why their other Excel files could be closing down?

I have a project monitoring register which has a normal data spreadsheet on sheet 1 (named Sheet1) and I want it to go automatically on Sheet 2 (with graphs on it) when it's opened. I'm using the following code:

Private Sub Workbook_Activate()
Worksheets(Sheet2).Activate
End Sub

which I thought would do it, but it's not working!

The workbook requires a password to enter it - is this the problem?

Thanks in advance

Mark

I have a workbook that is comprised entirely of formulas which reference to another workbook, pulling corresponding data.

The workbook was created on Excel 2007 and it is saved on our server at work. It seems to work just fine as long as it's opened on a 2007 version, but one guy in our office is using 2010 and every cell N/A's out when I try to open it. Any help here?

The formula:

=IF($BA2="", "", INDEX('P:ProductionMaster Schedule[MASTER SCHEDULE - Copy.xls]MSTRSCHEDULE'!A$1:A$65536, MATCH($BA2, 'P:ProductionMaster Schedule[MASTER SCHEDULE - Copy.xls]MSTRSCHEDULE'!$AQ$1:$AQ$65536, 0)))

Thanks in advance, all.

I am trying to have an operation run when opening the Workbook. The UserForm opens fine, but I get an error when the GoButton_Click sub runs. The help file for the error is not very comprehensive.

The user is promoted to 'Delete' or 'Cancel' before the sheet is deleted. I do want to select 'Delete' for the user, but I'm not sure how to do this.

The error mentioned occurs after the user selects 'Delete'. The error is:
Run-time error '-2147417848 (80010108)':
Automation error
The object envoked has disconnected from its clients.

	VB:
	
 
Private Sub UserForm_Initialize() 
    Me.Choices.List = Array("Choice 1", "Choice 2") 
End Sub 
Private Sub GoButton_Click() 
    If Templates = "Choice 1" Then 
        Sheets("Sheet1").Select 
        ActiveWindow.SelectedSheets.Delete 
        Unload StartForm 
    Else 
        If Templates = "Choice 2" Then 
            Sheets("Sheet2").Select 
            ActiveWindow.SelectedSheets.Delete 
            Unload StartForm 
        Else 
            MsgBox ("Nothing selected.") 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Sorry in advance for asking a stupid question, but I can't get this to work anywhere. What I want to do is have my workbook set the focus to cbxcustomer on Sheet1 when the workbook opens, perhaps Workbook Open Event. I tried using SetFocus, but I always get a compile error, so I tried setting it to Me.cbxcustomer.SetFocus on a worksheet Activate event for Worksheet 1, but that doesn't work either. I don't want to use a userform, I'd rather use the sheet as the userform, but it will not select any of the controls, combobox, textbox, or otherwise. Also, in a related note, I can't make Excel tab through the controls on the sheet. Any ideas? Thanks in advance for the help.

I have a worksheet that contains a macro that opens other workbooks updates a cell and then closes the workbook. The problem is that the workbooks that it updates (this is a maintenance thing to correct formulas in vast numbers of workbooks) contain a macro that fires before closing. This macro of course has variables and set objects, but the variables and set objects come back as equaling nothing. When another workbook is opened by another macro, how do I get the variables to load with it? Independently the workbooks and code work fine, it's just when these workbooks are opened by the maintenance routine that it hangs.

1st code that runs:

	VB:
	
 updatebylist() 
     
    Dim wb As Workbook 
    Dim c As Range 
    Application.ScreenUpdating = False 
    Application.DisplayAlerts = False 
    Dim certrecap As Workbook 
     
    Dim wbMaster As Workbook 
    Dim wbOpen As Workbook 
    Dim ws As Worksheet 
    Dim I As Long 
    Dim pass As String 
    Dim dir As String 
    Dim shtnm As String 
    Dim target As String 
    Dim form As String 
    Dim LastRow As String 
     
    pass = Worksheets(1).Range("c6").Text 
    dir = Worksheets(1).Range("c4").Text 
    shtnm = Worksheets(1).Range("c5").Text 
    target = Worksheets(1).Range("c7").Text 
    form = Worksheets(1).Range("c8").Text 
    LastRow = Range("b" & Rows.Count).End(xlUp).Row 'identifies last row used in colum b
     'Set certrecap = Workbooks.Open("S:West RegionOperationsCertification RecapsCert Recaps 06West Region Cert Recaps
2006.xls")
    Set certrecap = Workbooks.Open("S:West RegionOperationsCertification RecapsCert Recaps 06West Region Cert Recaps
2006.xls") 
    LastRow = Range("a" & Rows.Count).End(xlUp).Row 
    Set Rngrecap = certrecap.Worksheets("Cert Recap").Range("a4:a" & LastRow) 
     
    Workbooks("Update Checklists by List.xls").Activate 
    For Each c In Range("b19:b" & LastRow).Cells 
         'On Error Resume Next
        Set wb = Workbooks.Open(FileName:=dir & c.Value & ".xls") 
         
        Set ws = wb.Worksheets(shtnm) 
        ws.Unprotect Password:=pass 
         'If ws.Range("L3").Text = "1/1/2006" Then
         'ws.Range("L3").Select
         'Selection.Clear
         'Else: End If
         'On Error GoTo 0
        ws.Range(target).formula = form 
        ws.Protect Password:=pass 
        wb.Save 
        wb.Activate 
        wb.Close 
         
    Next c 
    Application.DisplayAlerts = True 
    Application.ScreenUpdating = True 
    MsgBox "All Checklists Have Been Updated" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Code that will not run in opened workbook.

	VB:
	
) 
     
    Dim Rngrecap    As Range 
    Dim Rng2    As Range 
    Dim w As Worksheet 
    Dim c1      As Range, c2 As Range 
    Dim cert    As Workbook 
    Dim certrecap As Workbook 
    Dim lastrow As String 
    Application.ScreenUpdating = False 
    Set cert = ThisWorkbook 
    Set certrecap = Workbooks.Open("S:West RegionOperationsCertification RecapsCert Recaps 06West Region Cert Recaps
2006.xls") 
    lastrow = Range("a" & Rows.Count).End(xlUp).Row 
    Set Rngrecap = certrecap.Worksheets("Cert Recap").Range("a4:a" & lastrow) 
     
     
     
    certrecap.Worksheets("Cert Recap").Unprotect Password:="antler1" 
    For Each c1 In Rngrecap 
         
        If c1.Value = cert.Worksheets("RTV").Range("D2").Value Then 
            cert.Worksheets("RTV").Activate 
            cert.Worksheets("RTV").Range("G135").Select 
            Selection.Copy 
            c1.Offset(, 10).PasteSpecial xlPasteValues 
            cert.Worksheets("RTV").Range("J135").Select 
            Selection.Copy 
            c1.Offset(, 12).PasteSpecial xlPasteValues 
            cert.Worksheets("RTV").Range("L3").Select 
            Selection.Copy 
            c1.Offset(, 9).PasteSpecial xlPasteValues 
            cert.Worksheets("RTV").Range("O3").Select 
            Selection.Copy 
            c1.Offset(, 11).PasteSpecial xlPasteValues 
             
            cert.Worksheets("Inventory Control").Activate 
            cert.Worksheets("Inventory Control").Range("G108").Select 
            Selection.Copy 
            c1.Offset(, 8).PasteSpecial xlPasteValues 
            cert.Worksheets("Inventory Control").Range("L3").Select 
            Selection.Copy 
            c1.Offset(, 7).PasteSpecial xlPasteValues 
             
            cert.Worksheets("Front End").Activate 
            cert.Worksheets("Front End").Range("G217").Select 
            Selection.Copy 
            c1.Offset(, 4).PasteSpecial xlPasteValues 
            cert.Worksheets("Front End").Range("J217").Select 
            Selection.Copy 
            c1.Offset(, 6).PasteSpecial xlPasteValues 
            cert.Worksheets("Front End").Range("L3").Select 
            Selection.Copy 
            c1.Offset(, 3).PasteSpecial xlPasteValues 
            cert.Worksheets("Front End").Range("O3").Select 
            Selection.Copy 
            c1.Offset(, 5).PasteSpecial xlPasteValues 
             
        Else: End If 
             
        Next c1 
        certrecap.Worksheets("Cert Recap").Protect Password:="antler1", AllowFiltering:=True 
        Application.CutCopyMode = False 
        certrecap.Close True 
        Application.ScreenUpdating = True 
    End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I have a large Excel file with large array formulas that take very long time to calculate. So I want to set the calculation mode to manual on this workbook. The trick is that I'd like to have this automatically done when opening the workbook and set back the calculation mode to automatic when closing it.

I tried to add a Application.Calculation=xlmanual instruction on a Workbook_Open() event. This works when the workbook is opened and Excel wasn't launched before. However if another workbook is yet open and the calculation mode was set to automatic, opening the workbook will cause first a complete recalculation and then only the calculation mode is set to manual. This makes each opening sequence pretty long.

A possible trick that I found on the net for this is to open a dummy empty workbook that first set the calculation property to manual and then open the real workbook. This would work but I do not like this option because I have to deal with two files and if either one is renamed or moved (I will not be the only user of the files), everything will get wrong. Moreover if this method works for opening the workbook, it does not work to close it and set back the calculation mode to automatic WITHOUT again performing a calculation of the workbook which again take a long time.

Does it exist something to get around that problem? I have Excel 2000 so I do not have all the calculation control such as calculationState, interruptKey etc that are available only from Excel 2002.

Does somebody know an elegant solution for this problem?

Many thanks

Hi there,

I have a bunch of excel files that I need to administer. The files are all stored in one directory on a server, but are generally opened on different computers for people to enter data.

I need to be able to protect and close the files on Sunday at midnight, and then open a bunch of new ones ready for the new week.

Can anyone suggest how to do this?

I had thought to have an "administration" workbook, that under windows is scheduled to open every sunday night. This workbook would have a macro that would automatically run on opening, which would process all the files.

The problem is, if one or more of the files are open on someone's PC, I get access permission errors when my macro tries to close them ...

any ideas?

Just looking for an understanding of how excel handles this....I have 2 workbooks with identical custom save routines that are sometimes open at the same time...if you have them both open and click the red X to close excel, you get the save prompts...it asks for each open workbook if you want to save before close. If you select no for one of the workbooks things loop a couple of times (you get asked about saving again) before excel closes. What I guess i'm asking is what happens when two workbooks are open that each contain VBA that is event driven?

Code if it helps (this did not start out as mine, but has been modified quite a bit...also, be nice..this is my first attempt at anything like this...and the msgbox stuff is my lame attempt at debugging...I now know there are better ways..we learn all the time!)

Code:
Option Explicit
 
Const WelcomePage = "Macros"
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
     
Dim State As Integer, wb As Workbook, ws As String, Actws As Worksheet
    'Turn off events to prevent unwanted loops
    Application.EnableEvents = False
    'Set active sheet to call later
     ws = ActiveWorkbook.Worksheets(1).Name
For Each wb In Application.Workbooks
         'Evaluate if workbook is P&L KILLER and emulate default propmts
    With wb
        
        If ws = "USER SALES" Then
              On Error GoTo Err1
                MsgBox ("you are in")
            Select Case MsgBox("Do you want to save the changes you made to '" & wb.Name & "'?", _
                vbYesNoCancel + vbExclamation)
            Case Is = vbYes
                 'Customized save routine
                 State = "1"
                 Set Actws = ActiveSheet
                Application.ScreenUpdating = False
                Call HideAllSheets
                wb.Save
                Call ShowAllSheets
                wb.Saved = True
                Actws.Activate
            Case Is = vbNo
                 'Do not save
                 State = "2"
                 wb.Saved = True
            Case Is = vbCancel
                 'Set up procedure to cancel close
                 State = "3"
                 Cancel = True
            End Select
        Else
            'If not P&L KILLER do this
            MsgBox (ws)
            Select Case MsgBox("Do you want to save the changes you made to '" & wb.Name & "'?", _
                vbYesNoCancel + vbExclamation)
            Case Is = vbYes
                 'save
                 State = "1"
                 wb.Save
                 wb.Saved = True
            Case Is = vbNo
                 'Do not save
                 State = "2"
                 wb.Saved = True
            Case Is = vbCancel
                 'Set up procedure to cancel close
                 State = "3"
                 Cancel = True
            End Select
         
        If Not State = "3" Then
            'Close it down
            Application.EnableEvents = True
            wb.Close
            Application.EnableEvents = False
                     
        End If
    End If
    End With
    'Check for another open workbook
    Next wb
    'if cancel is true the following happens
    Application.EnableEvents = True
    Application.ScreenUpdating = True
Exit Sub
Err1:
If ws = ("USER SALES") Then
        Call ShowAllSheets
        MsgBox ("Show sheets called")
    End If
    Cancel = True
    Actws.Activate
    MsgBox ("There was an error while saving.  The workbook is not Saved. Please Retry.")
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
     'Turn off events to prevent unwanted loops
    Application.EnableEvents = False
     
     'Call customized save routine and set workbook's saved property to true
     '(To cancel regular saving)
    Call CustomSave(SaveAsUI)
    Cancel = True
     
     'Turn events back on an set saved property to true
    Application.EnableEvents = True
    ActiveWorkbook.Saved = True
End Sub
Thanks!!

Hi all,

Is there a way to make sure (using VB or other methods) to ensure that when a workbook is opened it always opens on Sheet 1, regardless of which sheet it was saved on before it was closed ??

Thanks in Advance,

D

Hi,
Can anyone tell me the vba code to automatically open sheet one when a workbook is opened? At the moment it opens on the page on which the workbook was last saved.
Thanks

A helpful group member here gave me the following macro, which adds three
selections to the pop-up menu which appears when you right-click on the
spreadsheet:

Sub Add_Controls()
Dim i As Long
Dim onaction_names As Variant
Dim caption_names As Variant
onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")
With Application.CommandBars("Cell")
For i = LBound(onaction_names) To UBound(onaction_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & onaction_names(i)
.Caption = caption_names(i)
End With
Next i
End With
End Sub

It works really well. I've tried modifying it as follows, however, so that
it runs automatically when the workbook opens:

Private Sub Workbook_Open()
Sub Add_Controls()
Dim i As Long
Dim onaction_names As Variant
Dim caption_names As Variant
onaction_names = Array("macro1", "macro2", "macro3")
caption_names = Array("caption 1", "caption 2", "caption 3")
With Application.CommandBars("Cell")
For i = LBound(onaction_names) To UBound(onaction_names)
With .Controls.Add(Type:=msoControlButton)
.OnAction = ThisWorkbook.Name & "!" & onaction_names(i)
.Caption = caption_names(i)
End With
Next i
End With
End Sub

And now it is extremely volatile. Sometimes it adds just one control when I
open the workbook, but more usually it does nothing at all. Sometimes it
crashes while trying to open. Where have I gone wrong? (If it's useful to
know, I'm running this with Excel for Mac 2004 (11.2) on the Tiger OS.)

If anyone can help, I'd be very appreciative!

I know its there somewhere but I cannot find help on how to run a macro
which runs automatically when a workbook is opened and again how to run a
macro when a workbook is closed.
I can't figure out how to run open and close events!
Can anyone get me started?

How can we do to make sure that there’s only one sheet tab (and it is also
the last sheet) showing up on the bottom when we open our workbook? Usually
the first sheet tab, the second and .. ... appear when opening workbook.

Any suggestion? Thanks.

--
Aline Yiu

When opening the workbook, I want the next person to open in a particular
cell in a certain sheet. Not where the last person saved the workbook. I
need to do something on the opening of the workbook, but cannot think. Please
help


No luck finding an answer? You could always try Google.