LinkBack URL
About LinkBacksDave,
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
>
>
Register To Reply
Bookmarks