I need some help understanding why find dialog box, run via the
sendkeys method allows the dialog to run modeless yet, the second
routine will not run modeless. My users want to leave the find dialog
box open while making find & replace changes to their worksheet

I would prefer to use the vba format so I can set my arguments
aprpropriately, but need the modeless functionality of the sendkeys
routine.

Any help is greatly appreciated.

Alan

Sub find1()
With ActiveSheet
Application.Goto Reference:=Worksheets("Database").Range("A9")
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=False
On Error Resume Next
SendKeys ("^f{BS}")
SendKeys ("%T")
SendKeys ("%LF~%N{ESC}"), Wait:=True
SendKeys "{BS}"
End With
End sub

Sub find2()
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=True
Application.Dialogs(xlDialogFormulaFind).Show("", 1, 2, 2)

That will make your hair turn gray and fall out.

"acampbell" wrote:

I need some help understanding why find dialog box, run via the
sendkeys method allows the dialog to run modeless yet, the second
routine will not run modeless. My users want to leave the find dialog
box open while making find & replace changes to their worksheet

I would prefer to use the vba format so I can set my arguments
aprpropriately, but need the modeless functionality of the sendkeys
routine.

Any help is greatly appreciated.

Alan

Sub find1()
With ActiveSheet
Application.Goto Reference:=Worksheets("Database").Range("A9")
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=False
On Error Resume Next
SendKeys ("^f{BS}")
SendKeys ("%T")
SendKeys ("%LF~%N{ESC}"), Wait:=True
SendKeys "{BS}"
End With
End sub

Sub find2()
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=True
Application.Dialogs(xlDialogFormulaFind).Show("", 1, 2, 2)

On Feb 2, 6:18 pm, JLGWhiz wrote:
That will make your hair turn gray and fall out.



"acampbell" wrote:
I need some help understanding why find dialog box, run via the
sendkeys method allows the dialog to run modeless yet, the second
routine will not run modeless. My users want to leave the find dialog
box open while making find & replace changes to their worksheet

I would prefer to use the vba format so I can set my arguments
aprpropriately, but need the modeless functionality of the sendkeys
routine.

Any help is greatly appreciated.

Alan

Sub find1()
With ActiveSheet
Application.Goto Reference:=Worksheets("Database").Range("A9")
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=False
On Error Resume Next
SendKeys ("^f{BS}")
SendKeys ("%T")
SendKeys ("%LF~%N{ESC}"), Wait:=True
SendKeys "{BS}"
End With
End sub

Sub find2()
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=True
Application.Dialogs(xlDialogFormulaFind).Show("", 1, 2, 2)- Hide quoted text -

- Show quoted text -

Don't make me have to post my picture. What remains is almost gray
now!! I can live without an explanation if somebody can provide a
work-around that allows me to display and set the arguments for the
find dialog while allowing users to continue working in the spread
sheet.

Alan,
If I understand correctly, this does what you want.

Private Sub CommandButton2_Click()
Const FIND_REPLACE_DIALOG As Long = 1849
Application.CommandBars.FindControl(, FIND_REPLACE_DIALOG).Execute
End Sub

Ensure the command button has the .TakeFocusOnClick property=False.

NickHK

"acampbell" wrote in message
oups.com...
I need some help understanding why find dialog box, run via the
sendkeys method allows the dialog to run modeless yet, the second
routine will not run modeless. My users want to leave the find dialog
box open while making find & replace changes to their worksheet

I would prefer to use the vba format so I can set my arguments
aprpropriately, but need the modeless functionality of the sendkeys
routine.

Any help is greatly appreciated.

Alan

Sub find1()
With ActiveSheet
Application.Goto Reference:=Worksheets("Database").Range("A9")
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=False
On Error Resume Next
SendKeys ("^f{BS}")
SendKeys ("%T")
SendKeys ("%LF~%N{ESC}"), Wait:=True
SendKeys "{BS}"
End With
End sub

Sub find2()
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=True
Application.Dialogs(xlDialogFormulaFind).Show("", 1, 2, 2)

On Feb 6, 1:08 am, "NickHK" wrote:
Alan,
If I understand correctly, this does what you want.

Private Sub CommandButton2_Click()
Const FIND_REPLACE_DIALOG As Long = 1849
Application.CommandBars.FindControl(, FIND_REPLACE_DIALOG).Execute
End Sub

Ensure the command button has the .TakeFocusOnClick property=False.

NickHK

"acampbell" wrote in message

oups.com...



I need some help understanding why find dialog box, run via the
sendkeys method allows the dialog to run modeless yet, the second
routine will not run modeless. My users want to leave the find dialog
box open while making find & replace changes to their worksheet

I would prefer to use the vba format so I can set my arguments
aprpropriately, but need the modeless functionality of the sendkeys
routine.

Any help is greatly appreciated.

Alan

Sub find1()
With ActiveSheet
Application.Goto Reference:=Worksheets("Database").Range("A9")
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=False
On Error Resume Next
SendKeys ("^f{BS}")
SendKeys ("%T")
SendKeys ("%LF~%N{ESC}"), Wait:=True
SendKeys "{BS}"
End With
End sub

Sub find2()
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=True
Application.Dialogs(xlDialogFormulaFind).Show("", 1, 2, 2)- Hide quoted text -

- Show quoted text -

Nick,

Fantastic! Worked like a charm. Using some info Tom Ogilvy gave me a
while back regarding the find mehod and setting persistent values, I
ended up with a crude workaround yesterday, but prefer your code over
this by far. I had to set my persistent values (as needed) with the
find method and then used sendkeys to open the dialog box.

Thanks for your response.

Alan

Sub Find_Acct()

Dim Rng As Range

'Set visible range to users' prevference.
Application.Goto Reference:=Worksheets("Database").Range("A9")
Application.Goto Reference:=Worksheets("Database").Range("D9"), _
Scroll:=False

With Columns(4) 'sets persistent values of the find dialog without an
actual search
Set Rng = .Find(What:="", _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With

SendKeys "^f{BS}"
End Sub