View Custom Toolbar in Excel 2007

[Thomas M.]

I have an Excel 2003 file that contains a custom toolbar. In Excel 2003 the
custom toolbar is displayed and I can just click the buttons on that toolbar
to run my macros. When I open the file in Excel 2007 I can find no way to
display that custom toolbar. In fact, the only way that I have found to
access my macros is to go into View Macros and run them that way, which
isn't going to cut it.

Is there a way in Excel 2007 to re-create the Excel 2003 functionality,
perhaps by building a custom ribbon and putting buttons for my macros on
that ribbon? I just need some way to make the macros immediately available
at all times--not 3 or 4 clicks away.

--Tom

 

[Dave Peterson]

Didn't you see your toolbar under the Addins tab?

 

[Guest]

Hi Thomas,

Will using the quick access toolbar solve your problem? It can be customized
to have buttons which display with individual workbooks and you can run your
macros from them.

Open the required workbook.

Right click on the quick access toolbar (On an empty part) then select
Customize...

The drop down box at the top titled 'choose commands from' (defaults to
'popular commands'). Click on the drop down and select macros.

Opposite there is a drop down titled 'customize quick access toolbar'. This
defaults to 'for all documents'. Click on the drop down and select the
workbook that commands are to apply to.

Select the required macro in the left column and click on add.

Click on modify to select a button design.

Note the arrows at the right. You can move the buttons up and down to the
required location. Separators can also be selected and added.

Regards,

OssieMac

 

[Dave Peterson]

Ron de Bruin has some tips that may prove interesting to the OP:
http://www.rondebruin.nl/tips.htm

 

[Thomas M.]

Sorry that I didn't respond sooner. I've been out sick the last couple of
days.

I just opened the file in Excel 2007 and now I do see the custom toolbar
under Addins. Curious, I don't recall even seeing the Addins tab
previously.

Now I have a new problem. None of the buttons on the toolbar work. They
are all trying to refer back to the file on the server, but the file is no
longer on the server. The file is stored on the local machine and all the
macros are in the ThisDocument module. I've had similar problems in Excel
2003--it seems to loose the macro assignments for the buttons on the custom
toolbar, so they need to be reset each time the file is opened. However, in
Excel 2007 I have not yet discovered how to reassign the macros to the
buttons. Maybe I just need to climb a little higher on the learning curve
with 2007.

At this point I'm fairly frustrated with both Excel 2003 and 2007. All I'm
really doing is telling Excel to run a macro that is stored within the file
itself, and the bottom line is that both versions of Excel have utterly
failed to do that very simple task on a reliable basis. I am still open to
the idea that I've made some kind of mistake, or that there is some small
degree of file corruption causing the problem, but the more I work with it
the more I think that the problem is just Excel itself.

--Tom

 

[Thomas M.]

I've not yet had a chance to try these last two suggestions, and the next
two weeks are looking pretty hectic. I'll try to get them tested and will
let you know how things work out.

--Tom

 

[Dave Peterson]

If you can live with the customizations under the Addins tab...

Your life will become much simpler if you include code to create the toolbar
when the workbook is opened and include code to destroy the toolbar when the
workbook is closed.

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Thomas M.]

Sorry that I haven't replied back in so long. I've been buried lately and
have not had much time to work on this.

Just out of curiosity, why is it that using code to create the toolbar when
the workbook opens, and destroy it when the workbook closes, the preferred
option? That strikes me as being like using a bazooka to take out a
butterfly. If that's what I have to do, then that's what I have to do. I
can accept that and I'm willing to do the work, but it does seem like a
work-around for a bug that Microsoft should fix. Am I correct about that,
or is there something fundamental about how Excel works that I'm just not
understanding?

I'll try to take a look at the links that you posted and hopefully get my
problem fixed so that I can close out this thread. However, I tend to do
this kind of stuff on the weekend, and my home PC blue screened last night,
so I'm not sure that I'll have time this upcoming weekend.

--Tom