Creating sub menu in Command bar
 

Not sure if someone will still see this old post... I tried for a while to
make this sample run and finaly I could but how do I delete it when closing
the file... each time a run the file it creates a new comandbar called "Name
of new bar" and I can not delete it... I got my command bar full of them....
I need to close all my excel files to get ride of it... any idea?

"Roman" wrote:

Hi Anders,
try this one:

Sub makemenewbar()
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
..Caption = "Name of new bar"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Button1"
.OnAction = "macro1"
End With

Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Submenu1"
'.OnAction = "sheets_startuf"
End With

Set button2 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Button2 name"
.OnAction = "macro2"
End With

Set button3 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Button3 name"
.OnAction = "macro3"
End With
End Sub

Creating sub menu in Command bar
 

Check your other post for how to delete.


Gord Dibben MS Excel MVP

On Tue, 19 Jan 2010 16:58:01 -0800, Alberto Ast
wrote:

Not sure if someone will still see this old post... I tried for a while to
make this sample run and finaly I could but how do I delete it when closing
the file... each time a run the file it creates a new comandbar called "Name
of new bar" and I can not delete it... I got my command bar full of them....
I need to close all my excel files to get ride of it... any idea?

"Roman" wrote:

Hi Anders,
try this one:

Sub makemenewbar()
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
..Caption = "Name of new bar"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Button1"
.OnAction = "macro1"
End With

Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Submenu1"
'.OnAction = "sheets_startuf"
End With

Set button2 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Button2 name"
.OnAction = "macro2"
End With

Set button3 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Button3 name"
.OnAction = "macro3"
End With
End Sub

Creating sub menu in Command bar
 

Thanks for replying in the follow up of this old post... still not understand
how you get to pick all new posts or continuation of old post but you guys
have been very helpfull for me.

I did use
On Error Resume Next
CommandBars(1).Controls("Name of new bar").Delete
On Error GoTo 0

And was able to keep just one copy of it but still can not make it go away
when I close the file just go away when I close excel completely...
I have two questions...

HOW DO I DELETE IT BEFORE CLOSING THE FILE?
HOW DO I KEEP IT THERE EVEN BEFORE I CLOSE EXCEL? so it stays forever.

"Gord Dibben" wrote:

Check your other post for how to delete.


Gord Dibben MS Excel MVP

On Tue, 19 Jan 2010 16:58:01 -0800, Alberto Ast
wrote:

Not sure if someone will still see this old post... I tried for a while to
make this sample run and finaly I could but how do I delete it when closing
the file... each time a run the file it creates a new comandbar called "Name
of new bar" and I can not delete it... I got my command bar full of them....
I need to close all my excel files to get ride of it... any idea?

"Roman" wrote:

Hi Anders,
try this one:

Sub makemenewbar()
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
..Caption = "Name of new bar"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Button1"
.OnAction = "macro1"
End With

Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Submenu1"
'.OnAction = "sheets_startuf"
End With

Set button2 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Button2 name"
.OnAction = "macro2"
End With

Set button3 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Button3 name"
.OnAction = "macro3"
End With
End Sub



.

Creating sub menu in Command bar
 

First.............To have the new bar for just the one workbook.............

In Thisworkbook module of the workbook in question.

'create bar on open
Private Sub Workbook_Open()
makemenewbar
End Sub

'delete bar on close
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
CommandBars(1).Controls("Name of new bar").Delete
On Error GoTo 0
End Sub

Second..................

If you want the new bar for all workbooks, place the Sub makemenewbar()
into your Personal.xls.

Also stick the code above into Thisworkbook module of Personal.xls.

Make sure Personal.xls also has the macros 1, 2, 3 etc. that are assigned to
the submenus.

Or stick everything into a new workbook and save it as an add-in.


Gord


On Thu, 21 Jan 2010 10:57:01 -0800, Alberto Ast
wrote:

Thanks for replying in the follow up of this old post... still not understand
how you get to pick all new posts or continuation of old post but you guys
have been very helpfull for me.

I did use
On Error Resume Next
CommandBars(1).Controls("Name of new bar").Delete
On Error GoTo 0

And was able to keep just one copy of it but still can not make it go away
when I close the file just go away when I close excel completely...
I have two questions...

HOW DO I DELETE IT BEFORE CLOSING THE FILE?
HOW DO I KEEP IT THERE EVEN BEFORE I CLOSE EXCEL? so it stays forever.

"Gord Dibben" wrote:

Check your other post for how to delete.


Gord Dibben MS Excel MVP

On Tue, 19 Jan 2010 16:58:01 -0800, Alberto Ast
wrote:

Not sure if someone will still see this old post... I tried for a while to
make this sample run and finaly I could but how do I delete it when closing
the file... each time a run the file it creates a new comandbar called "Name
of new bar" and I can not delete it... I got my command bar full of them....
I need to close all my excel files to get ride of it... any idea?

"Roman" wrote:

Hi Anders,
try this one:

Sub makemenewbar()
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
..Caption = "Name of new bar"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Button1"
.OnAction = "macro1"
End With

Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Submenu1"
'.OnAction = "sheets_startuf"
End With

Set button2 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Button2 name"
.OnAction = "macro2"
End With

Set button3 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Button3 name"
.OnAction = "macro3"
End With
End Sub



.

Creating sub menu in Command bar
 

It sounds intersting but you have introduce me to some new items for me....
please explain a little more.

you mentioned my personal.xls... what is it? how do I get it done?
add-in...? same, what is it? how do I get it done?

and.... since I am making a multi users program and I might install this new
command bar into others PC... just in case how will I remove if I follow
avobe proposals? I guess this will help me modify as well as needed.

Hope I am not asking too many basic questions.

I will review my actual coding because I think I already have first option
but it is not deleting the code before closing.

Thanks,
AA


"Gord Dibben" wrote:

First.............To have the new bar for just the one workbook.............

In Thisworkbook module of the workbook in question.

'create bar on open
Private Sub Workbook_Open()
makemenewbar
End Sub

'delete bar on close
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
CommandBars(1).Controls("Name of new bar").Delete
On Error GoTo 0
End Sub

Second..................

If you want the new bar for all workbooks, place the Sub makemenewbar()
into your Personal.xls.

Also stick the code above into Thisworkbook module of Personal.xls.

Make sure Personal.xls also has the macros 1, 2, 3 etc. that are assigned to
the submenus.

Or stick everything into a new workbook and save it as an add-in.


Gord


On Thu, 21 Jan 2010 10:57:01 -0800, Alberto Ast
wrote:

Thanks for replying in the follow up of this old post... still not understand
how you get to pick all new posts or continuation of old post but you guys
have been very helpfull for me.

I did use
On Error Resume Next
CommandBars(1).Controls("Name of new bar").Delete
On Error GoTo 0

And was able to keep just one copy of it but still can not make it go away
when I close the file just go away when I close excel completely...
I have two questions...

HOW DO I DELETE IT BEFORE CLOSING THE FILE?
HOW DO I KEEP IT THERE EVEN BEFORE I CLOSE EXCEL? so it stays forever.

"Gord Dibben" wrote:

Check your other post for how to delete.


Gord Dibben MS Excel MVP

On Tue, 19 Jan 2010 16:58:01 -0800, Alberto Ast
wrote:

Not sure if someone will still see this old post... I tried for a while to
make this sample run and finaly I could but how do I delete it when closing
the file... each time a run the file it creates a new comandbar called "Name
of new bar" and I can not delete it... I got my command bar full of them....
I need to close all my excel files to get ride of it... any idea?

"Roman" wrote:

Hi Anders,
try this one:

Sub makemenewbar()
Set mynewbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
Temporary:=True)
With mynewbar
..Caption = "Name of new bar"
End With

Set button1 = mynewbar.Controls.Add(Type:=msoControlButton)
With button1
.Caption = "Button1"
.OnAction = "macro1"
End With

Set mysubmenu = mynewbar.Controls.Add(Type:=msoControlPopup)
With mysubmenu
.Caption = "Submenu1"
'.OnAction = "sheets_startuf"
End With

Set button2 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button2
.Caption = "Button2 name"
.OnAction = "macro2"
End With

Set button3 = mysubmenu.Controls.Add(Type:=msoControlButton)
With button3
.Caption = "Button3 name"
.OnAction = "macro3"
End With
End Sub



.


.