A new instance of Excel opens Book, Book1, Book2, etc. however with Book.xlsx in XLSTART I get 'locked for editing' (no network sharing)

cpcp

New Member
Joined
Jul 27, 2010
Messages
5
I've searched the forum on this issue and can only find instances of this error where network sharing is enabled; in my case there is no network sharing (i.e. the Server service is never started). You will be familiar with the behaviour on opening multiple instances of Excel 2010 where the new Blank Workbook shown changes automatically from Book.xlsx to Book2.xlsx then Book3.xlsx and so on. I want to customize the default Blank Workbook (lets call it Zbook.xlsx) and every time I open an instance of Excel have it load the same way as above i.e. Zbook.xlsx, Zbook1.xlsx, Zbook2.xlsx. etc. To achieve this I have tried saving Zbook.xlsx in XLSTART however on the second and subsequent instance of Excel I get the error "Zbook.xlsx is locked for editing". Clearly, on this occasion, Excel can't create a Zbook2.xlsx. Without having to click through some sequence (like File>New>MyTemplates) how can I have Excel automatically open a sequentially numbered Workbook? For example is there a way to permanently change the makeup of the default Blank Workbook? Thank You!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hi,

You should be able to solve this via the use of an AddIn.

1- Create a new blank workbook and place the following code in the ThisWorkbook Module:
Code:
Option Explicit

Private WithEvents AppEvents As Application

Private Sub Workbook_Open()
    Set AppEvents = Application
    If Len(Dir(Application.TemplatesPath & "Zbook.xlt")) = 0 Then
        FileCopy Application.TemplatesPath & "Book1.xlt", Application.TemplatesPath & "Zbook.xlt"
    End If
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.ReadOnly Then Kill Application.TemplatesPath & "Zbook.xlt"
End Sub

Private Sub AppEvents_NewWorkbook(ByVal Wb As Workbook)
    Wb.Close
    Workbooks.Add Application.TemplatesPath & "Zbook.xlt"
End Sub

2- Now set the IsAddin Property of the workbook to TRUE via the VBE Properties Window and then save the workbook as an AddIn *.xla in the default addins folder

3- Close the application.

Next time you launch excel or open a new workbook, the workbook name will default to Zbook1,Zbook2,Zbook3 etc

This should work when working with multiple excel instances as well.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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