Custom menu (Office 2002)

[Mike_R]

Ok, now back to our show...

The following should run nicely. It's designed to be placed in the 'ThisWorkbook' class module:

Option Explicit
Dim WithEvents m_cbButton As Office.CommandBarButton
Const m_TAG = "My Unique Tag"
 
Private Sub m_cbButton_Click(ByVal Ctrl As Office.CommandBarButton, _
CancelDefault As Boolean)
' The Sub MyMacro() is where your code to Hide/Show rows would be:
Call MyMacro
End Sub
 
Private Sub Workbook_Open()
Dim cbBar As Office.CommandBar
Set cbBar = Application.CommandBars(1)
' (Note that CommandBars(1) is the "Worksheet Menu Bar".)
On Error Resume Next
' Try to find the existing CommandBarButton, if present:
Set m_cbButton = cbBar.FindControl(msoControlButton, Tag:=m_TAG)
On Error GoTo 0
If m_cbButton Is Nothing Then
    ' If we did not find the CommandBarButton, then we make it:
    Set m_cbButton = cbBar.Controls.Add(msoControlButton, Temporary:=True)
    m_cbButton.Tag = m_TAG
    m_cbButton.FaceId = 2950 ' <-- 2950 is a Smiley Face :-)
End If
End Sub
 
Private Sub Workbook_Activate()
m_cbButton.Visible = True
End Sub
 
Private Sub Workbook_Deactivate()
m_cbButton.Visible = False
End Sub[/vba] Within a standard code module, you would then need code that looks something like this:[vba]Sub MyMacro()
If ThisWorkbook Is ActiveWorkbook Then 
    ' Your code goes here.
    ' Your code goes here.
    ' Your code goes here.
End If
End Sub

The point of this is to avoid all your workbooks from fireing every time you hit this button, for it sounds like your Macro is designed to apply to only the ActiveWorkbook.

See the attached Workbook, which you can make copies of and try running 2 or 3 of them at the same time along with Workbooks that do not have this button-related code within it. You'll see that it runs pretty smoothly...

I hope this helps! ,
Mike

[Edit: Hmm... struggling with the VB-Tags a bit! Ok, switched to Code-Tags You really have a great site here.]