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

At least in Excel 2000, there's no Built-in dialog object to call for the Find function. There are many built-ins you can call with the following syntax (this opens the Find File dialog in Excel):

dlgAnswer = Application.Dialogs(xlDialogFindFile).Show


You would need to use the InputBox to prompt the user for the search, then use the Find method...

Sub findSomething()
userFind = InputBox("Enter search term: ", "Find")
With ActiveSheet.UsedRange.Cells
Set c = .Find(userFind, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not c Is Nothing Then
firstAddress = c.Address
ActiveSheet.Range(firstAddress).Select
Else
MsgBox "No match was found."
End If
End With
End Sub


Hope this helps,
theDude

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

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

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" <PeoSjoblom@discussions.microsoft.com> schreef in bericht
news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> Try
>
> Application.Dialogs(xlDialogFormulaFind).Show
>
>
> Regards,
>
> Peo Sjoblom
>
> "marika1981" wrote:
>
> > I'm trying to write a macro (which I'll assign to an on-screen button)
that
> > simply brings up the Find dialogue box. Thus, you press the button and
the
> > Find box appears.
> >
> > When I try to record the macro, it requires I exit the dialogue box
before
> > stopping recording.
> >
> > Any ideas?????
> >
> > Thank you!!!!!

Yep. You only get one of each type of event within each sheet module.

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

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

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

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

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

You have an overlap of a couple of cells.

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

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

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

Dim myRng1 As Range
Dim myRng2 As Range

If Target.Cells.Count > 1 Then Exit Sub

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

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

End Sub

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

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

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

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

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

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

Dim myRng1 As Range
Dim myRng2 As Range

If Target.Cells.Count > 1 Then Exit Sub

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

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

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

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

End Sub

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

Jack Sons wrote:
>
> Dave,
>
> I could use in stead of a find button on my spreadsheet that would
> automatically let the find function work in 2 non-consecutive columns (in my
> case E and G), a macro that does the same if E1, F1 or G1 is double clicked
> (or right clicked). Perhaps something like the code below the dotted line
> could work (if you would be so kind to correct it for me). But in the code
> module of the worksheet I already have another worksheet procedure that is
> triggered by a double click, it begins with:
>
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> Boolean)
> If Target.Cells.Count > 1 Then Exit Sub
> If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target,
> Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is
> Nothing Then Exit Sub
>
> and also a procedure that reacts to a right click. It begins with:
>
> Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
> Boolean)
> Dim myShtCtBar As Object
> If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then
> Select Case Selection.Column
>
> It seems that you can have only one eventprocedure of a kind in a worksheet
> code module. Is there a way out?
>
> ----------------------------------------------------------------------------
> -----
> Private Sub Worksheet_BeforeDoubleClick( _
> ByVal Target As Excel.Range, Cancel As Boolean)
>
> If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub
>
> Range("E:E,G:G").Select
> Range("G1").Activate
> Application.Dialogs(xlDialogFormulaFind).Show
>
> End If
>
> End Sub
>
> Jack Sons
> The Netherlands
>
> "Peo Sjoblom" <PeoSjoblom@discussions.microsoft.com> schreef in bericht
> news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > Try
> >
> > Application.Dialogs(xlDialogFormulaFind).Show
> >
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> > "marika1981" wrote:
> >
> > > I'm trying to write a macro (which I'll assign to an on-screen button)
> that
> > > simply brings up the Find dialogue box. Thus, you press the button and
> the
> > > Find box appears.
> > >
> > > When I try to record the macro, it requires I exit the dialogue box
> before
> > > stopping recording.
> > >
> > > Any ideas?????
> > >
> > > Thank you!!!!!

--

Dave Peterson

Dave,

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

Jack.


"Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> 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" <PeoSjoblom@discussions.microsoft.com> schreef in bericht
> > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > Try
> > >
> > > Application.Dialogs(xlDialogFormulaFind).Show
> > >
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > "marika1981" wrote:
> > >
> > > > I'm trying to write a macro (which I'll assign to an on-screen
button)
> > that
> > > > simply brings up the Find dialogue box. Thus, you press the button
and
> > the
> > > > Find box appears.
> > > >
> > > > When I try to record the macro, it requires I exit the dialogue box
> > before
> > > > stopping recording.
> > > >
> > > > Any ideas?????
> > > >
> > > > Thank you!!!!!
>
> --
>
> Dave Peterson

Dave,

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

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

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

Jack.



"Jack Sons" <j.sons@planet.nl> schreef in bericht
news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> Dave,
>
> Thanks a lot, I hope to find time during the weekend to try it out.
>
> Jack.
>
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > 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" <PeoSjoblom@discussions.microsoft.com> schreef in
bericht
> > > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > Try
> > > >
> > > > Application.Dialogs(xlDialogFormulaFind).Show
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > > "marika1981" wrote:
> > > >
> > > > > I'm trying to write a macro (which I'll assign to an on-screen
> button)
> > > that
> > > > > simply brings up the Find dialogue box. Thus, you press the
button
> and
> > > the
> > > > > Find box appears.
> > > > >
> > > > > When I try to record the macro, it requires I exit the dialogue
box
> > > before
> > > > > stopping recording.
> > > > >
> > > > > Any ideas?????
> > > > >
> > > > > Thank you!!!!!
> >
> > --
> >
> > Dave Peterson
>
>

I'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" <j.sons@planet.nl> schreef in bericht
> news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> > Dave,
> >
> > Thanks a lot, I hope to find time during the weekend to try it out.
> >
> > Jack.
> >
> >
> > "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> > news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > > 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" <PeoSjoblom@discussions.microsoft.com> schreef in
> bericht
> > > > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > > 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,

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" <ec35720@netscapeXSPAM.com> schreef in bericht
news:41E46304.EF1C2FF6@netscapeXSPAM.com...
> 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" <j.sons@planet.nl> schreef in bericht
> > news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> > > Dave,
> > >
> > > Thanks a lot, I hope to find time during the weekend to try it out.
> > >
> > > Jack.
> > >
> > >
> > > "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > > > 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" <PeoSjoblom@discussions.microsoft.com> schreef in
> > bericht
> > > > > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > > > 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

This macro will create a custom toolbar when the workbook is opened and place it in the lower left corner of the Excel window (replace code in BOLD below with your own):

Sub Auto_Open()
' Create the custom toolbar...
Set newBar = CommandBars.Add(Name:="Your_Toolbar", _
Position:=msoBarBottom, Temporary:=True)
newBar.Visible = True
' Add custom button to the toolbar and assign the macro to it...
Set con = newBar.Controls.Add(Type:=msoControlButton, ID:=2949)
con.Style = msoButtonCaption
con.Caption = "Your Caption"
con.OnAction = "your_Macro_Name"
End Sub


This macro will delete the custom toolbar when the workbook is closed (replace code in BOLD below with your own):

Private Sub Auto_Close()
' Delete the custom toolbar...
Set newBar = CommandBars("Your_Toolbar")
newBar.Delete
End Sub

Hope this helps,
theDude

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" <ec35720@netscapeXSPAM.com> schreef in bericht
> news:41E46304.EF1C2FF6@netscapeXSPAM.com...
> > 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" <j.sons@planet.nl> schreef in bericht
> > > news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> > > > Dave,
> > > >
> > > > Thanks a lot, I hope to find time during the weekend to try it out.
> > > >
> > > > Jack.
> > > >
> > > >
> > > > "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > > news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > > > > 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" <PeoSjoblom@discussions.microsoft.com> schreef in
> > > bericht
> > > > > > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > > > > 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

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" <ec35720@netscapeXSPAM.com> schreef in bericht
> > news:41E46304.EF1C2FF6@netscapeXSPAM.com...
> > > 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" <j.sons@planet.nl> schreef in bericht
> > > > news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> > > > > Dave,
> > > > >
> > > > > Thanks a lot, I hope to find time during the weekend to try it out.
> > > > >
> > > > > Jack.
> > > > >
> > > > >
> > > > > "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > > > news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > > > > > 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" <PeoSjoblom@discussions.microsoft.com> schreef in
> > > > bericht
> > > > > > > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > > > > > 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,

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" <ec35720@netscapeXSPAM.com> schreef in bericht
news:41E5C06E.A2FF2D34@netscapeXSPAM.com...
> 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" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > news:41E46304.EF1C2FF6@netscapeXSPAM.com...
> > > > 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" <j.sons@planet.nl> schreef in bericht
> > > > > news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> > > > > > Dave,
> > > > > >
> > > > > > Thanks a lot, I hope to find time during the weekend to try it
out.
> > > > > >
> > > > > > Jack.
> > > > > >
> > > > > >
> > > > > > "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > > > > news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > > > > > > 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" <PeoSjoblom@discussions.microsoft.com> schreef
in
> > > > > bericht
> > > > > > > > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > > > > > > 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

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...280a%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" <ec35720@netscapeXSPAM.com> schreef in bericht
> news:41E5C06E.A2FF2D34@netscapeXSPAM.com...
> > 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" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > > news:41E46304.EF1C2FF6@netscapeXSPAM.com...
> > > > > 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" <j.sons@planet.nl> schreef in bericht
> > > > > > news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> > > > > > > Dave,
> > > > > > >
> > > > > > > Thanks a lot, I hope to find time during the weekend to try it
> out.
> > > > > > >
> > > > > > > Jack.
> > > > > > >
> > > > > > >
> > > > > > > "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > > > > > news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > > > > > > > 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" <PeoSjoblom@discussions.microsoft.com> schreef
> in
> > > > > > bericht
> > > > > > > > > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > > > > > > > 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,

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" <ec35720@netscapeXSPAM.com> schreef in bericht
news:41E70999.E143B60A@netscapeXSPAM.com...
> 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...1280a%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" <ec35720@netscapeXSPAM.com> schreef in bericht
> > news:41E5C06E.A2FF2D34@netscapeXSPAM.com...
> > > 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" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > > > news:41E46304.EF1C2FF6@netscapeXSPAM.com...
> > > > > > 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" <j.sons@planet.nl> schreef in bericht
> > > > > > > news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> > > > > > > > Dave,
> > > > > > > >
> > > > > > > > Thanks a lot, I hope to find time during the weekend to try
it
> > out.
> > > > > > > >
> > > > > > > > Jack.
> > > > > > > >
> > > > > > > >
> > > > > > > > "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in
bericht
> > > > > > > > news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > > > > > > > > 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" <PeoSjoblom@discussions.microsoft.com>
schreef
> > in
> > > > > > > bericht
> > > > > > > > > >
news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > > > > > > > > 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

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" <ec35720@netscapeXSPAM.com> schreef in bericht
> news:41E70999.E143B60A@netscapeXSPAM.com...
> > 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...1280a%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" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > news:41E5C06E.A2FF2D34@netscapeXSPAM.com...
> > > > 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" <ec35720@netscapeXSPAM.com> schreef in bericht
> > > > > > news:41E46304.EF1C2FF6@netscapeXSPAM.com...
> > > > > > > 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" <j.sons@planet.nl> schreef in bericht
> > > > > > > > news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> > > > > > > > > Dave,
> > > > > > > > >
> > > > > > > > > Thanks a lot, I hope to find time during the weekend to try
> it
> > > out.
> > > > > > > > >
> > > > > > > > > Jack.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in
> bericht
> > > > > > > > > news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > > > > > > > > > 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" <PeoSjoblom@discussions.microsoft.com>
> schreef
> > > in
> > > > > > > > bericht
> > > > > > > > > > >
> news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > > > > > > > > > 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