Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
marika1981
 
Posts: n/a
Default Macro to simply bring up the Find dialogue box??

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!!!!!
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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

  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #4   Report Post  
Jack Sons
 
Posts: n/a
Default

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



  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #6   Report Post  
Jack Sons
 
Posts: n/a
Default

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



  #7   Report Post  
Jack Sons
 
Posts: n/a
Default

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





  #8   Report Post  
Dave Peterson
 
Posts: n/a
Default

I'm not quite sure I understand the problem with the toolbar.

If you create a new toolbar (tools|customize|toolbars tab) and assign your macro
to that custom toolbar, then you could keep that toolbar shown all the time (or
hide it and then reshow it on demand).

When you click on the button on that toolbar, excel will open that other
workbook -- if it's not open already.

You wouldn't need to keep that macro workbook open. Excel will take care of the
housekeeping.



Jack Sons wrote:

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




--

Dave Peterson
  #9   Report Post  
Jack Sons
 
Posts: n/a
Default

Dave,

Maybe my phrase "worksheet button" is wrong, I don't know the correct name.

I mean this.

I can assign a macro (any macro) to either a button on one of the toolbars
at the upperside of my screen, above the worksheet, or to a button that is
fixed to a specific worksheet. The latter I meant by "worksheet button". The
advantage of a worksheet button is that you don't clog the permanent
toolbars with all kind of buttons that you only need with specific
worksheets.

When I assign a macro (any macro) to a button on the worksheet, that button
is fixed (nailed down) to the specific position where I did put it, say for
example C1. When the worksheet is scrolled to the right the button
disappears out of sight. If the sheet is scrolled to te region of, say,
column AP and this button has to be used, you have to scroll back to the
region where column C - and the button - is visible again. After you get the
result from clicking the button you have to scroll back to the AP region,
which is a bit tedious.

What I would like is that the button "floats" on the worksheet so that it
stays in sight when the sheet is scrolled to the right (or the left).

Hope I made it clear this time. Is what I want possible and if so, how?

Thanks in advance.

Jack.
"Dave Peterson" schreef in bericht
...
I'm not quite sure I understand the problem with the toolbar.

If you create a new toolbar (tools|customize|toolbars tab) and assign your

macro
to that custom toolbar, then you could keep that toolbar shown all the

time (or
hide it and then reshow it on demand).

When you click on the button on that toolbar, excel will open that other
workbook -- if it's not open already.

You wouldn't need to keep that macro workbook open. Excel will take care

of the
housekeeping.



Jack Sons wrote:

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



--

Dave Peterson



  #10   Report Post  
Dave Peterson
 
Posts: n/a
Default

It sure sounds like you're describing a toolbar to me. (I think it would be
easiest to just let it float (don't dock it to the top of the application
window).)

But if you want, maybe something like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("Button 1")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

Rightclick on the worksheet that should have this behavior. Select view code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you may want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the user moves the
screen using the scroll bars, then it won't show up until they select a range.

Jack Sons wrote:

Dave,

Maybe my phrase "worksheet button" is wrong, I don't know the correct name.

I mean this.

I can assign a macro (any macro) to either a button on one of the toolbars
at the upperside of my screen, above the worksheet, or to a button that is
fixed to a specific worksheet. The latter I meant by "worksheet button". The
advantage of a worksheet button is that you don't clog the permanent
toolbars with all kind of buttons that you only need with specific
worksheets.

When I assign a macro (any macro) to a button on the worksheet, that button
is fixed (nailed down) to the specific position where I did put it, say for
example C1. When the worksheet is scrolled to the right the button
disappears out of sight. If the sheet is scrolled to te region of, say,
column AP and this button has to be used, you have to scroll back to the
region where column C - and the button - is visible again. After you get the
result from clicking the button you have to scroll back to the AP region,
which is a bit tedious.

What I would like is that the button "floats" on the worksheet so that it
stays in sight when the sheet is scrolled to the right (or the left).

Hope I made it clear this time. Is what I want possible and if so, how?

Thanks in advance.

Jack.
"Dave Peterson" schreef in bericht
...
I'm not quite sure I understand the problem with the toolbar.

If you create a new toolbar (tools|customize|toolbars tab) and assign your

macro
to that custom toolbar, then you could keep that toolbar shown all the

time (or
hide it and then reshow it on demand).

When you click on the button on that toolbar, excel will open that other
workbook -- if it's not open already.

You wouldn't need to keep that macro workbook open. Excel will take care

of the
housekeeping.



Jack Sons wrote:

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



--

Dave Peterson


--

Dave Peterson


  #11   Report Post  
Dave Peterson
 
Posts: n/a
Default

Just to add: With these type event macros (and macros in general), you may lose
the undo stack.



Dave Peterson wrote:

It sure sounds like you're describing a toolbar to me. (I think it would be
easiest to just let it float (don't dock it to the top of the application
window).)

But if you want, maybe something like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("Button 1")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

Rightclick on the worksheet that should have this behavior. Select view code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you may want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the user moves the
screen using the scroll bars, then it won't show up until they select a range.

Jack Sons wrote:

Dave,

Maybe my phrase "worksheet button" is wrong, I don't know the correct name.

I mean this.

I can assign a macro (any macro) to either a button on one of the toolbars
at the upperside of my screen, above the worksheet, or to a button that is
fixed to a specific worksheet. The latter I meant by "worksheet button". The
advantage of a worksheet button is that you don't clog the permanent
toolbars with all kind of buttons that you only need with specific
worksheets.

When I assign a macro (any macro) to a button on the worksheet, that button
is fixed (nailed down) to the specific position where I did put it, say for
example C1. When the worksheet is scrolled to the right the button
disappears out of sight. If the sheet is scrolled to te region of, say,
column AP and this button has to be used, you have to scroll back to the
region where column C - and the button - is visible again. After you get the
result from clicking the button you have to scroll back to the AP region,
which is a bit tedious.

What I would like is that the button "floats" on the worksheet so that it
stays in sight when the sheet is scrolled to the right (or the left).

Hope I made it clear this time. Is what I want possible and if so, how?

Thanks in advance.

Jack.
"Dave Peterson" schreef in bericht
...
I'm not quite sure I understand the problem with the toolbar.

If you create a new toolbar (tools|customize|toolbars tab) and assign your

macro
to that custom toolbar, then you could keep that toolbar shown all the

time (or
hide it and then reshow it on demand).

When you click on the button on that toolbar, excel will open that other
workbook -- if it's not open already.

You wouldn't need to keep that macro workbook open. Excel will take care

of the
housekeeping.



Jack Sons wrote:

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



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #12   Report Post  
Jack Sons
 
Posts: n/a
Default

Dave,

Thank you very much, this is the solution.
But .. I am afraid I don't grasp the floating toolbar stuff. Is it possible
to have a custom toolbar in sight - that stays in sight - as soon as the
worksheet (or workbook?) is opened? That has the advantage that one could
have more than one button to use.

Jack.

"Dave Peterson" schreef in bericht
...
Just to add: With these type event macros (and macros in general), you

may lose
the undo stack.



Dave Peterson wrote:

It sure sounds like you're describing a toolbar to me. (I think it

would be
easiest to just let it float (don't dock it to the top of the

application
window).)

But if you want, maybe something like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("Button 1")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

Rightclick on the worksheet that should have this behavior. Select view

code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you may

want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the user

moves the
screen using the scroll bars, then it won't show up until they select a

range.

Jack Sons wrote:

Dave,

Maybe my phrase "worksheet button" is wrong, I don't know the correct

name.

I mean this.

I can assign a macro (any macro) to either a button on one of the

toolbars
at the upperside of my screen, above the worksheet, or to a button

that is
fixed to a specific worksheet. The latter I meant by "worksheet

button". The
advantage of a worksheet button is that you don't clog the permanent
toolbars with all kind of buttons that you only need with specific
worksheets.

When I assign a macro (any macro) to a button on the worksheet, that

button
is fixed (nailed down) to the specific position where I did put it,

say for
example C1. When the worksheet is scrolled to the right the button
disappears out of sight. If the sheet is scrolled to te region of,

say,
column AP and this button has to be used, you have to scroll back to

the
region where column C - and the button - is visible again. After you

get the
result from clicking the button you have to scroll back to the AP

region,
which is a bit tedious.

What I would like is that the button "floats" on the worksheet so that

it
stays in sight when the sheet is scrolled to the right (or the left).

Hope I made it clear this time. Is what I want possible and if so,

how?

Thanks in advance.

Jack.
"Dave Peterson" schreef in bericht
...
I'm not quite sure I understand the problem with the toolbar.

If you create a new toolbar (tools|customize|toolbars tab) and

assign your
macro
to that custom toolbar, then you could keep that toolbar shown all

the
time (or
hide it and then reshow it on demand).

When you click on the button on that toolbar, excel will open that

other
workbook -- if it's not open already.

You wouldn't need to keep that macro workbook open. Excel will take

care
of the
housekeeping.



Jack Sons wrote:

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



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson



  #13   Report Post  
Dave Peterson
 
Posts: n/a
Default

First, tools|customize|toolbars tab.

Create a new toolbar. Notice that it's initial location is just floating on the
worksheet. You can do that with other toolbars, too. Just grab it (by the grab
handle on the far left) and drag it down to where you want it.

You could do that with any custom toolbar you create. Personally, I like to
load the toolbar so it's floating. It gets the user's attention (since it's in
the way). If they want to dock it at the top/side/bottom, they can.)

Some alternatives:

You could attach a toolbar to a workbook.

If you're going to do that, you'll want to read Jan Karel Pieterse's notes:
http://google.com/groups?threadm=083...0a% 40phx.gbl

You could build the toolbar on the fly within that workbook:
http://groups.google.co.uk/groups?th...5B41%40msn.com

That link has a link to John Walkenbach's MenuMaker program. It adds items to
the worksheet toolbar.

=====
If the macro is supposed to be used against multiple workbooks, I would try to
separate the macro from the data. Save your macro workbook as an addin (*.xla)
and load that whenever you need it.



Jack Sons wrote:

Dave,

Thank you very much, this is the solution.
But .. I am afraid I don't grasp the floating toolbar stuff. Is it possible
to have a custom toolbar in sight - that stays in sight - as soon as the
worksheet (or workbook?) is opened? That has the advantage that one could
have more than one button to use.

Jack.

"Dave Peterson" schreef in bericht
...
Just to add: With these type event macros (and macros in general), you

may lose
the undo stack.



Dave Peterson wrote:

It sure sounds like you're describing a toolbar to me. (I think it

would be
easiest to just let it float (don't dock it to the top of the

application
window).)

But if you want, maybe something like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("Button 1")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

Rightclick on the worksheet that should have this behavior. Select view

code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you may

want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the user

moves the
screen using the scroll bars, then it won't show up until they select a

range.

Jack Sons wrote:

Dave,

Maybe my phrase "worksheet button" is wrong, I don't know the correct

name.

I mean this.

I can assign a macro (any macro) to either a button on one of the

toolbars
at the upperside of my screen, above the worksheet, or to a button

that is
fixed to a specific worksheet. The latter I meant by "worksheet

button". The
advantage of a worksheet button is that you don't clog the permanent
toolbars with all kind of buttons that you only need with specific
worksheets.

When I assign a macro (any macro) to a button on the worksheet, that

button
is fixed (nailed down) to the specific position where I did put it,

say for
example C1. When the worksheet is scrolled to the right the button
disappears out of sight. If the sheet is scrolled to te region of,

say,
column AP and this button has to be used, you have to scroll back to

the
region where column C - and the button - is visible again. After you

get the
result from clicking the button you have to scroll back to the AP

region,
which is a bit tedious.

What I would like is that the button "floats" on the worksheet so that

it
stays in sight when the sheet is scrolled to the right (or the left).

Hope I made it clear this time. Is what I want possible and if so,

how?

Thanks in advance.

Jack.
"Dave Peterson" schreef in bericht
...
I'm not quite sure I understand the problem with the toolbar.

If you create a new toolbar (tools|customize|toolbars tab) and

assign your
macro
to that custom toolbar, then you could keep that toolbar shown all

the
time (or
hide it and then reshow it on demand).

When you click on the button on that toolbar, excel will open that

other
workbook -- if it's not open already.

You wouldn't need to keep that macro workbook open. Excel will take

care
of the
housekeeping.



Jack Sons wrote:

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



--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
  #14   Report Post  
Jack Sons
 
Posts: n/a
Default

Dave,

That's it.
Without you and all those others in this NG who are never too tired to
answer stupid or much repeated questions I would be nowhere in Excel land.

Jack.
"Dave Peterson" schreef in bericht
...
First, tools|customize|toolbars tab.

Create a new toolbar. Notice that it's initial location is just floating

on the
worksheet. You can do that with other toolbars, too. Just grab it (by

the grab
handle on the far left) and drag it down to where you want it.

You could do that with any custom toolbar you create. Personally, I like

to
load the toolbar so it's floating. It gets the user's attention (since

it's in
the way). If they want to dock it at the top/side/bottom, they can.)

Some alternatives:

You could attach a toolbar to a workbook.

If you're going to do that, you'll want to read Jan Karel Pieterse's

notes:

http://google.com/groups?threadm=083...80a% 40phx.gb
l

You could build the toolbar on the fly within that workbook:
http://groups.google.co.uk/groups?th...5B41%40msn.com

That link has a link to John Walkenbach's MenuMaker program. It adds

items to
the worksheet toolbar.

=====
If the macro is supposed to be used against multiple workbooks, I would

try to
separate the macro from the data. Save your macro workbook as an addin

(*.xla)
and load that whenever you need it.



Jack Sons wrote:

Dave,

Thank you very much, this is the solution.
But .. I am afraid I don't grasp the floating toolbar stuff. Is it

possible
to have a custom toolbar in sight - that stays in sight - as soon as the
worksheet (or workbook?) is opened? That has the advantage that one

could
have more than one button to use.

Jack.

"Dave Peterson" schreef in bericht
...
Just to add: With these type event macros (and macros in general),

you
may lose
the undo stack.



Dave Peterson wrote:

It sure sounds like you're describing a toolbar to me. (I think it

would be
easiest to just let it float (don't dock it to the top of the

application
window).)

But if you want, maybe something like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("Button 1")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

Rightclick on the worksheet that should have this behavior. Select

view
code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you

may
want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the

user
moves the
screen using the scroll bars, then it won't show up until they

select a
range.

Jack Sons wrote:

Dave,

Maybe my phrase "worksheet button" is wrong, I don't know the

correct
name.

I mean this.

I can assign a macro (any macro) to either a button on one of the

toolbars
at the upperside of my screen, above the worksheet, or to a button

that is
fixed to a specific worksheet. The latter I meant by "worksheet

button". The
advantage of a worksheet button is that you don't clog the

permanent
toolbars with all kind of buttons that you only need with specific
worksheets.

When I assign a macro (any macro) to a button on the worksheet,

that
button
is fixed (nailed down) to the specific position where I did put

it,
say for
example C1. When the worksheet is scrolled to the right the button
disappears out of sight. If the sheet is scrolled to te region of,

say,
column AP and this button has to be used, you have to scroll back

to
the
region where column C - and the button - is visible again. After

you
get the
result from clicking the button you have to scroll back to the AP

region,
which is a bit tedious.

What I would like is that the button "floats" on the worksheet so

that
it
stays in sight when the sheet is scrolled to the right (or the

left).

Hope I made it clear this time. Is what I want possible and if so,

how?

Thanks in advance.

Jack.
"Dave Peterson" schreef in bericht
...
I'm not quite sure I understand the problem with the toolbar.

If you create a new toolbar (tools|customize|toolbars tab) and

assign your
macro
to that custom toolbar, then you could keep that toolbar shown

all
the
time (or
hide it and then reshow it on demand).

When you click on the button on that toolbar, excel will open

that
other
workbook -- if it's not open already.

You wouldn't need to keep that macro workbook open. Excel will

take
care
of the
housekeeping.



Jack Sons wrote:

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



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson



  #15   Report Post  
Dave Peterson
 
Posts: n/a
Default

Glad you got it sorted out.



Jack Sons wrote:

Dave,

That's it.
Without you and all those others in this NG who are never too tired to
answer stupid or much repeated questions I would be nowhere in Excel land.

Jack.
"Dave Peterson" schreef in bericht
...
First, tools|customize|toolbars tab.

Create a new toolbar. Notice that it's initial location is just floating

on the
worksheet. You can do that with other toolbars, too. Just grab it (by

the grab
handle on the far left) and drag it down to where you want it.

You could do that with any custom toolbar you create. Personally, I like

to
load the toolbar so it's floating. It gets the user's attention (since

it's in
the way). If they want to dock it at the top/side/bottom, they can.)

Some alternatives:

You could attach a toolbar to a workbook.

If you're going to do that, you'll want to read Jan Karel Pieterse's

notes:

http://google.com/groups?threadm=083...80a% 40phx.gb
l

You could build the toolbar on the fly within that workbook:
http://groups.google.co.uk/groups?th...5B41%40msn.com

That link has a link to John Walkenbach's MenuMaker program. It adds

items to
the worksheet toolbar.

=====
If the macro is supposed to be used against multiple workbooks, I would

try to
separate the macro from the data. Save your macro workbook as an addin

(*.xla)
and load that whenever you need it.



Jack Sons wrote:

Dave,

Thank you very much, this is the solution.
But .. I am afraid I don't grasp the floating toolbar stuff. Is it

possible
to have a custom toolbar in sight - that stays in sight - as soon as the
worksheet (or workbook?) is opened? That has the advantage that one

could
have more than one button to use.

Jack.

"Dave Peterson" schreef in bericht
...
Just to add: With these type event macros (and macros in general),

you
may lose
the undo stack.



Dave Peterson wrote:

It sure sounds like you're describing a toolbar to me. (I think it
would be
easiest to just let it float (don't dock it to the top of the
application
window).)

But if you want, maybe something like this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myShape As Shape

Set myShape = Me.Shapes("Button 1")

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
myShape.Top = .Top
myShape.Left = .Left
End With

End Sub

Rightclick on the worksheet that should have this behavior. Select

view
code
and paste this into that codewindow.

If you did window|freeze panes, so that row 1 is always visible, you

may
want to
change:

With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn)
to
With Me.Cells(1, ActiveWindow.ScrollColumn)

(or whatever row you want the button in).

This routine moves the button when the selection changes. If the

user
moves the
screen using the scroll bars, then it won't show up until they

select a
range.

Jack Sons wrote:

Dave,

Maybe my phrase "worksheet button" is wrong, I don't know the

correct
name.

I mean this.

I can assign a macro (any macro) to either a button on one of the
toolbars
at the upperside of my screen, above the worksheet, or to a button
that is
fixed to a specific worksheet. The latter I meant by "worksheet
button". The
advantage of a worksheet button is that you don't clog the

permanent
toolbars with all kind of buttons that you only need with specific
worksheets.

When I assign a macro (any macro) to a button on the worksheet,

that
button
is fixed (nailed down) to the specific position where I did put

it,
say for
example C1. When the worksheet is scrolled to the right the button
disappears out of sight. If the sheet is scrolled to te region of,
say,
column AP and this button has to be used, you have to scroll back

to
the
region where column C - and the button - is visible again. After

you
get the
result from clicking the button you have to scroll back to the AP
region,
which is a bit tedious.

What I would like is that the button "floats" on the worksheet so

that
it
stays in sight when the sheet is scrolled to the right (or the

left).

Hope I made it clear this time. Is what I want possible and if so,
how?

Thanks in advance.

Jack.
"Dave Peterson" schreef in bericht
...
I'm not quite sure I understand the problem with the toolbar.

If you create a new toolbar (tools|customize|toolbars tab) and
assign your
macro
to that custom toolbar, then you could keep that toolbar shown

all
the
time (or
hide it and then reshow it on demand).

When you click on the button on that toolbar, excel will open

that
other
workbook -- if it's not open already.

You wouldn't need to keep that macro workbook open. Excel will

take
care
of the
housekeeping.



Jack Sons wrote:

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



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
macro to Find Replace in Excel Nurddin Excel Discussion (Misc queries) 7 January 3rd 05 05:29 AM
Macro to find and delete all FALSE statements Woody13 Excel Discussion (Misc queries) 3 December 9th 04 12:16 AM
I need a macro to find cut and paste data to new cell Rex Excel Discussion (Misc queries) 0 December 6th 04 01:45 AM
I need a macro to find cut and paste data to new cell Rex Excel Discussion (Misc queries) 0 December 6th 04 01:23 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM


All times are GMT +1. The time now is 08:31 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"