Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Macro to simply bring up the Find dialogue box??
I'm trying to write a macro (which I'll assign to an on-screen button) that
simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! |
#2
|
|||
|
|||
Try
Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! |
#3
|
|||
|
|||
I've assigned my version of that macro to ctrl-f (actually, that's the keyboard
shortcut key). But you could also customize an existing toolbar (or create a new one) that shows that dialog. tools|customize|commands tab|Edit category Near the bottom, there's a binoculars icon (Find). Just drag it to your favorite toolbar. marika1981 wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson |
#4
|
|||
|
|||
Dave,
I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? ---------------------------------------------------------------------------- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! |
#5
|
|||
|
|||
Yep. You only get one of each type of event within each sheet module.
But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? ---------------------------------------------------------------------------- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson |
#6
|
|||
|
|||
Dave,
Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson |
#7
|
|||
|
|||
Dave,
About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson |
#8
|
|||
|
|||
I'm not quite sure I understand the problem with the toolbar.
If you create a new toolbar (tools|customize|toolbars tab) and assign your macro to that custom toolbar, then you could keep that toolbar shown all the time (or hide it and then reshow it on demand). When you click on the button on that toolbar, excel will open that other workbook -- if it's not open already. You wouldn't need to keep that macro workbook open. Excel will take care of the housekeeping. Jack Sons wrote: Dave, About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson -- Dave Peterson |
#9
|
|||
|
|||
Dave,
Maybe my phrase "worksheet button" is wrong, I don't know the correct name. I mean this. I can assign a macro (any macro) to either a button on one of the toolbars at the upperside of my screen, above the worksheet, or to a button that is fixed to a specific worksheet. The latter I meant by "worksheet button". The advantage of a worksheet button is that you don't clog the permanent toolbars with all kind of buttons that you only need with specific worksheets. When I assign a macro (any macro) to a button on the worksheet, that button is fixed (nailed down) to the specific position where I did put it, say for example C1. When the worksheet is scrolled to the right the button disappears out of sight. If the sheet is scrolled to te region of, say, column AP and this button has to be used, you have to scroll back to the region where column C - and the button - is visible again. After you get the result from clicking the button you have to scroll back to the AP region, which is a bit tedious. What I would like is that the button "floats" on the worksheet so that it stays in sight when the sheet is scrolled to the right (or the left). Hope I made it clear this time. Is what I want possible and if so, how? Thanks in advance. Jack. "Dave Peterson" schreef in bericht ... I'm not quite sure I understand the problem with the toolbar. If you create a new toolbar (tools|customize|toolbars tab) and assign your macro to that custom toolbar, then you could keep that toolbar shown all the time (or hide it and then reshow it on demand). When you click on the button on that toolbar, excel will open that other workbook -- if it's not open already. You wouldn't need to keep that macro workbook open. Excel will take care of the housekeeping. Jack Sons wrote: Dave, About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson -- Dave Peterson |
#10
|
|||
|
|||
It sure sounds like you're describing a toolbar to me. (I think it would be
easiest to just let it float (don't dock it to the top of the application window).) But if you want, maybe something like this: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myShape As Shape Set myShape = Me.Shapes("Button 1") With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) myShape.Top = .Top myShape.Left = .Left End With End Sub Rightclick on the worksheet that should have this behavior. Select view code and paste this into that codewindow. If you did window|freeze panes, so that row 1 is always visible, you may want to change: With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) to With Me.Cells(1, ActiveWindow.ScrollColumn) (or whatever row you want the button in). This routine moves the button when the selection changes. If the user moves the screen using the scroll bars, then it won't show up until they select a range. Jack Sons wrote: Dave, Maybe my phrase "worksheet button" is wrong, I don't know the correct name. I mean this. I can assign a macro (any macro) to either a button on one of the toolbars at the upperside of my screen, above the worksheet, or to a button that is fixed to a specific worksheet. The latter I meant by "worksheet button". The advantage of a worksheet button is that you don't clog the permanent toolbars with all kind of buttons that you only need with specific worksheets. When I assign a macro (any macro) to a button on the worksheet, that button is fixed (nailed down) to the specific position where I did put it, say for example C1. When the worksheet is scrolled to the right the button disappears out of sight. If the sheet is scrolled to te region of, say, column AP and this button has to be used, you have to scroll back to the region where column C - and the button - is visible again. After you get the result from clicking the button you have to scroll back to the AP region, which is a bit tedious. What I would like is that the button "floats" on the worksheet so that it stays in sight when the sheet is scrolled to the right (or the left). Hope I made it clear this time. Is what I want possible and if so, how? Thanks in advance. Jack. "Dave Peterson" schreef in bericht ... I'm not quite sure I understand the problem with the toolbar. If you create a new toolbar (tools|customize|toolbars tab) and assign your macro to that custom toolbar, then you could keep that toolbar shown all the time (or hide it and then reshow it on demand). When you click on the button on that toolbar, excel will open that other workbook -- if it's not open already. You wouldn't need to keep that macro workbook open. Excel will take care of the housekeeping. Jack Sons wrote: Dave, About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
|
|||
|
|||
Just to add: With these type event macros (and macros in general), you may lose
the undo stack. Dave Peterson wrote: It sure sounds like you're describing a toolbar to me. (I think it would be easiest to just let it float (don't dock it to the top of the application window).) But if you want, maybe something like this: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myShape As Shape Set myShape = Me.Shapes("Button 1") With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) myShape.Top = .Top myShape.Left = .Left End With End Sub Rightclick on the worksheet that should have this behavior. Select view code and paste this into that codewindow. If you did window|freeze panes, so that row 1 is always visible, you may want to change: With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) to With Me.Cells(1, ActiveWindow.ScrollColumn) (or whatever row you want the button in). This routine moves the button when the selection changes. If the user moves the screen using the scroll bars, then it won't show up until they select a range. Jack Sons wrote: Dave, Maybe my phrase "worksheet button" is wrong, I don't know the correct name. I mean this. I can assign a macro (any macro) to either a button on one of the toolbars at the upperside of my screen, above the worksheet, or to a button that is fixed to a specific worksheet. The latter I meant by "worksheet button". The advantage of a worksheet button is that you don't clog the permanent toolbars with all kind of buttons that you only need with specific worksheets. When I assign a macro (any macro) to a button on the worksheet, that button is fixed (nailed down) to the specific position where I did put it, say for example C1. When the worksheet is scrolled to the right the button disappears out of sight. If the sheet is scrolled to te region of, say, column AP and this button has to be used, you have to scroll back to the region where column C - and the button - is visible again. After you get the result from clicking the button you have to scroll back to the AP region, which is a bit tedious. What I would like is that the button "floats" on the worksheet so that it stays in sight when the sheet is scrolled to the right (or the left). Hope I made it clear this time. Is what I want possible and if so, how? Thanks in advance. Jack. "Dave Peterson" schreef in bericht ... I'm not quite sure I understand the problem with the toolbar. If you create a new toolbar (tools|customize|toolbars tab) and assign your macro to that custom toolbar, then you could keep that toolbar shown all the time (or hide it and then reshow it on demand). When you click on the button on that toolbar, excel will open that other workbook -- if it's not open already. You wouldn't need to keep that macro workbook open. Excel will take care of the housekeeping. Jack Sons wrote: Dave, About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
|
|||
|
|||
Dave,
Thank you very much, this is the solution. But .. I am afraid I don't grasp the floating toolbar stuff. Is it possible to have a custom toolbar in sight - that stays in sight - as soon as the worksheet (or workbook?) is opened? That has the advantage that one could have more than one button to use. Jack. "Dave Peterson" schreef in bericht ... Just to add: With these type event macros (and macros in general), you may lose the undo stack. Dave Peterson wrote: It sure sounds like you're describing a toolbar to me. (I think it would be easiest to just let it float (don't dock it to the top of the application window).) But if you want, maybe something like this: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myShape As Shape Set myShape = Me.Shapes("Button 1") With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) myShape.Top = .Top myShape.Left = .Left End With End Sub Rightclick on the worksheet that should have this behavior. Select view code and paste this into that codewindow. If you did window|freeze panes, so that row 1 is always visible, you may want to change: With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) to With Me.Cells(1, ActiveWindow.ScrollColumn) (or whatever row you want the button in). This routine moves the button when the selection changes. If the user moves the screen using the scroll bars, then it won't show up until they select a range. Jack Sons wrote: Dave, Maybe my phrase "worksheet button" is wrong, I don't know the correct name. I mean this. I can assign a macro (any macro) to either a button on one of the toolbars at the upperside of my screen, above the worksheet, or to a button that is fixed to a specific worksheet. The latter I meant by "worksheet button". The advantage of a worksheet button is that you don't clog the permanent toolbars with all kind of buttons that you only need with specific worksheets. When I assign a macro (any macro) to a button on the worksheet, that button is fixed (nailed down) to the specific position where I did put it, say for example C1. When the worksheet is scrolled to the right the button disappears out of sight. If the sheet is scrolled to te region of, say, column AP and this button has to be used, you have to scroll back to the region where column C - and the button - is visible again. After you get the result from clicking the button you have to scroll back to the AP region, which is a bit tedious. What I would like is that the button "floats" on the worksheet so that it stays in sight when the sheet is scrolled to the right (or the left). Hope I made it clear this time. Is what I want possible and if so, how? Thanks in advance. Jack. "Dave Peterson" schreef in bericht ... I'm not quite sure I understand the problem with the toolbar. If you create a new toolbar (tools|customize|toolbars tab) and assign your macro to that custom toolbar, then you could keep that toolbar shown all the time (or hide it and then reshow it on demand). When you click on the button on that toolbar, excel will open that other workbook -- if it's not open already. You wouldn't need to keep that macro workbook open. Excel will take care of the housekeeping. Jack Sons wrote: Dave, About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
|
|||
|
|||
First, tools|customize|toolbars tab.
Create a new toolbar. Notice that it's initial location is just floating on the worksheet. You can do that with other toolbars, too. Just grab it (by the grab handle on the far left) and drag it down to where you want it. You could do that with any custom toolbar you create. Personally, I like to load the toolbar so it's floating. It gets the user's attention (since it's in the way). If they want to dock it at the top/side/bottom, they can.) Some alternatives: You could attach a toolbar to a workbook. If you're going to do that, you'll want to read Jan Karel Pieterse's notes: http://google.com/groups?threadm=083...0a% 40phx.gbl You could build the toolbar on the fly within that workbook: http://groups.google.co.uk/groups?th...5B41%40msn.com That link has a link to John Walkenbach's MenuMaker program. It adds items to the worksheet toolbar. ===== If the macro is supposed to be used against multiple workbooks, I would try to separate the macro from the data. Save your macro workbook as an addin (*.xla) and load that whenever you need it. Jack Sons wrote: Dave, Thank you very much, this is the solution. But .. I am afraid I don't grasp the floating toolbar stuff. Is it possible to have a custom toolbar in sight - that stays in sight - as soon as the worksheet (or workbook?) is opened? That has the advantage that one could have more than one button to use. Jack. "Dave Peterson" schreef in bericht ... Just to add: With these type event macros (and macros in general), you may lose the undo stack. Dave Peterson wrote: It sure sounds like you're describing a toolbar to me. (I think it would be easiest to just let it float (don't dock it to the top of the application window).) But if you want, maybe something like this: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myShape As Shape Set myShape = Me.Shapes("Button 1") With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) myShape.Top = .Top myShape.Left = .Left End With End Sub Rightclick on the worksheet that should have this behavior. Select view code and paste this into that codewindow. If you did window|freeze panes, so that row 1 is always visible, you may want to change: With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) to With Me.Cells(1, ActiveWindow.ScrollColumn) (or whatever row you want the button in). This routine moves the button when the selection changes. If the user moves the screen using the scroll bars, then it won't show up until they select a range. Jack Sons wrote: Dave, Maybe my phrase "worksheet button" is wrong, I don't know the correct name. I mean this. I can assign a macro (any macro) to either a button on one of the toolbars at the upperside of my screen, above the worksheet, or to a button that is fixed to a specific worksheet. The latter I meant by "worksheet button". The advantage of a worksheet button is that you don't clog the permanent toolbars with all kind of buttons that you only need with specific worksheets. When I assign a macro (any macro) to a button on the worksheet, that button is fixed (nailed down) to the specific position where I did put it, say for example C1. When the worksheet is scrolled to the right the button disappears out of sight. If the sheet is scrolled to te region of, say, column AP and this button has to be used, you have to scroll back to the region where column C - and the button - is visible again. After you get the result from clicking the button you have to scroll back to the AP region, which is a bit tedious. What I would like is that the button "floats" on the worksheet so that it stays in sight when the sheet is scrolled to the right (or the left). Hope I made it clear this time. Is what I want possible and if so, how? Thanks in advance. Jack. "Dave Peterson" schreef in bericht ... I'm not quite sure I understand the problem with the toolbar. If you create a new toolbar (tools|customize|toolbars tab) and assign your macro to that custom toolbar, then you could keep that toolbar shown all the time (or hide it and then reshow it on demand). When you click on the button on that toolbar, excel will open that other workbook -- if it's not open already. You wouldn't need to keep that macro workbook open. Excel will take care of the housekeeping. Jack Sons wrote: Dave, About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
|
|||
|
|||
Dave,
That's it. Without you and all those others in this NG who are never too tired to answer stupid or much repeated questions I would be nowhere in Excel land. Jack. "Dave Peterson" schreef in bericht ... First, tools|customize|toolbars tab. Create a new toolbar. Notice that it's initial location is just floating on the worksheet. You can do that with other toolbars, too. Just grab it (by the grab handle on the far left) and drag it down to where you want it. You could do that with any custom toolbar you create. Personally, I like to load the toolbar so it's floating. It gets the user's attention (since it's in the way). If they want to dock it at the top/side/bottom, they can.) Some alternatives: You could attach a toolbar to a workbook. If you're going to do that, you'll want to read Jan Karel Pieterse's notes: http://google.com/groups?threadm=083...80a% 40phx.gb l You could build the toolbar on the fly within that workbook: http://groups.google.co.uk/groups?th...5B41%40msn.com That link has a link to John Walkenbach's MenuMaker program. It adds items to the worksheet toolbar. ===== If the macro is supposed to be used against multiple workbooks, I would try to separate the macro from the data. Save your macro workbook as an addin (*.xla) and load that whenever you need it. Jack Sons wrote: Dave, Thank you very much, this is the solution. But .. I am afraid I don't grasp the floating toolbar stuff. Is it possible to have a custom toolbar in sight - that stays in sight - as soon as the worksheet (or workbook?) is opened? That has the advantage that one could have more than one button to use. Jack. "Dave Peterson" schreef in bericht ... Just to add: With these type event macros (and macros in general), you may lose the undo stack. Dave Peterson wrote: It sure sounds like you're describing a toolbar to me. (I think it would be easiest to just let it float (don't dock it to the top of the application window).) But if you want, maybe something like this: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myShape As Shape Set myShape = Me.Shapes("Button 1") With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) myShape.Top = .Top myShape.Left = .Left End With End Sub Rightclick on the worksheet that should have this behavior. Select view code and paste this into that codewindow. If you did window|freeze panes, so that row 1 is always visible, you may want to change: With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) to With Me.Cells(1, ActiveWindow.ScrollColumn) (or whatever row you want the button in). This routine moves the button when the selection changes. If the user moves the screen using the scroll bars, then it won't show up until they select a range. Jack Sons wrote: Dave, Maybe my phrase "worksheet button" is wrong, I don't know the correct name. I mean this. I can assign a macro (any macro) to either a button on one of the toolbars at the upperside of my screen, above the worksheet, or to a button that is fixed to a specific worksheet. The latter I meant by "worksheet button". The advantage of a worksheet button is that you don't clog the permanent toolbars with all kind of buttons that you only need with specific worksheets. When I assign a macro (any macro) to a button on the worksheet, that button is fixed (nailed down) to the specific position where I did put it, say for example C1. When the worksheet is scrolled to the right the button disappears out of sight. If the sheet is scrolled to te region of, say, column AP and this button has to be used, you have to scroll back to the region where column C - and the button - is visible again. After you get the result from clicking the button you have to scroll back to the AP region, which is a bit tedious. What I would like is that the button "floats" on the worksheet so that it stays in sight when the sheet is scrolled to the right (or the left). Hope I made it clear this time. Is what I want possible and if so, how? Thanks in advance. Jack. "Dave Peterson" schreef in bericht ... I'm not quite sure I understand the problem with the toolbar. If you create a new toolbar (tools|customize|toolbars tab) and assign your macro to that custom toolbar, then you could keep that toolbar shown all the time (or hide it and then reshow it on demand). When you click on the button on that toolbar, excel will open that other workbook -- if it's not open already. You wouldn't need to keep that macro workbook open. Excel will take care of the housekeeping. Jack Sons wrote: Dave, About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & ..Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & ..Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
|
|||
|
|||
Glad you got it sorted out.
Jack Sons wrote: Dave, That's it. Without you and all those others in this NG who are never too tired to answer stupid or much repeated questions I would be nowhere in Excel land. Jack. "Dave Peterson" schreef in bericht ... First, tools|customize|toolbars tab. Create a new toolbar. Notice that it's initial location is just floating on the worksheet. You can do that with other toolbars, too. Just grab it (by the grab handle on the far left) and drag it down to where you want it. You could do that with any custom toolbar you create. Personally, I like to load the toolbar so it's floating. It gets the user's attention (since it's in the way). If they want to dock it at the top/side/bottom, they can.) Some alternatives: You could attach a toolbar to a workbook. If you're going to do that, you'll want to read Jan Karel Pieterse's notes: http://google.com/groups?threadm=083...80a% 40phx.gb l You could build the toolbar on the fly within that workbook: http://groups.google.co.uk/groups?th...5B41%40msn.com That link has a link to John Walkenbach's MenuMaker program. It adds items to the worksheet toolbar. ===== If the macro is supposed to be used against multiple workbooks, I would try to separate the macro from the data. Save your macro workbook as an addin (*.xla) and load that whenever you need it. Jack Sons wrote: Dave, Thank you very much, this is the solution. But .. I am afraid I don't grasp the floating toolbar stuff. Is it possible to have a custom toolbar in sight - that stays in sight - as soon as the worksheet (or workbook?) is opened? That has the advantage that one could have more than one button to use. Jack. "Dave Peterson" schreef in bericht ... Just to add: With these type event macros (and macros in general), you may lose the undo stack. Dave Peterson wrote: It sure sounds like you're describing a toolbar to me. (I think it would be easiest to just let it float (don't dock it to the top of the application window).) But if you want, maybe something like this: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myShape As Shape Set myShape = Me.Shapes("Button 1") With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) myShape.Top = .Top myShape.Left = .Left End With End Sub Rightclick on the worksheet that should have this behavior. Select view code and paste this into that codewindow. If you did window|freeze panes, so that row 1 is always visible, you may want to change: With Me.Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn) to With Me.Cells(1, ActiveWindow.ScrollColumn) (or whatever row you want the button in). This routine moves the button when the selection changes. If the user moves the screen using the scroll bars, then it won't show up until they select a range. Jack Sons wrote: Dave, Maybe my phrase "worksheet button" is wrong, I don't know the correct name. I mean this. I can assign a macro (any macro) to either a button on one of the toolbars at the upperside of my screen, above the worksheet, or to a button that is fixed to a specific worksheet. The latter I meant by "worksheet button". The advantage of a worksheet button is that you don't clog the permanent toolbars with all kind of buttons that you only need with specific worksheets. When I assign a macro (any macro) to a button on the worksheet, that button is fixed (nailed down) to the specific position where I did put it, say for example C1. When the worksheet is scrolled to the right the button disappears out of sight. If the sheet is scrolled to te region of, say, column AP and this button has to be used, you have to scroll back to the region where column C - and the button - is visible again. After you get the result from clicking the button you have to scroll back to the AP region, which is a bit tedious. What I would like is that the button "floats" on the worksheet so that it stays in sight when the sheet is scrolled to the right (or the left). Hope I made it clear this time. Is what I want possible and if so, how? Thanks in advance. Jack. "Dave Peterson" schreef in bericht ... I'm not quite sure I understand the problem with the toolbar. If you create a new toolbar (tools|customize|toolbars tab) and assign your macro to that custom toolbar, then you could keep that toolbar shown all the time (or hide it and then reshow it on demand). When you click on the button on that toolbar, excel will open that other workbook -- if it's not open already. You wouldn't need to keep that macro workbook open. Excel will take care of the housekeeping. Jack Sons wrote: Dave, About conflicting event ranges: I got it. Thank you very much. Connected to my first question another one. How nice would it be (for me) if a button on a worksheet (such as the one with the find function) would not get out of sight when the sheet is scrolled to the left or to the right. So a kind of "floating" button that always stays in sight. Can't have it on a fixed toolbar and fixing it to a custom toolbar is not very practical because I would have to enable that toolbar every time I want to use that button. Fixing it to a cell (for instance A1) in the left most column and fixing that column (don't know the proper phrase) is also not disirable. Is it possible to make a worksheet button that goes along with the scrolling? Jack. "Jack Sons" schreef in bericht ... Dave, Thanks a lot, I hope to find time during the weekend to try it out. Jack. "Dave Peterson" schreef in bericht ... Yep. You only get one of each type of event within each sheet module. But you could check to see what range you're in and process based on that. If you have two ranges, you can have 1. No intersection between them (mutually exclusive). 2. An overlap of at least one cell. If the ranges are mutually exclusive, then it's pretty straight forward. Figure out which one you're in (if either) and do the processing based on that. If the ranges overlap, then you have a decision to make. Do you do the first procedure or the second procedure or both? (Doing one of them, but not the other resolves to the first case, too.) You have an overlap of a couple of cells. If you're in column E, do one thing. But what should occur if you're in E1--that cell is in both ranges. I decided <vbg that you wanted to do only one thing and that one thing is to show the .find dialog. Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt 'cancel = true ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show 'cancel = true End If End With End Sub ====== I left the "cancel = true" commented out. But I would think that you would want that code uncommented. If your user has edit directly in cell, double clicking will start that process. "Cancel = true" stops that from happening. And in the _BeforeRightClick event, "cancel=true" will stop the popup from showing up. (I'm betting that you don't want either to occur, but you'll have to uncomment it.) ========= And since the routine that occurs with rightclicking also occurs with doubleclicking in a certain area, you can put that code in one spot and then when something changes, you don't have to fix two routines: Option Explicit Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _ Cancel As Boolean) Dim myRng1 As Range Dim myRng2 As Range If Target.Cells.Count 1 Then Exit Sub With Me 'stay away from row 1??? Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _ .Rows("2:" & .Rows.Count)) Set myRng2 = Me.Range("e1:g1") If Not (Intersect(Target, myRng1) Is Nothing) Then 'it's something 'do your code for stuff that's in e,as,bt MsgBox "myRng1" Cancel = True ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then 'do your code for stuff that's in e1,g1 Call Worksheet_BeforeRightClick(Target, True) Cancel = True End If End With End Sub Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _ Cancel As Boolean) With Me 'do your code for stuff that's in e1,g1 Application.EnableEvents = False .Range("E:E,G:G").Select .Range("G1").Activate Application.EnableEvents = True Application.Dialogs(xlDialogFormulaFind).Show Cancel = True End With End Sub ps. The application.enableevents stuff is toggled off just in case you have a _selectionchange event, too. Jack Sons wrote: Dave, I could use in stead of a find button on my spreadsheet that would automatically let the find function work in 2 non-consecutive columns (in my case E and G), a macro that does the same if E1, F1 or G1 is double clicked (or right clicked). Perhaps something like the code below the dotted line could work (if you would be so kind to correct it for me). But in the code module of the worksheet I already have another worksheet procedure that is triggered by a double click, it begins with: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Cells.Count 1 Then Exit Sub If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target, Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is Nothing Then Exit Sub and also a procedure that reacts to a right click. It begins with: Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Dim myShtCtBar As Object If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then Select Case Selection.Column It seems that you can have only one eventprocedure of a kind in a worksheet code module. Is there a way out? -------------------------------------------------------------------------- -- ----- Private Sub Worksheet_BeforeDoubleClick( _ ByVal Target As Excel.Range, Cancel As Boolean) If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub Range("E:E,G:G").Select Range("G1").Activate Application.Dialogs(xlDialogFormulaFind).Show End If End Sub Jack Sons The Netherlands "Peo Sjoblom" schreef in bericht ... Try Application.Dialogs(xlDialogFormulaFind).Show Regards, Peo Sjoblom "marika1981" wrote: I'm trying to write a macro (which I'll assign to an on-screen button) that simply brings up the Find dialogue box. Thus, you press the button and the Find box appears. When I try to record the macro, it requires I exit the dialogue box before stopping recording. Any ideas????? Thank you!!!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to Find Replace in Excel | Excel Discussion (Misc queries) | |||
Macro to find and delete all FALSE statements | Excel Discussion (Misc queries) | |||
I need a macro to find cut and paste data to new cell | Excel Discussion (Misc queries) | |||
I need a macro to find cut and paste data to new cell | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) |