Customising template in Excel

  ChrisP 18:43 20 Jul 04
Locked

Sorry if this has been raised before but the Search yielded nothing.

I have Excel2000 with XP Home. I like to change the layout of my default template and also to redraw the buttons. This is easy enough to do on any given worksheet, but I cannot find how to save this so my settings come up every time I open Excel. I am sure it should be easy, but am getting nowhere. I have tried opening, modifying and saving BOOK.XLT. I have also tried to do the same with excel.xlb. But no joy. Help, please.

  Dirk Diggler 18:52 20 Jul 04

There is a website "click here" - forums full of excel experts!

Hope this helps

  ChrisP 00:45 24 Jul 04

Thanks. I have scoured this site but cannot find anything referring to so simple a matter. I have looked in all the forums.

Of course I can use Excel perfectly well without this, but it would be nice to know how to save new buttons on the default template.

  pc moron 01:17 24 Jul 04

Not totally sure about the default path for Excel 2000 but for Office xp the default path is

C:\Program Files\Microsoft Office\Office10\XLStart

So, to modify the default template do the following:

Make any changes you want in the current workbook.

From the Menu Bar select File> Save As.

Select template.xlt and navigate to the default path (above).

Name the template Book.xlt and save it in XLStart. It will open as the default workbook (Book1.xls) everytime you start Excel.

In Excel Help type templates and look for the default path for Office 2000.

  ChrisP 02:28 24 Jul 04

Thanks. That's what I have been doing. If I then close all files and open up again, I have a toolbar with the new button. If I close down Excel, however, the first time I open it afterwards, I get the new button for an instant and it then flashes off to be replaced by the original, unmodified, toolbar. The next time I open Excel, there is no sign of a toolbar with the new button nor can I find any files which contain this. My book.xlt can be either in the MS Office\template folder or in D&S\Owner\App.Data\Ms\execel\XLstart folder; it doesn't seem to matter which. In the latter folder I have two *.xlb files whose function is obscure to me, although I seem to have made them at some time in the past. I do not see any way to save with this extension from Excel. The only way to get to *.xlb is to save as *xls and then change the extension in Windows Explorer.

  pc moron 11:49 24 Jul 04

Have a read of this click here

If you still get the error then post back with more info on what it is exactly that you're trying to do- does it involve VBA?

The .xlb files are where Excel stores its command bars, including any custom toolbars.

  AccessMoron 11:51 24 Jul 04

Going down the pub later?

  ChrisP 15:54 24 Jul 04

Thanks again. I have tried a number of things. First i have copied religiously the instructions on the site you gave me for making a custom toolbar. There is clearly a problem here as the 'micro' toolbar which appears in the middle of my screen does not go away. This is not referred to on the website. I can remove it by clicking top right but this of course removes the whole point. Even if I don't do that, the new toolbar seen on opening the next time has no new buttons on it. I've tried playing with attaching new toolbars I have made but no joy at all. Just a lot of files to delete to get back to square one!

What I want, however, is more simple. I like to have varied button designs on my toolbars (two showing) and just want to add buttons for 'Edit delete' and 'Insert Rows'. Not another toolbar which will take up more space.

The *.xlb files (one of which bears my name so I must have had something to do with it!) originate from an earlier version of Excel (Excel97). I had to copy this file into the new template folder to get all the coloured buttons to show up when I loaded MS Office onto my new computer a while back. So I know this file is important but do not know how it 'works'or how to amend it by adding more buttons.

  pc moron 06:38 25 Jul 04

Sorry ChrisP, but I'm a bit lost.

Why did you have to import a toolbar from Office 97 into Office 2000?

You should see your customised toolbars just by opening the workbook (originally Office 97) in Office 2000- assuming your customised toolbar was attached to Book.xlt (the default template) or the template you used originally.

If you can't see your customised toolbar then it wasn't attached (stored) to the template you're trying to use, and you'll have to re-create it in Office 2000.

I'm not 100% sure, but trying to import the .xlb files directly probably won't work.

If all you want to add to the default toolbar(s) is Edit>Delete and Insert>Rows, you can do it by following the procedure in the link I posted above.

  ChrisP 11:54 25 Jul 04

"Why did you have to import a toolbar from Office 97 into Office 2000? You should see your customised toolbars just by opening the workbook (originally Office 97) in Office 2000- assuming your customised toolbar was attached to Book.xlt (the default template) or the template you used originally."

I bought a new computer so had to load software de novo. Of course the toolbar was the basic default. The only way I found I could get the 'new' toolbar to look like the one I had constructed in Excel97 and was used to, was to copy the *.xlb files to XLstart. I don't understand why but the default toolbar I have now is the same as I had originally, i.e. whatever happened, it worked!

"If you can't see your customised toolbar then it wasn't attached (stored) to the template you're trying to use, and you'll have to re-create it in Office 2000.

I'm not 100% sure, but trying to import the .xlb files directly probably won't work."

As above, I have my customised toolbar as my default and am satisfied with this. So importing the *.xlb files does work. What I now want to do is to add the two buttons to it. The way I did this before was to go to Tools|customise, then commands| and select, then drag onto the toolbar to the place where I wanted it, and finally save as book.xlt, overwriting the original template. This no longer works, hence my query here.

"If all you want to add to the default toolbar(s) is Edit>Delete and Insert>Rows, you can do it by following the procedure in the link I posted above".

I have tried, honest, but as mentioned before, the 'new' toolbar (the little square in the centre of the screen does not go away when I press 'attach' and I can only get rid of it by closing the file or clicking it off top right. Whatever I do, the buttons do not appear, other than transitorily during the workbook opening, on my workbook. And I cannot find any file which seems to have retained them. I have tried opening a number of possible files - book.xlt, book1.xls and the *.xlb files but no joy.

  pc moron 15:03 25 Jul 04

If the buttons still won't appear I can only assume that the .xlb file imported from Office 97 either won't work with Office 2000 or has become corrupt.

As a last resort you could try to Reset the toolbars to their default state and then try again to customise them.

To reset a toolbar, do the following:

Tools> Customise

Select the toolbar from the list and click Reset.


If you still can't customise the toolbars then I think you may be looking at a re-install of Office/Excel.

I've searched around a bit and I can't find any info about the problem you're having (buttons not appearing or only appearing for a momment).

I've put Delete and Insert Rows buttons on my Standard toolbar and they appear everytime I open Excel and work no problem.

This thread is now locked and can not be replied to.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

iPhone 7 review: Enhanced cameras, a refreshed design and water resistance make the iPhone 7 an…