I've got an addin written a few years ago. Unfortunately it doesn't want to
work in Office 2007, due to the fact that there are no toolbars anymore. How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

I have a few workbooks that create the toolbars on the fly when the workbook is
opened. And delete them when the workbook closes. If I allow macros to run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in xl2007,
though. And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm



John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want to
work in Office 2007, due to the fact that there are no toolbars anymore. How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

In Excel 2007, custom command bars are displayed on the Add Ins tab of
the Ribbon.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 29 Aug 2009 23:30:24 +0100, "John M"
wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want to
work in Office 2007, due to the fact that there are no toolbars anymore. How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

Thanks for responding. It's been a holiday weekend so i didn't get to this
as soon as I should.

This might be a dumb question but where is the Add-in tab of the ribbon?

I see eight tabs on the ribbon
- Home
- Insert
- Page Layout
- Formulas
- Data
- Review
- View
- Developer

In the View tab there is a Macro tab but that doesn't show the custom
command bar.


"Chip Pearson" wrote in message
...
In Excel 2007, custom command bars are displayed on the Add Ins tab of
the Ribbon.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Sat, 29 Aug 2009 23:30:24 +0100, "John M"
wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

Thanks for response

There is code in the workbook to add and remove the toolbars:

Module: basAutostart
---------------
Sub Auto_Open()
Call CreateMenubar
End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub
--------------

The sub CreateMenubar checks for a pre-existing toolbar then adds the
correct number of buttons. Here's the critical snippet:

-------------
With Application.CommandBars.Add
.Name = "xxxxx" '<---- anonymised 2009
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop

For i = LBound(vMacNames) To UBound(vMacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & vMacNames(i)
.Caption = vCapNames(i)
.Style = msoButtonCaption
.FaceId = 71 + i
.TooltipText = vTipText(i)
End With
Next i
End With
-------------

Clearly vMacNames etc are variant arrays. Perhaps my problem is the method
chosen to invoke "CreateMenubar". Is "Auto_Open" the right event for Excel
2007?


"Dave Peterson" wrote in message
...
I have a few workbooks that create the toolbars on the fly when the
workbook is
opened. And delete them when the workbook closes. If I allow macros to
run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in
xl2007,
though. And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the
addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de
Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved
as an
addin)
or
http://www.rondebruin.nl/2007addin.htm



John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

My guess is that you're not noticing the warning that macros are disabled (until
you allow them) when you open this workbook. Could I be right?

John M wrote:

Thanks for response

There is code in the workbook to add and remove the toolbars:

Module: basAutostart
---------------
Sub Auto_Open()
Call CreateMenubar
End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub
--------------

The sub CreateMenubar checks for a pre-existing toolbar then adds the
correct number of buttons. Here's the critical snippet:

-------------
With Application.CommandBars.Add
.Name = "xxxxx" '<---- anonymised 2009
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop

For i = LBound(vMacNames) To UBound(vMacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & vMacNames(i)
.Caption = vCapNames(i)
.Style = msoButtonCaption
.FaceId = 71 + i
.TooltipText = vTipText(i)
End With
Next i
End With
-------------

Clearly vMacNames etc are variant arrays. Perhaps my problem is the method
chosen to invoke "CreateMenubar". Is "Auto_Open" the right event for Excel
2007?

"Dave Peterson" wrote in message
...
I have a few workbooks that create the toolbars on the fly when the
workbook is
opened. And delete them when the workbook closes. If I allow macros to
run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in
xl2007,
though. And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the
addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de
Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved
as an
addin)
or
http://www.rondebruin.nl/2007addin.htm



John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

--

Dave Peterson

I've got a similar problem. My Addins also do not display their
toolbars. And like John M, I see no Add-in tab on the ribbon. Google
searches show nothing and the help is well, unhelpful.

Question: Where is the Add-in tab on the ribbon? How do I get it? I
see John M also asks this question.

To eliminate the obvious, you ask John M if the macros are disabled. I
have fully enabled all macros, yet I still don't get toolbars. If I go
into the VB editor all my code executes except the code to display the
toolbar, which appears to execute but no toolbar appears.

Thanks




On Aug 31, 8:26*pm, Dave Peterson wrote:
If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

All macros are enabled - I have triple checked. I can run them in the
VBA editor and all the Add-in code is effective except that which
displays and hides the toolbar. It executes in step through mode but
no toolbar appears.

I have found an Add-ins tab. This is accessible through customization
of the Quick Launch toolbar and gives me three icons: custom toolbars,
menu commands and toolbar commands. (eg first's icon shows 3
rectangles superimposed upon one another). Inside each is a drop down
space with a button marked "c..."/"m..." etc at the bottom. Clicking
this has no effect. Running my add toolbar code does not add a toolbar
to any of these.

I am guessing this is NOT the Add-ins ribbon tab you expect me to see.
I also guess that the Add-ins tab should appear after Developer tab
but it's not there! [I guess this because in various screen shots in
the help I can see an Add-in Tab after the Develop tab]

Is this perhaps an Office version issue?

If not how do I get it to display.

---

I've also tried saving my add-in as an "xlam" file. That makes no
difference. In either case my Add-in appears in the list of "active"
add-ins but no toolbar.

I think my problem boils down to finding out how to enable the Add-in
tab of the ribbon. If I can do that I am guessing my problem will be
solved.


PS: I am sending this from my gmail account. I normally prefer to use
a newsreader because I more easily avoid spam but I'm at work.



On Aug 31, 8:26*pm, Dave Peterson wrote:
If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

The addins tab of the ribbon should be shown when excel finds an addin. It
always shows up for me with my addins are loaded.

I don't have a guess why it doesn't show up for you or TDK.

JohnM wrote:

All macros are enabled - I have triple checked. I can run them in the
VBA editor and all the Add-in code is effective except that which
displays and hides the toolbar. It executes in step through mode but
no toolbar appears.

I have found an Add-ins tab. This is accessible through customization
of the Quick Launch toolbar and gives me three icons: custom toolbars,
menu commands and toolbar commands. (eg first's icon shows 3
rectangles superimposed upon one another). Inside each is a drop down
space with a button marked "c..."/"m..." etc at the bottom. Clicking
this has no effect. Running my add toolbar code does not add a toolbar
to any of these.

I am guessing this is NOT the Add-ins ribbon tab you expect me to see.
I also guess that the Add-ins tab should appear after Developer tab
but it's not there! [I guess this because in various screen shots in
the help I can see an Add-in Tab after the Develop tab]

Is this perhaps an Office version issue?

If not how do I get it to display.

---

I've also tried saving my add-in as an "xlam" file. That makes no
difference. In either case my Add-in appears in the list of "active"
add-ins but no toolbar.

I think my problem boils down to finding out how to enable the Add-in
tab of the ribbon. If I can do that I am guessing my problem will be
solved.

PS: I am sending this from my gmail account. I normally prefer to use
a newsreader because I more easily avoid spam but I'm at work.

On Aug 31, 8:26 pm, Dave Peterson wrote:
If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

My guess is that you're not noticing the warning that macros are disabled (until
you allow them) when you open this workbook. Could I be right?



John M wrote:

Thanks for response

There is code in the workbook to add and remove the toolbars:

Module: basAutostart
---------------
Sub Auto_Open()
Call CreateMenubar
End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub
--------------

The sub CreateMenubar checks for a pre-existing toolbar then adds the
correct number of buttons. Here's the critical snippet:

-------------
With Application.CommandBars.Add
.Name = "xxxxx" '<---- anonymised 2009
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop

For i = LBound(vMacNames) To UBound(vMacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & vMacNames(i)
.Caption = vCapNames(i)
.Style = msoButtonCaption
.FaceId = 71 + i
.TooltipText = vTipText(i)
End With
Next i
End With
-------------

Clearly vMacNames etc are variant arrays. Perhaps my problem is the method
chosen to invoke "CreateMenubar". Is "Auto_Open" the right event for Excel
2007?

"Dave Peterson" wrote in message
...
I have a few workbooks that create the toolbars on the fly when the
workbook is
opened. And delete them when the workbook closes. If I allow macros to
run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in
xl2007,
though. And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the
addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de
Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved
as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Okay. Appreciate response.

I doubt this thread will generate any new responders so I will post
new topic. Before I try that I'm going to find a different PC and see
if they have an add-in tab.



On Sep 2, 1:36*pm, Dave Peterson wrote:
The addins tab of the ribbon should be shown when excel finds an addin. *It
always shows up for me with my addins are loaded.

I don't have a guess why it doesn't show up for you or TDK.



JohnM wrote:

All macros are enabled - I have triple checked. I can run them in the
VBA editor and all the Add-in code is effective except that which
displays and hides the toolbar. It executes in step through mode but
no toolbar appears.

I have found an Add-ins tab. This is accessible through customization
of the Quick Launch toolbar and gives me three icons: custom toolbars,
menu commands and toolbar commands. (eg first's icon shows 3
rectangles superimposed upon one another). Inside each is a drop down
space with a button marked "c..."/"m..." etc at the bottom. Clicking
this has no effect. Running my add toolbar code does not add a toolbar
to any of these.

I am guessing this is NOT the Add-ins ribbon tab you expect me to see.
I also guess that the Add-ins tab should appear after Developer tab
but it's not there! [I guess this because in various screen shots in
the help I can see an Add-in Tab after the Develop tab]

Is this perhaps an Office version issue?

If not how do I get it to display.

---

I've also tried saving my add-in as an "xlam" file. That makes no
difference. In either case my Add-in appears in the list of "active"
add-ins but no toolbar.

I think my problem boils down to finding out how to enable the Add-in
tab of the ribbon. If I can do that I am guessing my problem will be
solved.

PS: I am sending this from my gmail account. I normally prefer to use
a newsreader because I more easily avoid spam but I'm at work.

On Aug 31, 8:26 pm, Dave Peterson wrote:
If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

My guess is that you're not noticing the warning that macros are disabled (until
you allow them) when you open this workbook. *Could I be right?

John M wrote:

Thanks for response

There is code in the workbook to add and remove the toolbars:

Module: basAutostart
---------------
Sub Auto_Open()
* Call CreateMenubar
End Sub

Sub Auto_Close()
* Call RemoveMenubar
End Sub
--------------

The sub CreateMenubar checks for a pre-existing toolbar then adds the
correct number of buttons. Here's the critical snippet:

-------------
* With Application.CommandBars.Add
* * * .Name = "xxxxx" *'<---- anonymised 2009
* * * .Protection = msoBarNoProtection
* * * .Visible = True
* * * .Position = msoBarTop

* * * For i = LBound(vMacNames) To UBound(vMacNames)
* * * * With .Controls.Add(Type:=msoControlButton)
* * * * * .OnAction = "'" & ThisWorkbook.Name & "'!" & vMacNames(i)
* * * * * .Caption = vCapNames(i)
* * * * * .Style = msoButtonCaption
* * * * * .FaceId = 71 + i
* * * * * .TooltipText = vTipText(i)
* * * * End With
* * * Next i
*End With
-------------

Clearly vMacNames etc are variant arrays. Perhaps my problem is the method
chosen to invoke "CreateMenubar". Is "Auto_Open" the right event for Excel
2007?

"Dave Peterson" wrote in message
...
I have a few workbooks that create the toolbars on the fly when the
workbook is
opened. *And delete them when the workbook closes. *If I allow macros to
run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in
xl2007,
though. *And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the
addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de
Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm-- For macros for all workbooks (saved
as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

My error. There was a problem with my code.

It did a check to see if there was already a toolbar with the same
name and if so didn't recreate it. There was such a toolbar but it had
no controls so didn't show in the Add-in tab. Not quite sure how that
occurred but no doubt something I did early on.

Once I created a brand new add-in just to test the code this was
obvious. And of course the Add-in tab showed up.

I'm ashamed to admit this ranks as an "Is it plugged in?"


On Sep 2, 1:36*pm, Dave Peterson wrote:
The addins tab of the ribbon should be shown when excel finds an addin. *It
always shows up for me with my addins are loaded.

I don't have a guess why it doesn't show up for you or TDK.



JohnM wrote:

All macros are enabled - I have triple checked. I can run them in the
VBA editor and all the Add-in code is effective except that which
displays and hides the toolbar. It executes in step through mode but
no toolbar appears.

I have found an Add-ins tab. This is accessible through customization
of the Quick Launch toolbar and gives me three icons: custom toolbars,
menu commands and toolbar commands. (eg first's icon shows 3
rectangles superimposed upon one another). Inside each is a drop down
space with a button marked "c..."/"m..." etc at the bottom. Clicking
this has no effect. Running my add toolbar code does not add a toolbar
to any of these.

I am guessing this is NOT the Add-ins ribbon tab you expect me to see.
I also guess that the Add-ins tab should appear after Developer tab
but it's not there! [I guess this because in various screen shots in
the help I can see an Add-in Tab after the Develop tab]

Is this perhaps an Office version issue?

If not how do I get it to display.

---

I've also tried saving my add-in as an "xlam" file. That makes no
difference. In either case my Add-in appears in the list of "active"
add-ins but no toolbar.

I think my problem boils down to finding out how to enable the Add-in
tab of the ribbon. If I can do that I am guessing my problem will be
solved.

PS: I am sending this from my gmail account. I normally prefer to use
a newsreader because I more easily avoid spam but I'm at work.

On Aug 31, 8:26 pm, Dave Peterson wrote:
If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

My guess is that you're not noticing the warning that macros are disabled (until
you allow them) when you open this workbook. *Could I be right?

John M wrote:

Thanks for response

There is code in the workbook to add and remove the toolbars:

Module: basAutostart
---------------
Sub Auto_Open()
* Call CreateMenubar
End Sub

Sub Auto_Close()
* Call RemoveMenubar
End Sub
--------------

The sub CreateMenubar checks for a pre-existing toolbar then adds the
correct number of buttons. Here's the critical snippet:

-------------
* With Application.CommandBars.Add
* * * .Name = "xxxxx" *'<---- anonymised 2009
* * * .Protection = msoBarNoProtection
* * * .Visible = True
* * * .Position = msoBarTop

* * * For i = LBound(vMacNames) To UBound(vMacNames)
* * * * With .Controls.Add(Type:=msoControlButton)
* * * * * .OnAction = "'" & ThisWorkbook.Name & "'!" & vMacNames(i)
* * * * * .Caption = vCapNames(i)
* * * * * .Style = msoButtonCaption
* * * * * .FaceId = 71 + i
* * * * * .TooltipText = vTipText(i)
* * * * End With
* * * Next i
*End With
-------------

Clearly vMacNames etc are variant arrays. Perhaps my problem is the method
chosen to invoke "CreateMenubar". Is "Auto_Open" the right event for Excel
2007?

"Dave Peterson" wrote in message
...
I have a few workbooks that create the toolbars on the fly when the
workbook is
opened. *And delete them when the workbook closes. *If I allow macros to
run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in
xl2007,
though. *And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the
addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de
Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm-- For macros for all workbooks (saved
as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Thanks for posting back.

Those kinds of things never ever happen to me <I crack me up!.

And you've restored my faith in Microsoft!

JohnM wrote:

My error. There was a problem with my code.

It did a check to see if there was already a toolbar with the same
name and if so didn't recreate it. There was such a toolbar but it had
no controls so didn't show in the Add-in tab. Not quite sure how that
occurred but no doubt something I did early on.

Once I created a brand new add-in just to test the code this was
obvious. And of course the Add-in tab showed up.

I'm ashamed to admit this ranks as an "Is it plugged in?"

On Sep 2, 1:36 pm, Dave Peterson wrote:
The addins tab of the ribbon should be shown when excel finds an addin. It
always shows up for me with my addins are loaded.

I don't have a guess why it doesn't show up for you or TDK.



JohnM wrote:

All macros are enabled - I have triple checked. I can run them in the
VBA editor and all the Add-in code is effective except that which
displays and hides the toolbar. It executes in step through mode but
no toolbar appears.

I have found an Add-ins tab. This is accessible through customization
of the Quick Launch toolbar and gives me three icons: custom toolbars,
menu commands and toolbar commands. (eg first's icon shows 3
rectangles superimposed upon one another). Inside each is a drop down
space with a button marked "c..."/"m..." etc at the bottom. Clicking
this has no effect. Running my add toolbar code does not add a toolbar
to any of these.

I am guessing this is NOT the Add-ins ribbon tab you expect me to see.
I also guess that the Add-ins tab should appear after Developer tab
but it's not there! [I guess this because in various screen shots in
the help I can see an Add-in Tab after the Develop tab]

Is this perhaps an Office version issue?

If not how do I get it to display.

---

I've also tried saving my add-in as an "xlam" file. That makes no
difference. In either case my Add-in appears in the list of "active"
add-ins but no toolbar.

I think my problem boils down to finding out how to enable the Add-in
tab of the ribbon. If I can do that I am guessing my problem will be
solved.

PS: I am sending this from my gmail account. I normally prefer to use
a newsreader because I more easily avoid spam but I'm at work.

On Aug 31, 8:26 pm, Dave Peterson wrote:
If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

My guess is that you're not noticing the warning that macros are disabled (until
you allow them) when you open this workbook. Could I be right?

John M wrote:

Thanks for response

There is code in the workbook to add and remove the toolbars:

Module: basAutostart
---------------
Sub Auto_Open()
Call CreateMenubar
End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub
--------------

The sub CreateMenubar checks for a pre-existing toolbar then adds the
correct number of buttons. Here's the critical snippet:

-------------
With Application.CommandBars.Add
.Name = "xxxxx" '<---- anonymised 2009
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop

For i = LBound(vMacNames) To UBound(vMacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & vMacNames(i)
.Caption = vCapNames(i)
.Style = msoButtonCaption
.FaceId = 71 + i
.TooltipText = vTipText(i)
End With
Next i
End With
-------------

Clearly vMacNames etc are variant arrays. Perhaps my problem is the method
chosen to invoke "CreateMenubar". Is "Auto_Open" the right event for Excel
2007?

"Dave Peterson" wrote in message
...
I have a few workbooks that create the toolbars on the fly when the
workbook is
opened. And delete them when the workbook closes. If I allow macros to
run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in
xl2007,
though. And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the
addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de
Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm-- For macros for all workbooks (saved
as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Thanks for these posts.

My toolbar was built into the add-in and the code merely enabled it
and made it visible rather than rebuilt it. This appears to be work
satisfactorily in Excel 2003 - the add-in can be copied to a new
machine and the toolbar appears whenever the add-in is ticked. For
whatever reason this doesn't work out in Excel 2007. However I too can
confirm that rebuilding from scratch does make the add-in tab appear
and give access to the required buttons.

One reason for originally doing it this was to attach icons to the
buttons and then not have to worry about them anymore. Now I get the
button text only. I haven't tried to attach icons that aren't in the
standard list yet.

I'll have a play with that problem while I eat humble pie.


On Sep 2, 9:11*pm, Dave Peterson wrote:
Thanks for posting back. *

Those kinds of things never ever happen to me <I crack me up!.

And you've restored my faith in Microsoft!



JohnM wrote:

My error. There was a problem with my code.

It did a check to see if there was already a toolbar with the same
name and if so didn't recreate it. There was such a toolbar but it had
no controls so didn't show in the Add-in tab. Not quite sure how that
occurred but no doubt something I did early on.

Once I created a brand new add-in just to test the code this was
obvious. And of course the Add-in tab showed up.

I'm ashamed to admit this ranks as an "Is it plugged in?"

On Sep 2, 1:36 pm, Dave Peterson wrote:
The addins tab of the ribbon should be shown when excel finds an addin. *It
always shows up for me with my addins are loaded.

I don't have a guess why it doesn't show up for you or TDK.

JohnM wrote:

All macros are enabled - I have triple checked. I can run them in the
VBA editor and all the Add-in code is effective except that which
displays and hides the toolbar. It executes in step through mode but
no toolbar appears.

I have found an Add-ins tab. This is accessible through customization
of the Quick Launch toolbar and gives me three icons: custom toolbars,
menu commands and toolbar commands. (eg first's icon shows 3
rectangles superimposed upon one another). Inside each is a drop down
space with a button marked "c..."/"m..." etc at the bottom. Clicking
this has no effect. Running my add toolbar code does not add a toolbar
to any of these.

I am guessing this is NOT the Add-ins ribbon tab you expect me to see.
I also guess that the Add-ins tab should appear after Developer tab
but it's not there! [I guess this because in various screen shots in
the help I can see an Add-in Tab after the Develop tab]

Is this perhaps an Office version issue?

If not how do I get it to display.

---

I've also tried saving my add-in as an "xlam" file. That makes no
difference. In either case my Add-in appears in the list of "active"
add-ins but no toolbar.

I think my problem boils down to finding out how to enable the Add-in
tab of the ribbon. If I can do that I am guessing my problem will be
solved.

PS: I am sending this from my gmail account. I normally prefer to use
a newsreader because I more easily avoid spam but I'm at work.

On Aug 31, 8:26 pm, Dave Peterson wrote:
If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

My guess is that you're not noticing the warning that macros are disabled (until
you allow them) when you open this workbook. *Could I be right?

John M wrote:

Thanks for response

There is code in the workbook to add and remove the toolbars:

Module: basAutostart
---------------
Sub Auto_Open()
* Call CreateMenubar
End Sub

Sub Auto_Close()
* Call RemoveMenubar
End Sub
--------------

The sub CreateMenubar checks for a pre-existing toolbar then adds the
correct number of buttons. Here's the critical snippet:

-------------
* With Application.CommandBars.Add
* * * .Name = "xxxxx" *'<---- anonymised 2009
* * * .Protection = msoBarNoProtection
* * * .Visible = True
* * * .Position = msoBarTop

* * * For i = LBound(vMacNames) To UBound(vMacNames)
* * * * With .Controls.Add(Type:=msoControlButton)
* * * * * .OnAction = "'" & ThisWorkbook.Name & "'!" & vMacNames(i)
* * * * * .Caption = vCapNames(i)
* * * * * .Style = msoButtonCaption
* * * * * .FaceId = 71 + i
* * * * * .TooltipText = vTipText(i)
* * * * End With
* * * Next i
*End With
-------------

Clearly vMacNames etc are variant arrays. Perhaps my problem is the method
chosen to invoke "CreateMenubar". Is "Auto_Open" the right event for Excel
2007?

"Dave Peterson" wrote in message
...
I have a few workbooks that create the toolbars on the fly when the
workbook is
opened. *And delete them when the workbook closes. *If I allow macros to
run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in
xl2007,
though. *And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the
addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de
Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm--For macros for all workbooks (saved
as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

The icons show up for me in John's code. (the .FaceId = 71 + i works fine).

But if you could put the pictures on a worksheet and name them nicely, you could
use something like:

Option Explicit
Public Const ToolBarName As String = "MyToolbarName"
Sub Auto_Open()
Call CreateMenubar
End Sub
Sub Auto_Close()
Call RemoveMenubar
End Sub
Sub RemoveMenubar()
On Error Resume Next
Application.CommandBars(ToolBarName).Delete
On Error GoTo 0
End Sub
Sub CreateMenubar()

Dim iCtr As Long

Dim MacNames As Variant
Dim CapNames As Variant
Dim TipText As Variant
Dim PictNames As Variant
Dim PictWks As Worksheet

Call RemoveMenubar

MacNames = Array("aaa", _
"bbb")

CapNames = Array("AAA Caption", _
"BBB Caption")

TipText = Array("AAA tip", _
"BBB tip")

PictNames = Array("Picture 1", _
"Picture 2")

Set PictWks = ThisWorkbook.Worksheets("Pictures")

With Application.CommandBars.Add
.Name = ToolBarName
.Left = 200
.Top = 200
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarFloating

For iCtr = LBound(MacNames) To UBound(MacNames)
PictWks.Pictures(PictNames(iCtr)).Copy
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & MacNames(iCtr)
.Caption = CapNames(iCtr)
.Style = msoButtonIconAndCaption
.PasteFace
.TooltipText = TipText(iCtr)
End With
Next iCtr

End With
End Sub
Sub AAA()
MsgBox "aaa"
End Sub
Sub BBB()
MsgBox "bbb"
End Sub


It worked ok in my testing.

TDK wrote:

Thanks for these posts.

My toolbar was built into the add-in and the code merely enabled it
and made it visible rather than rebuilt it. This appears to be work
satisfactorily in Excel 2003 - the add-in can be copied to a new
machine and the toolbar appears whenever the add-in is ticked. For
whatever reason this doesn't work out in Excel 2007. However I too can
confirm that rebuilding from scratch does make the add-in tab appear
and give access to the required buttons.

One reason for originally doing it this was to attach icons to the
buttons and then not have to worry about them anymore. Now I get the
button text only. I haven't tried to attach icons that aren't in the
standard list yet.

I'll have a play with that problem while I eat humble pie.

On Sep 2, 9:11 pm, Dave Peterson wrote:
Thanks for posting back.

Those kinds of things never ever happen to me <I crack me up!.

And you've restored my faith in Microsoft!



JohnM wrote:

My error. There was a problem with my code.

It did a check to see if there was already a toolbar with the same
name and if so didn't recreate it. There was such a toolbar but it had
no controls so didn't show in the Add-in tab. Not quite sure how that
occurred but no doubt something I did early on.

Once I created a brand new add-in just to test the code this was
obvious. And of course the Add-in tab showed up.

I'm ashamed to admit this ranks as an "Is it plugged in?"

On Sep 2, 1:36 pm, Dave Peterson wrote:
The addins tab of the ribbon should be shown when excel finds an addin. It
always shows up for me with my addins are loaded.

I don't have a guess why it doesn't show up for you or TDK.

JohnM wrote:

All macros are enabled - I have triple checked. I can run them in the
VBA editor and all the Add-in code is effective except that which
displays and hides the toolbar. It executes in step through mode but
no toolbar appears.

I have found an Add-ins tab. This is accessible through customization
of the Quick Launch toolbar and gives me three icons: custom toolbars,
menu commands and toolbar commands. (eg first's icon shows 3
rectangles superimposed upon one another). Inside each is a drop down
space with a button marked "c..."/"m..." etc at the bottom. Clicking
this has no effect. Running my add toolbar code does not add a toolbar
to any of these.

I am guessing this is NOT the Add-ins ribbon tab you expect me to see.
I also guess that the Add-ins tab should appear after Developer tab
but it's not there! [I guess this because in various screen shots in
the help I can see an Add-in Tab after the Develop tab]

Is this perhaps an Office version issue?

If not how do I get it to display.

---

I've also tried saving my add-in as an "xlam" file. That makes no
difference. In either case my Add-in appears in the list of "active"
add-ins but no toolbar.

I think my problem boils down to finding out how to enable the Add-in
tab of the ribbon. If I can do that I am guessing my problem will be
solved.

PS: I am sending this from my gmail account. I normally prefer to use
a newsreader because I more easily avoid spam but I'm at work.

On Aug 31, 8:26 pm, Dave Peterson wrote:
If you allow macros to run when you open this workbook that owns the code, then
you should see the toolbar in the Addins (not developer) tab of the ribbon.

My guess is that you're not noticing the warning that macros are disabled (until
you allow them) when you open this workbook. Could I be right?

John M wrote:

Thanks for response

There is code in the workbook to add and remove the toolbars:

Module: basAutostart
---------------
Sub Auto_Open()
Call CreateMenubar
End Sub

Sub Auto_Close()
Call RemoveMenubar
End Sub
--------------

The sub CreateMenubar checks for a pre-existing toolbar then adds the
correct number of buttons. Here's the critical snippet:

-------------
With Application.CommandBars.Add
.Name = "xxxxx" '<---- anonymised 2009
.Protection = msoBarNoProtection
.Visible = True
.Position = msoBarTop

For i = LBound(vMacNames) To UBound(vMacNames)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & vMacNames(i)
.Caption = vCapNames(i)
.Style = msoButtonCaption
.FaceId = 71 + i
.TooltipText = vTipText(i)
End With
Next i
End With
-------------

Clearly vMacNames etc are variant arrays. Perhaps my problem is the method
chosen to invoke "CreateMenubar". Is "Auto_Open" the right event for Excel
2007?

"Dave Peterson" wrote in message
...
I have a few workbooks that create the toolbars on the fly when the
workbook is
opened. And delete them when the workbook closes. If I allow macros to
run,
then these toolbars will appear the Addin's tab in xl2007.

(I've never attached a toolbar to a workbook to see how that works in
xl2007,
though. And I'm not sure how you created your toolbar.)

Saved from a previous post:

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)

In xl2007, those toolbars and menu modifications will show up under the
addins.

And if you use xl2007:

If you want to learn about modifying the ribbon, you can start at Ron de
Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm--For macros for all workbooks (saved
as an
addin)
or
http://www.rondebruin.nl/2007addin.htm

John M wrote:

I've got an addin written a few years ago. Unfortunately it doesn't want
to
work in Office 2007, due to the fact that there are no toolbars anymore.
How
do I expose the Sub Procedures within the addin to a user, so they can
execute them, without using a toolbar?

Many thanks

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson