Dave,

Thanks a lot, I hope to find time during the weekend to try it out.

Jack.


"Dave Peterson" schreef in bericht
...
Yep. You only get one of each type of event within each sheet module.

But you could check to see what range you're in and process based on that.

If you have two ranges, you can have
1. No intersection between them (mutually exclusive).
2. An overlap of at least one cell.

If the ranges are mutually exclusive, then it's pretty straight forward.
Figure
out which one you're in (if either) and do the processing based on that.

If the ranges overlap, then you have a decision to make. Do you do the
first
procedure or the second procedure or both?

(Doing one of them, but not the other resolves to the first case, too.)

You have an overlap of a couple of cells.

If you're in column E, do one thing. But what should occur if you're in
E1--that cell is in both ranges.

I decided <vbg that you wanted to do only one thing and that one thing is
to
show the .find dialog.

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

Dim myRng1 As Range
Dim myRng2 As Range

If Target.Cells.Count 1 Then Exit Sub

With Me
'stay away from row 1???
Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _
.Rows("2:" & .Rows.Count))
Set myRng2 = Me.Range("e1:g1")

If Not (Intersect(Target, myRng1) Is Nothing) Then
'it's something
'do your code for stuff that's in e,as,bt
'cancel = true
ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then
'do your code for stuff that's in e1,g1
Application.EnableEvents = False
.Range("E:E,G:G").Select
.Range("G1").Activate
Application.EnableEvents = True
Application.Dialogs(xlDialogFormulaFind).Show
'cancel = true
End If
End With

End Sub

======
I left the "cancel = true" commented out. But I would think that you
would want
that code uncommented.

If your user has edit directly in cell, double clicking will start that
process. "Cancel = true" stops that from happening.

And in the _BeforeRightClick event, "cancel=true" will stop the popup from
showing up.

(I'm betting that you don't want either to occur, but you'll have to
uncomment
it.)

=========
And since the routine that occurs with rightclicking also occurs with
doubleclicking in a certain area, you can put that code in one spot and
then
when something changes, you don't have to fix two routines:

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)

Dim myRng1 As Range
Dim myRng2 As Range

If Target.Cells.Count 1 Then Exit Sub

With Me
'stay away from row 1???
Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _
.Rows("2:" & .Rows.Count))
Set myRng2 = Me.Range("e1:g1")

If Not (Intersect(Target, myRng1) Is Nothing) Then
'it's something
'do your code for stuff that's in e,as,bt
MsgBox "myRng1"
Cancel = True
ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then
'do your code for stuff that's in e1,g1
Call Worksheet_BeforeRightClick(Target, True)
Cancel = True
End If
End With

End Sub
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)

With Me
'do your code for stuff that's in e1,g1
Application.EnableEvents = False
.Range("E:E,G:G").Select
.Range("G1").Activate
Application.EnableEvents = True
Application.Dialogs(xlDialogFormulaFind).Show
Cancel = True
End With

End Sub

ps. The application.enableevents stuff is toggled off just in case you
have a
_selectionchange event, too.

Jack Sons wrote:

Dave,

I could use in stead of a find button on my spreadsheet that would
automatically let the find function work in 2 non-consecutive columns
(in my
case E and G), a macro that does the same if E1, F1 or G1 is double
clicked
(or right clicked). Perhaps something like the code below the dotted
line
could work (if you would be so kind to correct it for me). But in the
code
module of the worksheet I already have another worksheet procedure that
is
triggered by a double click, it begins with:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target,
Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT"))
Is
Nothing Then Exit Sub

and also a procedure that reacts to a right click. It begins with:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim myShtCtBar As Object
If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then
Select Case Selection.Column

It seems that you can have only one eventprocedure of a kind in a
worksheet
code module. Is there a way out?


--------------------------------------------------------------------------
--
-----
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)

If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub

Range("E:E,G:G").Select
Range("G1").Activate
Application.Dialogs(xlDialogFormulaFind).Show

End If

End Sub

Jack Sons
The Netherlands

"Peo Sjoblom" schreef in bericht
...
Try

Application.Dialogs(xlDialogFormulaFind).Show


Regards,

Peo Sjoblom

"marika1981" wrote:

I'm trying to write a macro (which I'll assign to an on-screen
button)
that
simply brings up the Find dialogue box. Thus, you press the button
and
the
Find box appears.

When I try to record the macro, it requires I exit the dialogue box
before
stopping recording.

Any ideas?????

Thank you!!!!!

--

Dave Peterson