workbook event, only to certain sheets

[art]

Hello:

I have a Workbook_SheetBeforeDoubleClick and Workbook_SheetBeforeRightClick
event that does certain things when triggered. How ever, I need this event to
apply only to certain sheets. I have about 20 sheets, and I don't want to put
it in each sheet seperatly. Is there a way to force the workbook event to
kick in only for specific sheets?

Also, I need the event to apply only to a certain range in the sheet. How
can I apply that as well to the code?

Please help me figure this out.

Thanks for your help.

 

[Tom Hutchins]

The events you mentioned apply to the ThisWorkbook module. Just check the
name of the sheet from which the event was called. If it is one of the sheets
where you don't want the code to work, exit the sub.

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Select Case Sh.Name
Case "NotThisSheet1", "NotThisSheet2", "NotThisSheet3"
Exit Sub
Case Else
'do your event code
End Select
End Sub

Hope this helps,

Hutch

 

[art]

Thanks, it works great, however, how can I make that I should not need to
enter the "Name" of the sheet, rather I should enter which sheet number it
is. Like worksheet(1)...

Thanks.

 

[Tom Hutchins]

Maybe something like

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
Dim x As Long
For x = 1 To Sheets.Count
If Sheets(x).Name = Sh.Name Then
Select Case x
Case 3, 5, 7 'skip these sheets
Exit For
Case Else
'your event code
End Select
End If
Next x
End Sub

Hope this helps,

Hutch

 

[art]

What about ranges? How can I make that the event should only work in a
certain range?

 

[Chip Pearson]

What about ranges? How can I make that the event should only work in a

certain range?

You can't restrict an event to trigger for only a range of cells. You
can, however, test the Target parameter, which is a reference to the
cell that initiated the event. If Target is within some range, run
your code. Otherwise, don't run the code. For example, the following
code shows how to restrict BeforeDoubleClick to take action only if
the user double-clicks somewhere within A1:A10.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Not Application.Intersect(Target, _
Me.Range("A1:A10")) Is Nothing Then
'''''''''''''''''''''''''''''
' your event code goes here
'''''''''''''''''''''''''''''
End If
End Sub


Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Gord Dibben]

Private Sub Workbook_SheetBeforeDoubleClick(ByVal _
Sh As Object, ByVal Target As Range, Cancel As Boolean)
Const MY_RANGE As String = "A1:A10" 'adjust to suit
Dim x As Long
For x = 1 To Sheets.Count
If Sheets(x).Name = Sh.Name Then
Select Case x
Case 3, 5, 7 'skip these sheets
Exit For
Case Else
If Not Intersect(Target, ActiveSheet.Range(MY_RANGE)) Is Nothing Then
MsgBox "you have d-clicked on " & ActiveCell.Address
Cancel = True
End If
End Select
End If
Next x
End Sub


Gord Dibben MS Excel MVP

 

[art]

I have a named range "GridCell". How can I restict it only to that range?

 

[art]

There is a error saying method or data member not found?

 

[Chip Pearson]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Not Application.Intersect(Target, _
Range("GridCell")) Is Nothing Then
''''''''''''''''''''''''''''''''''
' do your double-click code here
'''''''''''''''''''''''''''''''''
Cancel = True
End If
End Sub

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Chip Pearson]

What exactly does the error message say (details count) and on what
line of code does execution terminate (that line will be highlighted
in yellow)?

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[art]

thanks it works now. One more thing. The range gridcell was named for cell in
sheet1, however I want the named range to apply to all sheets, how can I do
this?