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!!!!!

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,

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!!!!!

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

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

Dave,

About conflicting event ranges: I got it. Thank you very much.

Connected to my first question another one.
How nice would it be (for me) if a button on a worksheet (such as the one
with the find function) would not get out of sight when the sheet is
scrolled to the left or to the right. So a kind of "floating" button that
always stays in sight. Can't have it on a fixed toolbar and fixing it to a
custom toolbar is not very practical because I would have to enable that
toolbar every time I want to use that button. Fixing it to a cell (for
instance A1) in the left most column and fixing that column (don't know the
proper phrase) is also not disirable.

Is it possible to make a worksheet button that goes along with the
scrolling?

Jack.



"Jack Sons" schreef in bericht
...
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

I've assigned my version of that macro to ctrl-f (actually, that's the keyboard
shortcut key).

But you could also customize an existing toolbar (or create a new one) that
shows that dialog.

tools|customize|commands tab|Edit category
Near the bottom, there's a binoculars icon (Find).

Just drag it to your favorite toolbar.



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