I'm so confused!!!

Some history...

I have a workbook that I created maybe eight years ago, that has some simple macros
that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
where the macros are listed at the bottom of the normal list. This macros (and the items
on the Tools menu) are ONLY available for this particular workbook.

I have tried to duplicate this action in two ways:
1. I have simply dragged an item from the toolbar customize box to the Tools menu, and
this works fine, but then it is present on any workbook I open from then on, and of
course, I only want it to be present for this workbook.

2. I have saved the original workbook as a new workbook, then deleted everything in
the new workbook, including one of the Tools Menu items, and reassigned new macros
to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools
menu looks just as it did without saving it.

What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
menu item), and make these menu items (or toolbar buttons) available ONLY to this
workbook. I am having no luck accomplishing this.

Can anyone explain why I'm having these problems?

Thanks,
Bernie

One way to place a item in the tools menu

Run the macros in this events in the Thisworkbook module for example

Private Sub Workbook_Activate()
MenuBar_Item_Item
End Sub

Private Sub Workbook_Deactivate()
MenuBar_Item_Item_Delete
End Sub


Sub MenuBar_Item_Item()
Dim MenuItem As CommandBarControl
MenuBar_Item_Item_Delete

Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu
If MenuItem Is Nothing Then Exit Sub
With MenuItem.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Ron de Bruin"
.OnAction = ThisWorkbook.Name & "!TestMacro"
.BeginGroup = True
.Tag = "MenuItemTag"
End With
Set MenuItem = Nothing
End Sub

Sub MenuBar_Item_Item_Delete()
Dim MenuItem As CommandBarControl
Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItem Tag")
If Not MenuItem Is Nothing Then
MenuItem.Delete
End If
Set MenuItem = Nothing
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"bw" wrote in message ...
I'm so confused!!!

Some history...

I have a workbook that I created maybe eight years ago, that has some simple macros
that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
where the macros are listed at the bottom of the normal list. This macros (and the items
on the Tools menu) are ONLY available for this particular workbook.

I have tried to duplicate this action in two ways:
1. I have simply dragged an item from the toolbar customize box to the Tools menu, and
this works fine, but then it is present on any workbook I open from then on, and of
course, I only want it to be present for this workbook.

2. I have saved the original workbook as a new workbook, then deleted everything in
the new workbook, including one of the Tools Menu items, and reassigned new macros
to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools
menu looks just as it did without saving it.

What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
menu item), and make these menu items (or toolbar buttons) available ONLY to this
workbook. I am having no luck accomplishing this.

Can anyone explain why I'm having these problems?

Thanks,
Bernie

Ron,

I haven't tried your suggestion yet. But this seems very confusing and a lot of effort to do
what I have asked. I didn't have to go through all this when I placed these items on the
Tools menu eight years ago, so I don't know why I have to now?

I'm clearly missing something important here, but I'm not sure what...

Bernie

On Wed, 30 Jul 2003 18:38:58 +0200, "Ron de Bruin"
wrote:
One way to place a item in the tools menu

Run the macros in this events in the Thisworkbook module for example

Private Sub Workbook_Activate()
MenuBar_Item_Item
End Sub

Private Sub Workbook_Deactivate()
MenuBar_Item_Item_Delete
End Sub


Sub MenuBar_Item_Item()
Dim MenuItem As CommandBarControl
MenuBar_Item_Item_Delete

Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu
If MenuItem Is Nothing Then Exit Sub
With MenuItem.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Ron de Bruin"
.OnAction = ThisWorkbook.Name & "!TestMacro"
.BeginGroup = True
.Tag = "MenuItemTag"
End With
Set MenuItem = Nothing
End Sub

Sub MenuBar_Item_Item_Delete()
Dim MenuItem As CommandBarControl
Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItem Tag")
If Not MenuItem Is Nothing Then
MenuItem.Delete
End If
Set MenuItem = Nothing
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"bw" wrote in message news:1105_1059582525
@news.cableone.net...
I'm so confused!!!

Some history...

I have a workbook that I created maybe eight years ago, that has some simple
macros
that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
where the macros are listed at the bottom of the normal list. This macros (and the
items
on the Tools menu) are ONLY available for this particular workbook.

I have tried to duplicate this action in two ways:
1. I have simply dragged an item from the toolbar customize box to the Tools menu,
and
this works fine, but then it is present on any workbook I open from then on, and of
course, I only want it to be present for this workbook.

2. I have saved the original workbook as a new workbook, then deleted everything
in
the new workbook, including one of the Tools Menu items, and reassigned new
macros
to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools
menu looks just as it did without saving it.

What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
menu item), and make these menu items (or toolbar buttons) available ONLY to this
workbook. I am having no luck accomplishing this.

Can anyone explain why I'm having these problems?

Thanks,
Bernie

You posted in Programming so I give you a macro solution.


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"bw" wrote in message ...
Ron,

I haven't tried your suggestion yet. But this seems very confusing and a lot of effort to do
what I have asked. I didn't have to go through all this when I placed these items on the
Tools menu eight years ago, so I don't know why I have to now?

I'm clearly missing something important here, but I'm not sure what...

Bernie

On Wed, 30 Jul 2003 18:38:58 +0200, "Ron de Bruin"
wrote:
One way to place a item in the tools menu

Run the macros in this events in the Thisworkbook module for example

Private Sub Workbook_Activate()
MenuBar_Item_Item
End Sub

Private Sub Workbook_Deactivate()
MenuBar_Item_Item_Delete
End Sub


Sub MenuBar_Item_Item()
Dim MenuItem As CommandBarControl
MenuBar_Item_Item_Delete

Set MenuItem = Application.CommandBars.FindControl(, 30007) 'Tools menu
If MenuItem Is Nothing Then Exit Sub
With MenuItem.Controls.Add(msoControlButton, 1, , , True)
.Caption = "&Ron de Bruin"
.OnAction = ThisWorkbook.Name & "!TestMacro"
.BeginGroup = True
.Tag = "MenuItemTag"
End With
Set MenuItem = Nothing
End Sub

Sub MenuBar_Item_Item_Delete()
Dim MenuItem As CommandBarControl
Set MenuItem = Application.CommandBars.FindControl(Tag:="MenuItem Tag")
If Not MenuItem Is Nothing Then
MenuItem.Delete
End If
Set MenuItem = Nothing
End Sub

Sub TestMacro()
MsgBox "Hi"
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"bw" wrote in message news:1105_1059582525
@news.cableone.net...
I'm so confused!!!

Some history...

I have a workbook that I created maybe eight years ago, that has some simple
macros
that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
where the macros are listed at the bottom of the normal list. This macros (and the
items
on the Tools menu) are ONLY available for this particular workbook.

I have tried to duplicate this action in two ways:
1. I have simply dragged an item from the toolbar customize box to the Tools menu,
and
this works fine, but then it is present on any workbook I open from then on, and of
course, I only want it to be present for this workbook.

2. I have saved the original workbook as a new workbook, then deleted everything
in
the new workbook, including one of the Tools Menu items, and reassigned new
macros
to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools
menu looks just as it did without saving it.

What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
menu item), and make these menu items (or toolbar buttons) available ONLY to this
workbook. I am having no luck accomplishing this.

Can anyone explain why I'm having these problems?

Thanks,
Bernie

Bernie

Thanks for the feedback.

Ron's code will help you get started with creating Toolbars and Menus "on the
fly".

Gord


On Wed, 30 Jul 2003 22:42:34 GMT, bw wrote:

Thanks Gord,

This is what I was looking for (hoping for). I'm pretty sure you are right about Excel 5.0,
and you have described the problem I'm having very well.

I'll try some of the macros provided by others to try to accomplish the same thing that
Excel 5.0 did.

Progress I guess...

Thanks again,
Bernie



On Wed, 30 Jul 2003 10:42:02 -0700, Gord Dibben wrote:
Bernie

"eight years ago" sounds like the workbook was created using XL95

The problem you are having with that particular workbook would be......

Excel 5.0 used the "Menu Editor" to build menus. This is no longer available
in versions newer than 5.0. You can't use the "delete" method or "reset" to
get rid of the custom menu items.

To get rid of the custom menu download Jim Rech's REMOVEMENUS.ZIP file from
Stephen Bullen's site. See instructions and d/l file from:

http://www.bmsltd.co.uk/MVP/Default.htm

Gord Dibben Excel MVP - XL97 SR2 & XL2002

On Wed, 30 Jul 2003 16:28:45 GMT, bw wrote:

I'm so confused!!!

Some history...

I have a workbook that I created maybe eight years ago, that has some simple macros
that are run ONLY from this workbook, and are accessed by clicking on Tools menu,
where the macros are listed at the bottom of the normal list. This macros (and the
items
on the Tools menu) are ONLY available for this particular workbook.

I have tried to duplicate this action in two ways:
1. I have simply dragged an item from the toolbar customize box to the Tools menu,
and
this works fine, but then it is present on any workbook I open from then on, and of
course, I only want it to be present for this workbook.

2. I have saved the original workbook as a new workbook, then deleted everything in
the new workbook, including one of the Tools Menu items, and reassigned new
macros
to the Tools Menu items. I save this new workbook, close it, reopen it, and the Tools
menu looks just as it did without saving it.

What I want to do is create macros, assign them to a toolbar (especially if it is a Tools
menu item), and make these menu items (or toolbar buttons) available ONLY to this
workbook. I am having no luck accomplishing this.

Can anyone explain why I'm having these problems?

Thanks,
Bernie