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