UtterAccess Discussion Forums > Application loses focus after code--Excel 03

Full Version: Application loses focus after code--Excel 03

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

Harfleur

Feb 13 2007, 07:39 PM

I've created some code that brings up a form where I can type in criteria for an Autofilter. At the end of the code, the form hides, but Excel does not become the active window. How can I make it do that?

Let me know if I'm not providing enough info here.

Thanks,
Eric

KingMartin

Feb 14 2007, 02:21 AM

Hi Eric,

I can't replicate the error. No matter how I try (modal vs. modeless, Me.Hide, Unload Me etc.)

Can you post more of your code please?

Martin

Harfleur

Feb 14 2007, 03:09 PM

Martin--

Here's the code for the command button:

CODE

Private Sub cmdApply_Click()

    Dim strName1 As String, strName2 As String

    Dim strNumber As String, intOperator As Integer



    strName1 = Me.txtCriteria1

    strName2 = Me.txtCriteria2

    strNumber = Me.txtNumberCriteria

    intOperator = xlAnd

    If Me.optOr = True Then intOperator = xlOr



    With Me

        .txtCriteria1 = ""

        .txtCriteria2 = ""

        .txtNumberCriteria = ""

        .Hide

    End With



    If strName1 <> "" Then

        If strName2 <> "" Then

            Selection.AutoFilter Field:=6, Criteria1:="=*" & strName1 _

                & "*", Operator:=intOperator, Criteria2:="=*" & strName2 _

                & "*"

        Else

            Selection.AutoFilter Field:=6, Criteria1:="=*" & strName1 & "*", Operator:=xlAnd

        End If

    Else

        Selection.AutoFilter Field:=6

    End If



    If strNumber <> "" Then

        Selection.AutoFilter Field:=3, Criteria1:="=*" & strNumber & "*", Operator:=xlAnd

    Else

        Selection.AutoFilter Field:=3

    End If





End Sub

I've moved the .Hide command up further in the code to try to get the focus on the sheet; it used to be at the end of this code.
Thanks for looking at this.

--Eric

Edited by: Harfleur on Wed Feb 14 15:14:07 EST 2007.

KingMartin

Feb 14 2007, 03:15 PM

Why do you just hide the form at the end? Does it have to stay loaded?

Try

.Hide
Unload frmHighLight

Also, is the form that has this code behind named frmHighlight?

If so, you should replace all frmHighlight refs with Me.

e.g.

Me.Hide: Unload Me

Martin

Harfleur

Feb 14 2007, 04:41 PM

Martin--

I think I outsmarted myself--I posted the wrong form, and you picked it up before I re-posted the right one. I edited my post above to show the right form.

Sorry!

KingMartin

Feb 14 2007, 05:37 PM

No prob,

and after the code is run Excel App window doesn't have focus? Which windows has focus then? .confused:

As I said before, try to unload the form from memory,

CODE

Me.Hide: Unload Me

I simply can't replicate the error...

BTW, do you need Selection? Is there some way VBA can determine the range so that you don't need to select?

Martin

Harfleur

Feb 14 2007, 06:00 PM

I don't know which window is "active" in Windows at that point...the Excel window is at the front, but it is "inactive," and I have to click on it, changing the color of the title bar, to type stuff in.

As far as Selection goes, I wrote the original code by having a macro record what I was doing and pirating the code from there, so I don't really KNOW if I need Selection! I'm not actually selecting anything myself...I think VBA knows what the selection is somehow from the Autofilter....

Ivan_F_Moala

Feb 15 2007, 02:11 AM

Use;

AppActivate Application.Caption

to activate your MS Application.

KingMartin

Feb 15 2007, 05:14 AM

Thanks for jumping in Ivan

Yeah, sorry, forgot about that one completely

Harfleur, then you're running into danger, potentially. The code relies that the active cell is within the range you want to autofilter (yes, Autofilter method is kind of flexible and will extend the range for ya).

If your first header is in A1, you should use at least

Range("A1")

instead of selection.

Better would be to detrmine the range from within VBA, eg

Dim rgAF As range
Set rgAF = Range("A1:F" & [a65536].end(3).row)

and use like

With rgAF
.Autofilter... etc...
End if

Harfleur

Feb 15 2007, 05:44 PM

Ivan--

Thanks, works like a charm!

Martin--

Thanks for all your help, and the warning on the range/autofilter thing. I will look into changing that.

Thanks guys!

--Eric

Ivan_F_Moala

Feb 15 2007, 08:59 PM

QUOTE

Ivan--

Thanks, works like a charm!

Martin--

Thanks for all your help, and the warning on the range/autofilter thing. I will look into changing that.

Thanks guys!

--Eric

Thanks, also thanks to Martin who is ALWAYS there to help.

KingMartin

Feb 16 2007, 02:45 AM

Ivan is too kind

Don't hesitate to post back if you bump into problem.

You're welcome.

Martin

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.