Right Click Dropdown
 

I run several reports with ledger data and journal data. I added a right
click drop down allowing a user to look up journals from the ledger data.
All works well except for .... I added a 'before close' piece of code to
delete the drop down items - don't wish them to be there if the file is not
in use. I assumed only one file would be open at a time - wrong. If a
second file is opened and either the first or second is closed, the menu
items go away. Is there an easy way to interrogate the open files and leave
the drop down if a file that needs it is open?

Module Code
Sub see_journals()
a = ActiveCell.Column
X = ActiveCell.Value
If a < 4 Or X = "" Then
MsgBox "Please place your cursor on an account number."
Exit Sub
End If
Worksheets("Journal Details").Select
Range("Journal").Select
Selection.AutoFilter Field:=5, Criteria1:=X
End Sub

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 RidIt()
On Error Resume Next
CommandBars("Cell").Controls("See Journals").Delete
End Sub

ThisWorkbook Code
Private Sub Workbook_BeforeClose(Cancel As Boolean)
RidIt
End Sub

Right Click Dropdown
 

My choice would be to remove the menu on deactivate and re-add it on
activate. The overhead is minimal and the option will only only available on
sheets that need it.

Private Sub Workbook_Deactivate()
RidIt
End Sub

Private Sub Workbook_Activate()
AddIt 'assuming that is the name of the procedure to add the menu control.
End Sub

--
HTH...

Jim Thomlinson


"Dan" wrote:

I run several reports with ledger data and journal data. I added a right
click drop down allowing a user to look up journals from the ledger data.
All works well except for .... I added a 'before close' piece of code to
delete the drop down items - don't wish them to be there if the file is not
in use. I assumed only one file would be open at a time - wrong. If a
second file is opened and either the first or second is closed, the menu
items go away. Is there an easy way to interrogate the open files and leave
the drop down if a file that needs it is open?

Module Code
Sub see_journals()
a = ActiveCell.Column
X = ActiveCell.Value
If a < 4 Or X = "" Then
MsgBox "Please place your cursor on an account number."
Exit Sub
End If
Worksheets("Journal Details").Select
Range("Journal").Select
Selection.AutoFilter Field:=5, Criteria1:=X
End Sub

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 RidIt()
On Error Resume Next
CommandBars("Cell").Controls("See Journals").Delete
End Sub

ThisWorkbook Code
Private Sub Workbook_BeforeClose(Cancel As Boolean)
RidIt
End Sub

Right Click Dropdown
 

Thanks Jim, worked great.

It's amazing how little one knows about this stuff, but can still do some
pretty good things.

"Jim Thomlinson" wrote:

My choice would be to remove the menu on deactivate and re-add it on
activate. The overhead is minimal and the option will only only available on
sheets that need it.

Private Sub Workbook_Deactivate()
RidIt
End Sub

Private Sub Workbook_Activate()
AddIt 'assuming that is the name of the procedure to add the menu control.
End Sub

--
HTH...

Jim Thomlinson


"Dan" wrote:

I run several reports with ledger data and journal data. I added a right
click drop down allowing a user to look up journals from the ledger data.
All works well except for .... I added a 'before close' piece of code to
delete the drop down items - don't wish them to be there if the file is not
in use. I assumed only one file would be open at a time - wrong. If a
second file is opened and either the first or second is closed, the menu
items go away. Is there an easy way to interrogate the open files and leave
the drop down if a file that needs it is open?

Module Code
Sub see_journals()
a = ActiveCell.Column
X = ActiveCell.Value
If a < 4 Or X = "" Then
MsgBox "Please place your cursor on an account number."
Exit Sub
End If
Worksheets("Journal Details").Select
Range("Journal").Select
Selection.AutoFilter Field:=5, Criteria1:=X
End Sub

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 RidIt()
On Error Resume Next
CommandBars("Cell").Controls("See Journals").Delete
End Sub

ThisWorkbook Code
Private Sub Workbook_BeforeClose(Cancel As Boolean)
RidIt
End Sub