SheetBeforeRightClick stops firing if macro populates data

[MAB]

The SheetBeforeRightClick event I created works initially when I open the
workbook but if I (with a macro) clear all the data (except header row) then
repopulate with new data, SheetBeforeRightClick stops firing? Any ideas why?

[Jim Thomlinson]

Something to look for would be that you set application.enableevents to false
and then don't turn it back on again. At that point no events will be firing
and your double click will not register. Other than that you need to post
some code so we can see what you are talking about...
--
HTH...

Jim Thomlinson


"MAB" wrote:

> The SheetBeforeRightClick event I created works initially when I open the
> workbook but if I (with a macro) clear all the data (except header row) then
> repopulate with new data, SheetBeforeRightClick stops firing? Any ideas why?

[MAB]

I am not setting application.enableevents to false.

'Class module ---------------------------------------------------------

Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Excel.Application
End Sub

'Doesn't seem to auto-execute
'Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
' MsgBox "xlApp_WorkbookOpen"
'End Sub

Private Sub xlApp_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)

MsgBox "xlApp_SheetBeforeRightClick " & Target.Address & " " &
Target.Hyperlinks.Count
'Add or remove Hyperlink menu items based on whether or not Hyperlink(s)
are selected

Dim oCtrl As CommandBarControl
Dim oBtn As CommandBarControl
Dim bMenusExist As Boolean
Dim bHyperlinksExist As Boolean

For Each oCtrl In Application.CommandBars("Cell").Controls
If oCtrl.Caption = "Copy Hyperlinked Docs To..." Then
bMenusExist = True
Exit For
End If
Next

bHyperlinksExist = Target.Hyperlinks.Count > 0

If bHyperlinksExist And Not bMenusExist Then
Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Temporary:=True)
oCtrl.Caption = "Copy Hyperlinked Docs To..."
oCtrl.OnAction = "CopyHyperlinkedDocsTo"

Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
Temporary:=True)
oCtrl.Caption = "Zip && Email Hyperlinked Docs..."
oCtrl.OnAction = "ZipAndEmailHyperlinkedDocs"

' Set oCtrl =
Application.CommandBars("Cell").Controls.Add(Type:=msoControlButton, _
' Temporary:=True)
' oCtrl.Caption = "Print Hyperlinked Docs..."
' oCtrl.OnAction = "PrintHyperlinkedDocs"
ElseIf Not bHyperlinksExist Then
For Each oCtrl In Application.CommandBars("Cell").Controls
If oCtrl.Caption = "Copy Hyperlinked Docs To..." Then
oCtrl.Delete
ElseIf oCtrl.Caption = "Zip && Email Hyperlinked Docs..." Then
oCtrl.Delete
ElseIf oCtrl.Caption = "Print Hyperlinked Docs..." Then
oCtrl.Delete
End If
Next
End If
End Sub

'Regular module --------------------------------------------------------------

Public moXLEvents As clsXLEvents



'ThisWorkbook -----------------------------------------------------------

Private Sub Workbook_Open()
Dim oWorksheet As Worksheet

Set moXLEvents = New clsXLEvents
Set oWorksheet = ThisWorkbook.ActiveSheet
If oWorksheet.Range("a2").Value = "" Then
ListNewerFiles.Show vbModal
End If
End Sub


"Jim Thomlinson" wrote:

> Something to look for would be that you set application.enableevents to false
> and then don't turn it back on again. At that point no events will be firing
> and your double click will not register. Other than that you need to post
> some code so we can see what you are talking about...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "MAB" wrote:
>
> > The SheetBeforeRightClick event I created works initially when I open the
> > workbook but if I (with a macro) clear all the data (except header row) then
> > repopulate with new data, SheetBeforeRightClick stops firing? Any ideas why?