Macro - Excel 2003
 

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?

Macro - Excel 2003
 

You might want to post the code, it will make it easier to answer your
question.
--
Kevin Backmann


"Dan" wrote:

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?

Macro - Excel 2003
 

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


"Kevin B" wrote:

You might want to post the code, it will make it easier to answer your
question.
--
Kevin Backmann


"Dan" wrote:

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?

Macro - Excel 2003
 

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.


Dan wrote:

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

"Kevin B" wrote:

You might want to post the code, it will make it easier to answer your
question.
--
Kevin Backmann


"Dan" wrote:

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?

--

Dave Peterson