Macro - Excel 2003

D

Dan

We recently upgraded to Excel 2003 (I'm not a fan) from Excel 2000. I have a
'right click' menu macro that does a simple filter on another sheet. Since
the upgrade, this macro opens an entirely unrelated workbook. There are no
file links, and the macro does not reference this workbook in anyway. After
the workbook opens, all is well; I can even close the workbook and the macro
will work without a 're-open'.

What am I missing?
 
D

Dan

Here is the 'right click' macro code"
'Call sub to add right click menu item on workbook open
Private Sub Workbook_Open()
call NewItem
End Sub

'Sub to add menu if not already there
Sub NewItem()
On Error GoTo one
If CommandBars("Cell").Controls("See Journals").Visible Then
Exit Sub
End If

one:
Dim ThisItem As Object
Set ThisItem = CommandBars("Cell").Controls.Add
With ThisItem
..Caption = "See Journals"
..OnAction = "See_Journals"
..BeginGroup = True
End With
End Sub

'Sub action and filter
Sub see_journals()
a = ActiveCell.Column
x = ActiveCell.Value
If a <> 3 Or x = "" Then
MsgBox "Please place your cursor on an account number."
Exit Sub
End If
Worksheets("Journal Details").Select
Selection.AutoFilter Field:=5, Criteria1:=x
End Sub
 
D

Dave Peterson

I'm betting that the "See Journals" option on the cell commandbar is still
visible. That means that the .onaction doesn't get reassigned to the workbook
with the code--it's still pointing to the other workbook.

And that other workbook has to be opened for the "See_Journal" macro to run.

I wouldn't check the .visible property. I'd just delete the item and add it.

Sub NewItem()

Dim ThisItem As CommandbarControl

On Error resume next
application.CommandBars("Cell").Controls("See Journals").delete
on error goto 0

Set ThisItem = application.CommandBars("Cell").Controls.Add
With ThisItem
.Caption = "See Journals"
.OnAction = "'" & thisworkbook.name & "'!" & "See_Journals"
.BeginGroup = True
End With
End Sub

Untested, uncompiled. Watch for typos.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top