Macro to simply bring up the Find dialogue box??

I'm trying to write a macro (which I'll assign to an on-screen button) that
simply brings up the Find dialogue box. Thus, you press the button and the
Find box appears.

When I try to record the macro, it requires I exit the dialogue box before
stopping recording.

Any ideas?????

Thank you!!!!!

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

dlgAnswer = Application.Dialogs(xlDialogFindFile).Show


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

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


Hope this helps,
theDude

Try

Application.Dialogs(xlDialogFormulaFind).Show


Regards,

Peo Sjoblom

"marika1981" wrote:

> I'm trying to write a macro (which I'll assign to an on-screen button) that
> simply brings up the Find dialogue box. Thus, you press the button and the
> Find box appears.
>
> When I try to record the macro, it requires I exit the dialogue box before
> stopping recording.
>
> Any ideas?????
>
> Thank you!!!!!

Dave,

I could use in stead of a find button on my spreadsheet that would
automatically let the find function work in 2 non-consecutive columns (in my
case E and G), a macro that does the same if E1, F1 or G1 is double clicked
(or right clicked). Perhaps something like the code below the dotted line
could work (if you would be so kind to correct it for me). But in the code
module of the worksheet I already have another worksheet procedure that is
triggered by a double click, it begins with:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target,
Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT")) Is
Nothing Then Exit Sub

and also a procedure that reacts to a right click. It begins with:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)
Dim myShtCtBar As Object
If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then
Select Case Selection.Column

It seems that you can have only one eventprocedure of a kind in a worksheet
code module. Is there a way out?

----------------------------------------------------------------------------
-----
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)

If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub

Range("E:E,G:G").Select
Range("G1").Activate
Application.Dialogs(xlDialogFormulaFind).Show

End If

End Sub

Jack Sons
The Netherlands


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

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

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

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

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

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

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

You have an overlap of a couple of cells.

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

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

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

Dim myRng1 As Range
Dim myRng2 As Range

If Target.Cells.Count > 1 Then Exit Sub

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

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

End Sub

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

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

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

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

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

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

Dim myRng1 As Range
Dim myRng2 As Range

If Target.Cells.Count > 1 Then Exit Sub

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

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

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

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

End Sub

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

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

--

Dave Peterson

Dave,

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

Jack.


"Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> Yep. You only get one of each type of event within each sheet module.
>
> But you could check to see what range you're in and process based on that.
>
> If you have two ranges, you can have
> 1. No intersection between them (mutually exclusive).
> 2. An overlap of at least one cell.
>
> If the ranges are mutually exclusive, then it's pretty straight forward.
Figure
> out which one you're in (if either) and do the processing based on that.
>
> If the ranges overlap, then you have a decision to make. Do you do the
first
> procedure or the second procedure or both?
>
> (Doing one of them, but not the other resolves to the first case, too.)
>
> You have an overlap of a couple of cells.
>
> If you're in column E, do one thing. But what should occur if you're in
> E1--that cell is in both ranges.
>
> I decided <vbg> that you wanted to do only one thing and that one thing is
to
> show the .find dialog.
>
> Option Explicit
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
> Cancel As Boolean)
>
> Dim myRng1 As Range
> Dim myRng2 As Range
>
> If Target.Cells.Count > 1 Then Exit Sub
>
> With Me
> 'stay away from row 1???
> Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _
> .Rows("2:" & .Rows.Count))
> Set myRng2 = Me.Range("e1:g1")
>
> If Not (Intersect(Target, myRng1) Is Nothing) Then
> 'it's something
> 'do your code for stuff that's in e,as,bt
> 'cancel = true
> ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then
> 'do your code for stuff that's in e1,g1
> Application.EnableEvents = False
> .Range("E:E,G:G").Select
> .Range("G1").Activate
> Application.EnableEvents = True
> Application.Dialogs(xlDialogFormulaFind).Show
> 'cancel = true
> End If
> End With
>
> End Sub
>
> ======
> I left the "cancel = true" commented out. But I would think that you
would want
> that code uncommented.
>
> If your user has edit directly in cell, double clicking will start that
> process. "Cancel = true" stops that from happening.
>
> And in the _BeforeRightClick event, "cancel=true" will stop the popup from
> showing up.
>
> (I'm betting that you don't want either to occur, but you'll have to
uncomment
> it.)
>
> =========
> And since the routine that occurs with rightclicking also occurs with
> doubleclicking in a certain area, you can put that code in one spot and
then
> when something changes, you don't have to fix two routines:
>
> Option Explicit
> Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
> Cancel As Boolean)
>
> Dim myRng1 As Range
> Dim myRng2 As Range
>
> If Target.Cells.Count > 1 Then Exit Sub
>
> With Me
> 'stay away from row 1???
> Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _
> .Rows("2:" & .Rows.Count))
> Set myRng2 = Me.Range("e1:g1")
>
> If Not (Intersect(Target, myRng1) Is Nothing) Then
> 'it's something
> 'do your code for stuff that's in e,as,bt
> MsgBox "myRng1"
> Cancel = True
> ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then
> 'do your code for stuff that's in e1,g1
> Call Worksheet_BeforeRightClick(Target, True)
> Cancel = True
> End If
> End With
>
> End Sub
> Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
> Cancel As Boolean)
>
> With Me
> 'do your code for stuff that's in e1,g1
> Application.EnableEvents = False
> .Range("E:E,G:G").Select
> .Range("G1").Activate
> Application.EnableEvents = True
> Application.Dialogs(xlDialogFormulaFind).Show
> Cancel = True
> End With
>
> End Sub
>
> ps. The application.enableevents stuff is toggled off just in case you
have a
> _selectionchange event, too.
>
> Jack Sons wrote:
> >
> > Dave,
> >
> > I could use in stead of a find button on my spreadsheet that would
> > automatically let the find function work in 2 non-consecutive columns
(in my
> > case E and G), a macro that does the same if E1, F1 or G1 is double
clicked
> > (or right clicked). Perhaps something like the code below the dotted
line
> > could work (if you would be so kind to correct it for me). But in the
code
> > module of the worksheet I already have another worksheet procedure that
is
> > triggered by a double click, it begins with:
> >
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
> > Boolean)
> > If Target.Cells.Count > 1 Then Exit Sub
> > If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target,
> > Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT"))
Is
> > Nothing Then Exit Sub
> >
> > and also a procedure that reacts to a right click. It begins with:
> >
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
> > Boolean)
> > Dim myShtCtBar As Object
> > If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then
> > Select Case Selection.Column
> >
> > It seems that you can have only one eventprocedure of a kind in a
worksheet
> > code module. Is there a way out?
> >
>
> --------------------------------------------------------------------------
--
> > -----
> > Private Sub Worksheet_BeforeDoubleClick( _
> > ByVal Target As Excel.Range, Cancel As Boolean)
> >
> > If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub
> >
> > Range("E:E,G:G").Select
> > Range("G1").Activate
> > Application.Dialogs(xlDialogFormulaFind).Show
> >
> > End If
> >
> > End Sub
> >
> > Jack Sons
> > The Netherlands
> >
> > "Peo Sjoblom" <PeoSjoblom@discussions.microsoft.com> schreef in bericht
> > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > Try
> > >
> > > Application.Dialogs(xlDialogFormulaFind).Show
> > >
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > > "marika1981" wrote:
> > >
> > > > I'm trying to write a macro (which I'll assign to an on-screen
button)
> > that
> > > > simply brings up the Find dialogue box. Thus, you press the button
and
> > the
> > > > Find box appears.
> > > >
> > > > When I try to record the macro, it requires I exit the dialogue box
> > before
> > > > stopping recording.
> > > >
> > > > Any ideas?????
> > > >
> > > > Thank you!!!!!
>
> --
>
> Dave Peterson

Dave,

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

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

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

Jack.



"Jack Sons" <j.sons@planet.nl> schreef in bericht
news:eHAOlVN9EHA.1292@TK2MSFTNGP10.phx.gbl...
> Dave,
>
> Thanks a lot, I hope to find time during the weekend to try it out.
>
> Jack.
>
>
> "Dave Peterson" <ec35720@netscapeXSPAM.com> schreef in bericht
> news:41DDC46C.BCDCF2D2@netscapeXSPAM.com...
> > Yep. You only get one of each type of event within each sheet module.
> >
> > But you could check to see what range you're in and process based on
that.
> >
> > If you have two ranges, you can have
> > 1. No intersection between them (mutually exclusive).
> > 2. An overlap of at least one cell.
> >
> > If the ranges are mutually exclusive, then it's pretty straight forward.
> Figure
> > out which one you're in (if either) and do the processing based on that.
> >
> > If the ranges overlap, then you have a decision to make. Do you do the
> first
> > procedure or the second procedure or both?
> >
> > (Doing one of them, but not the other resolves to the first case, too.)
> >
> > You have an overlap of a couple of cells.
> >
> > If you're in column E, do one thing. But what should occur if you're in
> > E1--that cell is in both ranges.
> >
> > I decided <vbg> that you wanted to do only one thing and that one thing
is
> to
> > show the .find dialog.
> >
> > Option Explicit
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
> > Cancel As Boolean)
> >
> > Dim myRng1 As Range
> > Dim myRng2 As Range
> >
> > If Target.Cells.Count > 1 Then Exit Sub
> >
> > With Me
> > 'stay away from row 1???
> > Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _
> > .Rows("2:" & .Rows.Count))
> > Set myRng2 = Me.Range("e1:g1")
> >
> > If Not (Intersect(Target, myRng1) Is Nothing) Then
> > 'it's something
> > 'do your code for stuff that's in e,as,bt
> > 'cancel = true
> > ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then
> > 'do your code for stuff that's in e1,g1
> > Application.EnableEvents = False
> > .Range("E:E,G:G").Select
> > .Range("G1").Activate
> > Application.EnableEvents = True
> > Application.Dialogs(xlDialogFormulaFind).Show
> > 'cancel = true
> > End If
> > End With
> >
> > End Sub
> >
> > ======
> > I left the "cancel = true" commented out. But I would think that you
> would want
> > that code uncommented.
> >
> > If your user has edit directly in cell, double clicking will start that
> > process. "Cancel = true" stops that from happening.
> >
> > And in the _BeforeRightClick event, "cancel=true" will stop the popup
from
> > showing up.
> >
> > (I'm betting that you don't want either to occur, but you'll have to
> uncomment
> > it.)
> >
> > =========
> > And since the routine that occurs with rightclicking also occurs with
> > doubleclicking in a certain area, you can put that code in one spot and
> then
> > when something changes, you don't have to fix two routines:
> >
> > Option Explicit
> > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
> > Cancel As Boolean)
> >
> > Dim myRng1 As Range
> > Dim myRng2 As Range
> >
> > If Target.Cells.Count > 1 Then Exit Sub
> >
> > With Me
> > 'stay away from row 1???
> > Set myRng1 = Intersect(.Range("e:e,as:as,bt:bt"), _
> > .Rows("2:" & .Rows.Count))
> > Set myRng2 = Me.Range("e1:g1")
> >
> > If Not (Intersect(Target, myRng1) Is Nothing) Then
> > 'it's something
> > 'do your code for stuff that's in e,as,bt
> > MsgBox "myRng1"
> > Cancel = True
> > ElseIf Not (Intersect(Target, myRng2) Is Nothing) Then
> > 'do your code for stuff that's in e1,g1
> > Call Worksheet_BeforeRightClick(Target, True)
> > Cancel = True
> > End If
> > End With
> >
> > End Sub
> > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
> > Cancel As Boolean)
> >
> > With Me
> > 'do your code for stuff that's in e1,g1
> > Application.EnableEvents = False
> > .Range("E:E,G:G").Select
> > .Range("G1").Activate
> > Application.EnableEvents = True
> > Application.Dialogs(xlDialogFormulaFind).Show
> > Cancel = True
> > End With
> >
> > End Sub
> >
> > ps. The application.enableevents stuff is toggled off just in case you
> have a
> > _selectionchange event, too.
> >
> > Jack Sons wrote:
> > >
> > > Dave,
> > >
> > > I could use in stead of a find button on my spreadsheet that would
> > > automatically let the find function work in 2 non-consecutive columns
> (in my
> > > case E and G), a macro that does the same if E1, F1 or G1 is double
> clicked
> > > (or right clicked). Perhaps something like the code below the dotted
> line
> > > could work (if you would be so kind to correct it for me). But in the
> code
> > > module of the worksheet I already have another worksheet procedure
that
> is
> > > triggered by a double click, it begins with:
> > >
> > > Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As
> > > Boolean)
> > > If Target.Cells.Count > 1 Then Exit Sub
> > > If Intersect(Target, Me.Range("E:E")) Is Nothing And Intersect(Target,
> > > Me.Range("AS:AS")) Is Nothing And Intersect(Target, Me.Range("BT:BT"))
> Is
> > > Nothing Then Exit Sub
> > >
> > > and also a procedure that reacts to a right click. It begins with:
> > >
> > > Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As
> > > Boolean)
> > > Dim myShtCtBar As Object
> > > If Target.Columns.Count = 1 Or Target.Rows.Count = 1 Then
> > > Select Case Selection.Column
> > >
> > > It seems that you can have only one eventprocedure of a kind in a
> worksheet
> > > code module. Is there a way out?
> > >
> >
>
> --------------------------------------------------------------------------
> --
> > > -----
> > > Private Sub Worksheet_BeforeDoubleClick( _
> > > ByVal Target As Excel.Range, Cancel As Boolean)
> > >
> > > If Intersect(Target, Range("E1:G1")) Is Nothing Then Exit Sub
> > >
> > > Range("E:E,G:G").Select
> > > Range("G1").Activate
> > > Application.Dialogs(xlDialogFormulaFind).Show
> > >
> > > End If
> > >
> > > End Sub
> > >
> > > Jack Sons
> > > The Netherlands
> > >
> > > "Peo Sjoblom" <PeoSjoblom@discussions.microsoft.com> schreef in
bericht
> > > news:9A623137-AE51-48E7-8354-B0AE9F62DB7E@microsoft.com...
> > > > Try
> > > >
> > > > Application.Dialogs(xlDialogFormulaFind).Show
> > > >
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > > "marika1981" wrote:
> > > >
> > > > > I'm trying to write a macro (which I'll assign to an on-screen
> button)
> > > that
> > > > > simply brings up the Find dialogue box. Thus, you press the
button
> and
> > > the
> > > > > Find box appears.
> > > > >
> > > > > When I try to record the macro, it requires I exit the dialogue
box
> > > before
> > > > > stopping recording.
> > > > >
> > > > > Any ideas?????
> > > > >
> > > > > Thank you!!!!!
> >
> > --
> >
> > Dave Peterson
>
>

I'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

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

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


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

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

Hope this helps,
theDude